The first table in the join order (usually called
the outer table because that is a term that makes
sense in a nested loop) is the one that is hashed.
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated Jan 21st 2005
"mark" <shad0wek_at_poczta.onet.pl> wrote in message
news:cvcvl3$1dr$1_at_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 - 10:13:21 CST
