inner or outer tabe is hashed?

From: mark <shad0wek_at_poczta.onet.pl>
Date: Mon, 21 Feb 2005 16:44:29 +0100
Message-ID: <cvcvl3$1dr$1@news.onet.pl>

Guys, in hash join which table is hashed (inner or outer)? I have two tables, KLIENT has 362 blocks, and ZAMOWIENIE has 5842 blocks. The block size is 4KB. I also disabled PGA_AGGREGATE_TARGET and set HASH_AREA_SIZE manualy to about 2MB (the ZAMOWIENIE table won't fit in that space and there will by physical writes in $sessionstat reported).

The hash table created from the smaller table(KLIENT), because therer is no 'physical writes' reported in v$sessionstat : select /*+ORDERED*/ count(*) from klient k,zamowienie z where k.id=z.klient_id;
Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=637 Card=1 Bytes=16)    1 0 SORT (AGGREGATE)

   2    1     HASH JOIN (Cost=637 Card=1000000 Bytes=16000000)
   3    2       TABLE ACCESS (FULL) OF 'KLIENT' (Cost=36 Card=10000 
Bytes=130000)
   4    2       TABLE ACCESS (FULL) OF 'ZAMOWIENIE' (Cost=563 Card=1000000 

Bytes=3000000)

The hash table created from the bigger one (ZAMOWIENIE), there are physical writes :
select /*+ORDERED*/ count(*) from zamowienie z,klient k where k.id=z.klient_id;
Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=838 Card=1 Bytes=16)    1 0 SORT (AGGREGATE)

   2    1     HASH JOIN (Cost=838 Card=1000000 Bytes=16000000)
   3    2       TABLE ACCESS (FULL) OF 'ZAMOWIENIE' (Cost=563 Card=1000000 
Bytes=3000000)
   4    2       TABLE ACCESS (FULL) OF 'KLIENT' (Cost=36 Card=10000 

Bytes=130000)

So according to this the hash table is created from the outer table? Received on Mon Feb 21 2005 - 09:44:29 CST