Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Always_semi_join
Hash search is a fast search algorithm which works on sets. If we
have set A with elements a1,...,aN, then hashing algorithm would work
roughly as follows:
If we wanted to search for an element a, we would compute f(a), go to H[f(a)] and compare a with all elements in the queue, instead of comparing it against all elements of the set A. I will not delve into collisions and perfect hashing functions. This description suffices for my purpose. A simple, but very good implementation of hash algorithm can be found in the Bible (Kernighan & Ritchie, The C Programming Language)
>From the above description we can read the following: the whole set A is used in
constructing hash table. Database tables are sets as well (as a matter of fact, set
theory was the mathematical theory after which Codd & Date modeled their relational
theory) which means that the whole table must be read to construct a hash table.
Bigger the database table, bigger gets the hash table. If you have monstrous tables
with 100M records, hash table can get rather substantial. In case of a dedicated
connection, hash table is stored in PGA (shared connection stores it in LARGE_POOL)
but if the table is really big, parts of the table are swapped to temporary extents
in the temp tablespace. That means that for hash join, you'll not only be reading the
two involved tables, but also the hash table. Add paging to adequately increase the
address space and you'll see that the whole thing will slow down drastically.
A corollary to what was said above is that you don't want the hash table to be
swapped to disk. That, in turn, means that you dont want to hash really big tables.
That is what sort/merge is for. The maximum size of an in-memory hash table is
HASH_AREA_SIZE. For sorts oracle has decent statistics like the ones below.
SQL> select name, value from v$sysstat where name like '%sort%';
NAME VALUE ---------------------------------------------------------------- ---------- sorts (memory) 846 sorts (disk) 0 sorts (rows) 2339
For hashing algorithm, no such statistics is available. The only ones that we get are the incomprehensible ones listed below:
SQL> select name, value from v$sysstat where name like '%hash%';
NAME VALUE ---------------------------------------------------------------- ---------- shared hash latch upgrades - no wait 2024 shared hash latch upgrades - wait 0 native hash arithmetic execute 0 native hash arithmetic fail 0
Cary might know some other way to monitor he efficiency of our hash setup in the database. I'm just a mere mortal so I have to use top and physical reads statistics in order to detect processes growing out of control. This is frequently caused by hashing.
On 2003.05.20 13:38 Ramon E. Estevez wrote:
> Tks,
>
> So it is only advisable when there's a small table involve in the query.
>
>
>
> Ramon E. Estevez
> restevez_at_blh.com.do
> 809-535-8994
>
>
>
> -----Original Message-----
> rgaffuri_at_cox.net
> Sent: Tuesday, May 20, 2003 11:47 AM
> To: Multiple recipients of list ORACLE-L
>
>
> hash always requires a full table scan. So you use hash's when you need
> to return large amounts of data from each and/or there are large
> differences in sizes of the two tables.
>
> you can set always_semi_join with alter session and from EXECUTE
> IMMEDIATE.
>
> chage it as needed.
> >
> > From: "Ramon E. Estevez" <restevez_at_blh.com.do>
> > Date: 2003/05/20 Tue AM 11:06:41 EDT
> > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > Subject: Always_semi_join
> >
> > Hi List,
> >
> > I change the parameter ALWAYS_SEMI_JOIN to HASH in a DB and ran a
> > report and last forever, In another DB, without the parameter, ran in
> > 12 seconds. This was the only process that was
> > Affected with the parameter.
> >
> > Why a FTS if I am using a index in the where clause, will the DB
> > ALWAYS use that method.
> >
> > Kind of confused.
> >
> > TIA
> >
> > **---
> > BD WITH ALWAYS_SEMI_JOIN=STANDARD
> > **---
> > QUERY_PLAN
> > ----------------------------------------------------------------------
> > --
> > --
> > SORT ORDER BY
> > FILTER
> > NESTED LOOPS
> > NESTED LOOPS
> > TABLE ACCESS BY INDEX ROWID TPLA_ACCRUAL
> > INDEX RANGE SCAN CP01CAP_ACC
> > TABLE ACCESS BY INDEX ROWID TPLA_CUENTA
> > INDEX UNIQUE SCAN CP01PLA_PDA
> > TABLE ACCESS BY INDEX ROWID TCLI_PERSONA
> > INDEX UNIQUE SCAN CP01CLI_CLI
> > FILTER
> > INDEX RANGE SCAN CP01CAP_ACC
> >
> > 12 rows selected.
> >
> > ************************************************
> > **---
> > BD WITH ALWAYS_SEMI_JOIN=HASH
> > **---
> > QUERY_PLAN
> > ----------------------------------------------------------------------
> > --
> > ---
> > SORT ORDER BY
> > MERGE JOIN CARTESIAN
> > HASH JOIN
> > TABLE ACCESS BY LOCAL INDEX ROWID TPLA_ACCRUAL
> > INDEX RANGE SCAN CP01CAP_ACC
> > HASH JOIN
> > TABLE ACCESS FULL TPLA_CUENTA
> > TABLE ACCESS FULL TCLI_PERSONA
> > SORT JOIN
> > INDEX RANGE SCAN CP01CAP_ACC
> >
> > 10 rows selected.
> >
> >
> >
> > Ramon E. Estevez
> > restevez_at_blh.com.do
> > 809-535-8994
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Ramon E. Estevez
> > INET: restevez_at_blh.com.do
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > 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.net
> --
> Author: <rgaffuri_at_cox.net
> INET: rgaffuri_at_cox.net
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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.net
> --
> Author: Ramon E. Estevez
> INET: restevez_at_blh.com.do
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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).
>
-- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: mgogala_at_adelphia.net Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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).Received on Wed May 21 2003 - 00:21:41 CDT