Re: SMJ, NL or HJ

Thanks Jonathan, for your inputs. I am trying to understand hash joins. New to it.

As I understand this, and looking at the plan that you have mentioned :

   hash

      table C
      hash
          table B
          table A

1. Tables B and C will be hashed in parallel, and Hash table for A will be created.
2. Rows from B will be probed against the hash table in A.
3. Rows from step 2 will be probed against the hash table in C.

Is my understanding right? Also, will my usage of hash joins reduce the TEMP tablespace utilization. I know for sure that none of these tables will fit in the hash area size. So, part of it will definitely be written to TEMP. But will this utilization be less than that of a merge sort join?

You say the memory usage will be twice the hash area size? From the little search that I have done on this parameter, I find no reference in the Oracle Docs. saying it will be so. Anyways, if you say it, it must be so ; -) Will look up at ixora from home.

Have a nice Weekend, e'one.
Raj

Something I found out during my research: HASH_AREA_SIZE is done away with in Oracle 9i, or retained for backward compatibility.

                                                                                                                          
                    "Jonathan Lewis"                                                                                      
                    <jonathan_at_jlcomp.de        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>        
                    mon.co.uk>                 cc:                                                                        
                    Sent by:                   Subject:     Re: SMJ, NL or HJ                                             
                    root_at_fatcity.com                                                                                      
                                                                                                                          
                                                                                                                          
                    May 03, 2002 04:13                                                                                    
                    PM                                                                                                    
                    Please respond to                                                                                     
                    ORACLE-L                                                                                              
                                                                                                                          
                                                                                                                          

Since table B and C are using the same
column to join to table A, then it should be possible to ensure that Oracle hashes
tables B and C at the same time, then
scans table A passing rows through
each hash in turn. (The order can be
permuted as necessary).

If you can set the hash area size to
something large enough you can
start getting your results through
without any I/O above a single table
scan of A B and C.

Remember that the total memory usage
in this case will be 2 x hash_area_size
though - one for table B, one for table C.

The path would be:

   hash

      table C
      hash
          table B
          table A

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

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

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 03 May 2002 18:43

|
|The CBO, presently does quite a good job. It chooses a sort merge
join on
|the tables. Given my understanding of the data distribution in the
tables,
|I agree its the best execution plan. But this kills my temporary
|tablespace, ORA-1652. To accomodate this query, I altered the sort
area for
|the session to a high value, and then, I took a hit on my temporary
|tablespace utilization, not withstanding the rather small values for
the
|extent sizes. And changing it would require me go thru a lot of
|bureaucracy, change management controls, approvals, the works.
|
|So, I was actually looking for a way to get around using sort merge
joins,
|and not compromise on performance. I would tend to use hash joins,
when a
|join happens between a smaller row source, and a large one. But that,
I
|know, is not the case here. I would like to drive this query via a
full
|table access, since I expect the query to return me about 90% of the
rows
|from each table. So, a nested loop is also not feasible.
|
|Left without an option, I guess. Headed now to put in a change
management
|request :(
|
|Raj
|
|
|
|>
|>Select ..............................
|>from largetableA a, largeTableB b, largeTableC c
|>where a.empnum = b.empnum
|>and a.empnum = c.empnum;
|>

--
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).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Rajesh.Rao_at_jpmchase.com

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).