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

On 24 Jan 2005 17:58:23 GMT, xhoster_at_gmail.com wrote:

>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

I think it must be something you described, because I did a test on a hash outer join and I saw (by means of the columns P1 and P2 from v$session_wait for a scattered read) that Oracle really was reading the table first that was to be outer joined, just as the explain plan (and v$sql_plan) said it would/was.

Thanks you and Mark for your input.

Jaap. Received on Tue Jan 25 2005 - 13:32:26 CST