| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Always_semi_join
Thanks Mladen, excellent exposicion.
Tks
Ramon E. Estevez
restevez_at_blh.com.do
809-535-8994
-----Original Message-----
Gogala
Sent: Wednesday, May 21, 2003 12:22 AM
To: Multiple recipients of list ORACLE-L
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
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
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). -- 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).Received on Wed May 21 2003 - 18:06:38 CDT
 |
 |