By the way, when I added the ORDERED and USE_HASH hints to this query, I
did not encounter the errors about the TEMP tablespace utilization, as with
the MERGE SORT JOIN.
Thanks a ton, Jonathan, for your inputs.
Raj
"Jonathan Lewis"
<[EMAIL PROTECTED] To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
mon.co.uk> cc:
Sent by: Subject: Re: SMJ, NL or HJ
[EMAIL PROTECTED]
May 04, 2002 08:58
AM
Please respond to
ORACLE-L
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:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
