Re: Efficiency implications of outer joins

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/02/25
Message-ID: <34f4ac75.12159425@www.sigov.si>#1/1

On 25 Feb 1998 15:42:39 GMT, Dave Wotton <Dave.Wotton_at_it.camcnty.no-spam.gov.uk> wrote:

>...[SNIP]...
>A much more significant effect of outer joins is that it forces the
>query to select the non-deficient table as the driving table.

IMHO you are jumping to conclusions too fast. It is not a general rule that a non-deficient table in an outer join is allways chosen as a driving table in nested-loop join. See below.

>This may be undesirable. Consider the following example:
>
> SELECT ORDER.CUSTOMER_NO,
> ORD_LINE.AMOUNT
> FROM ORDER, ORDER_LINE
> WHERE ORDER.ORDER_NO = ORD_LINE.ORDER_NO(+)
>
>This prints out orders, displaying the customer number ( from the
>ORDER table ) and the amount for each line ( from ORD_LINE ), and
>includes orders which have no lines. The query is parsed as a
>nested-loops construct, with the ORDER table as the driving table.
>
>Suppose we extend the query as follows:
>
> SELECT ORDER.CUSTOMER_NO,
> ORD_LINE.AMOUNT
> FROM ORDER, ORDER_LINE
> WHERE ORDER.ORDER_NO = ORD_LINE.ORDER_NO(+)
> AND ORD_LINE.ITEM_NO = 7
>
>The query will still be driven by ORDER. This is particularly
>distressing if there are thousands of orders but the index on
>ORDER_LINE.ITEM_NO was very selective and would have reduced the
>number of rows processed had it been used.

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!

>...[SNIP]...
>My question: since there's a fairly simple way of deciding when it's safe
>to replace an outer join by a standard one, enabling the optimiser to have
>much more flexibility in choosing an efficient access path, why doesn't
>the optimiser do it itself?

Well, in my example the optimizer did it. It in fact did replace an outer join with a regular one, because in the PLAN_TABLE.OPTIONS column tere is a NULL value in the record with the PLAN_TABLE.OPERATION = 'NESTED LOOP'. When the optimizer performs an outer join, the value of OPTIONS in plan table is allways 'OUTER'.

> Or have I missed something? ( We're currently
>using Oracle 7.3.3 )
>
>Dave.

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Wed Feb 25 1998 - 00:00:00 CST