Re: Efficiency implications of outer joins

jurij.modic_at_mf.sigov.mail.si (Jurij Modic) wrote:

>
>I can give you a different example. Consider the tables SCOTT.EMP and
>SCOTT.DEPT. Both have indexes, EMP on column EMPNO and DEPT on DEPTNO.
>Look at the following query and its execution plan:
>
>SQL> set autotrace traceonly explain
>SQL> select /*+ first_rows */ dept.deptno, emp.empno from emp,dept
> 2 where dept.deptno = emp.deptno(+)
> 3 and emp.empno = 10;
>
>Execution Plan
>----------------------------------------------------------
> 0 SELECT STATEMENT Cost=1 Optimizer=HINT: FIRST_ROWS
> 1 0 NESTED LOOPS
> 2 1 TABLE ACCESS (BY ROWID) OF 'EMP'
> 3 2 INDEX (RANGE SCAN) OF 'PK_EMP'
> 4 1 INDEX (UNIQUE SCAN) OF 'PK_DEPT'
>
>Note that the hint FIRST_ROWS is inserted only to prevent the
>optimizer to use HASH_JOIN or MERGE_JOIN instead of NESTED_LOOPS. It
>doesn't force any table to be a driving table in NL join. As you can
>see here, the deficient table (EMP) in this case *is* the driving
>table, using its more or less selective index to reduce the number of
>rows processed!
>

Ok, having reproduced Jurij's example at my site, I'll concede that the optimiser doesn't ALWAYS select the non-deficient table as the driving table. I've done a little bit more investigation. What I forgot to mention in my original posting was that our applications have been designed and tuned to use the rule based optimiser.

Investigating further, it would appear that it is only the rule-based optimiser which forces the selection of the non-deficient table as the driving table, and only if it is using a nested-loops execution path. But this covers a lot of production cases! ( The optimiser uses hash joins of full table scans on very small tables like EMP and DEPT ).

What is interesting is that my original example, running under the rule based optimiser, uses the non-deficient table as the driving table. But if I specify an ALL_ROWS hint, then the cost-based optimiser kicks in and drives from the other table *even though no statistics have been collected for the affected tables*.

So, to answer my original question: "Have I missed something?" - Yes, my guideline only applies to the rule-based optimiser. The cost-based optimiser includes the suggested enhancement and applies it, even if no statistics have been collected for the affected tables.

Dave.

-- 

To reply by email, remove the "no-spam" bit from my email address.