Re: SMJ, NL or HJ

Notes inline.

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-----
| hash
| table C
| hash
| table B
| table A
|

Table C will be scanned and hashed
Table B will be scanned and hashed
Table A will be scanned

    rows will be tested against hash B
    successful rows will be tested against has C

        Successful rows will be forwarded to the next step.

I have not tested exhaustively the effects of this path when the tables are too large to hash in memory, but I would take as a first hypothesis that if only one tenth of table C fits in memory, and one eight of table B, then Oracle would choose the larger table to define the in memory pattiition size, so your usage of temp would be limited to

        9/10 of C requirements
+ 9/10 of B requirements
+ 9/10 of A requirments.

where 'requirements' for B and C is a measure of the number of rows that would be used for the two hash tables, bearing in mind that each row has to carry the hashkey column and the relevant data rows, and you lose about 10% of the hash_area_size to overheads. The requirements for A is the total size of the largest number of rows which may end up passing through the hash join

|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?
|

Utilisation is likely to be less than a sort/mergeas a large multiplass sort requires some input and output data to exist concurrently in the TEMP tablespace. The difference may not be large though.

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

If everything were in the Oracle Docs then this list wouldn't be about Oracle, it would be about the merits of different alcoholic beverages ;)

There is a note I spotted somewhere in the manuals once that said two concurrent hashes could be running concurrently. It's wrong, however: in an N-table join you could have N-1 concurrent hashes.

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

You have the option to forget about it (and sort_area_size and half a dozen others) if you let Oracle monitor PGA memory usage through the PGA_AGGREGATE_MAX feature. However it will still apply to shared servers (formerly MTS).

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