Re: Is 10g finally getting it right?: explain plan table-order for hash outer join

Jaap, in all versions of Oracle prior to 10g the table that all rows
(subject to where clasue restrictions) are to be returned from, table A
in your example, is the driving table for the join. The plus sign goes on the table that is to return null rows when the join condition is not meant which is table B in your first example.

With version 10g Oracle can sometimes drive on table B. Notice in your large table, small table plans that Oracle is full scanning both tables so even though the smaller table which may not have a matching row is chosen in 10g to be the driving table that because the large table is also full scanned Oracle is able to select every row from large_table
(A).

HTH -- Mark D Powell -- Received on Mon Jan 24 2005 - 08:34:23 CST