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.
