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

Hi,

A question has been playing in the back of my mind for some time: when hash outer joining table B to table A, like this:

select * from A,B were A.id = B.id (+)

all records of table A have to be written to output, and this can only be done when table B is read first and converted in to a internal hash-table, and then table A is read and compared with this hash table. Otherwise: If table A was read first, how would Oracle take care of the records in the external hash-table that have no counterpart in table B, and assure that they are written to output too?

But if you check out the explain plan until Oracle 9, table B is invariably put last. (this in contrast with a normal hash join between a big and a small table: in that case the small table is put first).

Now it seems 10g has finally put this right (quoting from the presentation of Jonathan Lewis 'Evolution in Optimisation - 8i to 10g'):

"select count(st.padding),count(lt.padding) from small_table,large_table
where small_table.id(+) = large_table.n1;

0 SELECT STATEMENT
1 0 SORT (AGGREGATE) 2 1 HASH JOIN (OUTER) -- v9.2
3 2 TABLE ACCESS (FULL) OF 'LARGE_TABLE' 4 2 TABLE ACCESS (FULL) OF 'SMALL_TABLE' 2 1 HASH JOIN (RIGHT OUTER) -- 10.1
3 2 TABLE ACCESS (FULL) OF 'SMALL_TABLE' 4 2 TABLE ACCESS (FULL) OF 'LARGE_TABLE' " Jaap. Received on Sun Jan 23 2005 - 12:46:38 CST