Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I found that in a dimensional layout, it is beneficial to create a dummy row in the dimension to avoid writing LEFT OUTER from the fact table. There are other reason for this aside from perceived performance benefit.

However, is it appropriate to use it in a relational database? Should we create dummy rows in our base tables so that our relation table can attach to it? Besides being unnatural for RDBMS, it causes unnecessary database bloat. Just wondering if I am NOT seeing something.

All in the spirit of avoiding OUTER JOINs.

share|improve this question
    
Have you got an example where you're considering using dummy rows? Is this for dates, lookups or something else? –  Chris Saxon Jan 27 '13 at 12:42
    
For example, Table A (a_pkid, name), Table B (b_pkid, address) and Table C (c_pk_id, a_pk_id, b_pkid). Create a dummy b_pkid, so that a_pkid will always exist in Table C, avoiding a LEFT OUTER JOIN. Perhaps address in dummy row will say 'No Address Provided'. –  Robert Co Jan 28 '13 at 1:35
3  
What database are you using that makes outer joins so deadly? –  Chris Travers Jan 28 '13 at 9:32
add comment

1 Answer 1

Why do you wish to avoid outer joins? Using these does have a specific meaning which is "return me all the values from the driving table regardless of whether there is a matching row in the outer joined table." This gives a clear statement to future developers who maintain your code which would be lost if using a dummy value.

There are some things to bear in mind however:

(Note - discussion is based on Oracle; other major RDBMSes are likely to be similar but may have different/better optimizations which affect how outer joins are executed).

From a performance perspective, outer joining restricts the options available to the query optimizer. When constructing the query plan, the optimizer determines which is the smaller rowset and uses that as the driving table. When you specify an outer join, then the table you're outer joining from will become the driving rowsource.

Using the classic emp and dept tables supplied with Oracle, we can see this in action:

explain plan for 
  select * 
  from   scott.emp e, scott.dept d
  where  e.deptno = d.deptno;

select * from table(dbms_xplan.display(null, null, 'BASIC +ROWS'));

--------------------------------------------------------                                                                                                                                                                                                                                                     
| Id  | Operation                    | Name    | Rows  |                                                                                                                                                                                                                                                     
--------------------------------------------------------                                                                                                                                                                                                                                                     
|   0 | SELECT STATEMENT             |         |    14 |                                                                                                                                                                                                                                                     
|   1 |  MERGE JOIN                  |         |    14 |                                                                                                                                                                                                                                                     
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |                                                                                                                                                                                                                                                     
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |                                                                                                                                                                                                                                                     
|   4 |   SORT JOIN                  |         |    14 |                                                                                                                                                                                                                                                     
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |                                                                                                                                                                                                                                                     
--------------------------------------------------------   

explain plan for 
  select * 
  from   scott.emp e, scott.dept d
  where  e.deptno = d.deptno (+);

select * from table(dbms_xplan.display(null, null, 'BASIC +ROWS'));

-------------------------------------------                                                                                                                                                                                                                                                                  
| Id  | Operation          | Name | Rows  |                                                                                                                                                                                                                                                                  
-------------------------------------------                                                                                                                                                                                                                                                                  
|   0 | SELECT STATEMENT   |      |    14 |                                                                                                                                                                                                                                                                  
|   1 |  HASH JOIN OUTER   |      |    14 |                                                                                                                                                                                                                                                                  
|   2 |   TABLE ACCESS FULL| EMP  |    14 |                                                                                                                                                                                                                                                                  
|   3 |   TABLE ACCESS FULL| DEPT |     4 |                                                                                                                                                                                                                                                                  
-------------------------------------------  

When we fully join the two tables, Oracle chooses the smaller of the two (dept) to be the driving table. However, when we outer join to it, the order is switched. This is because it knows we need to return all the rows from emp no matter what.

As query plans are generally most efficient when starting with the smallest rowset, working up to the biggest, this could cause issues with multi-table joins outer joining from a "large" table to a "small" one. In some cases this can cause queries to be very slow. In an OLTP application most queries should only be returning a handful of rows though, so all rowsets should be small (even if coming from a large table), meaning you shouldn't encounter this problem very often in a well designed (OLTP) system.

To convert your queries to become full joins, you'll need to decide what value to use instead of null. This is a tricky issue as you're converting something which means I don't know into whatever value you assign "null" to be. This can cause confusion for future developers down the line and introduce performance issues of it's own (e.g. single column indexes don't include the null rows). See this for some discussion around null.

Personally I wouldn't attempt to create dummy values to represent null. If you find that you have performance issues related to outer joins then I would start by looking at the affected queries and tuning those individually. If this issue is affecting a lot of queries I may start looking at using dummy values, but only as a last resort.

Of course, you should always ask whether you genuinely mean to use an outer join or if you're using it "just in case"!

share|improve this answer
    
Also I would add that if you are adding dummy values your outer side will still always be smaller. I believe PostgreSQL will use indexes in trying to guess which side will be smaller (I don't know about Oracle) but if you are guaranteeing that for every row in relation A there is one or more rows in relation B I don't see how this changes things performance-wise from an outer join. –  Chris Travers Jan 28 '13 at 10:55
    
Oracle behaves similarly in trying to guess which side is smaller. The performance hit comes in when performing hash joins. A hash table is built on the driving table then the outer table is probed for matches. The larger the driving rowset, the longer this will take. This can be particularly problematic if the driving source is too large to fit into memory so the hash table has to be written to disk. If the driving order can be reversed so the hash table is on the smaller rowset then this issue is reduced. –  Chris Saxon Jan 28 '13 at 11:52
    
Thanks Chris + Chris, I'm on the side of NOT creating dummy rows in RDBMS. As my OP indicated, dimensional models have this "feature". So I am wondering if a relational model could benefit, as well. As for OLTP, I can see this as being useful on "I don't know yet" scenario, if the column doesn't allow NULLs, which is another debate to itself. –  Robert Co Feb 2 '13 at 20:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.