Re: hash join order

If you want to see it happen,
alter session set events '10104 trace name context forever, level 1'; alter session set events '10046 trace name context forever, level 8';

The first switches on the hash trace.
The second dumps wait states.

You can watch the hash taking place and see the data blocks being scanned at the same time (assuming you haven't managed to get the whole lot buffered).

It gets interesting when the data set from the first table (the b_tab in your case as Barbara pointed out) is too large to fit into memory in a hash table.

Jonathan Lewis

Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases See http://www.jlcomp.demon.co.uk/book_rev.html

For latest news of public appearances
See http://www.jlcomp.demon.co.uk

Screen saver or Life saver: http://www.ud.com Use spare CPU to assist in cancer research.

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 24 August 2001 15:59

|Steve:
|According to Orace The Complete Reference (in the Hitchhiker's Guide
to the
|Optimizer chapter), the 1st table is scanned via a table access full
and the
|database applies "hashing" functions to the data to prepare the table
for
|the join. The values from the second table are then read and the
hashing
|function is used to compare the 2nd table with the 1st.
|
|In your example the full table scan of b_tab and the index fast scan
are at
|the same level. Therefore the order is top-to-bottom, which makes
b_tab
|your driving table. My assumption is that b_tab is the object which
has the
|hashtable built on it.
|
|HTH
|Barb
|

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).