Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: inner or outer tabe is hashed?

Re: inner or outer tabe is hashed?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 21 Feb 2005 16:13:21 +0000 (UTC)
Message-ID: <cvd1b1$qe9$1@hercules.btinternet.com>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US