Re: Question about hash jion

Orlando,

> If it is case 1, I would think the more hash joins there are in the plan,
the more the PGA requirements will be to hash multiple (big) tables at the same time if it all hash joins nested to multiple levels.

It's hard to say more or less, because in case 2 you would have to store whole resultsets of hash joins, which can be much bigger than probe tables.

> Is there a way to prove the answer, say by trace data or something?

I forgot to mention that you can also monitor it using v$sql_workarea_active.

ср, 20 мар. 2019 г., 5:33 Sayan Malakshinov <xt.and.r_at_gmail.com>:

> Hi Orlando,
>
> > 1) can I assume that as soon as oracle finds a matching row between t1
> and t2, oracle *immediately* compares that matching row to t3?
> Yes, of course. You can read Jonathan's articles about how to read
> execution plans. He suggests “first child first, recursive descent”
> mnemonic rule for interpreting plans. In short and with some simplification
> we can say that each plan operation is a function which starts and stops
> own descendent plan operations(from top to bottom), processes their output
> and pipes own results to parent operation.
>
> > Is there a way to prove the answer, say by trace data or something?
>
> Of course, IMHO the easiest way to do this is to use real-time sql monitor
> in "active" mode, but you can also analyze raw sql trace 10046. BTW, Franck
> Pachot has a very good demonstration for that: he uses gdb breakpoints and
> shows each step.
>
> ср, 20 мар. 2019 г., 2:37 Orlando L <oralrnr_at_gmail.com>:
>
>>
>> I am reading the book on CBO fundamentals by Jonathan and I have a
>> question on hash join. He mentions (P289) that in the following hash join
>> scenario, the first join is T1->T2, with T1 the probed table, and if that
>> probe is successful, Oracle probes t3 (already hashed) for a match. Since
>> t3 and t1 are hashed in memory, 1) can I assume that as soon as oracle
>> finds a matching row between t1 and t2, oracle *immediately* compares that
>> matching row to t3? OR 2) does it wait to build a list of matching (t1,t2)
>> rows and THEN compare each row in that list to t3?
>>
>> I have been thinking all along that it was case 2. If it is case 1, I
>> would think the more hash joins there are in the plan, the more the PGA
>> requirements will be to hash multiple (big) tables at the same time if it
>> all hash joins nested to multiple levels.
>>
>> Execution PlanS (10.1.0.3 autotrace)
>> ----------------------------------------------------------
>> 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=212 Card=9551
>> Bytes=573060)
>> 1 0 HASH JOIN (Cost=212 Card=9551 Bytes=573060)
>> 2 1 TABLE ACCESS (FULL) OF 'T3' (TABLE) (Cost=53 Card=10000
>> Bytes=200000)
>> 3 1 HASH JOIN (Cost=120 Card=62500 Bytes=2500000)
>> 4 3 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=54 Card=10000
>> Bytes=200000)
>> 5 3 TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=53 Card=10000
>> Bytes=200000)
>>
>>
>> Is there a way to prove the answer, say by trace data or something?
>>
>> PS. I can finally appreciate what a great book CBOF is. Hope he publishes
>> a new book for the newer versions of Oracle.
>>
>> Orlando.
>>
>>
>>

--
http://www.freelists.org/webpage/oracle-l