Question about hash jion

From: Orlando L <oralrnr_at_gmail.com>
Date: Tue, 19 Mar 2019 18:36:42 -0500
Message-ID: <CAL8Ae75i0eGwmR8fvBLoGrG4eUboHRu6WDaFWPfiqOL34VBK5A_at_mail.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 Received on Wed Mar 20 2019 - 00:36:42 CET