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

Jaap W. van Dijk <j.w.vandijk.removethis_at_hetnet.nl> wrote:
> 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?

The hash table based on table A could include a field to mark each entry in A as being already matched against at least on row in B. Then, once all of B is streamed past the A hash table, go back over the hash table reporting rows that don't have that 'matched at least once' flag set.

Note that I am not saying that Oracle actually does this, but it is conceptually possible (and in fact I have done exactly this several times in non-Oracle contexts.)

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB