AW: Always_semi_join

Very good explanation. How does Oracle know, how to set up an optimal starting config for the hash table ? Statistics like number of rows and distinct values ? It would be interesting to know if it's possible to influence the hash function (since you might know better what the data looks like, which could safe Oracle time to go and figure that out for you).

Stefan Jahnke
Consultant
BOV Aktiengesellschaft
Voice: +49 201 - 4513-298
Fax: +49 201 - 4513-149
mailto: stefan.jahnke_at_nospam.bov.de
Please remove nospam to contact me via email.

visit our website: http://www.bov.de
subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp

Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an mailto:andrea.palluck_at_bov.de.

Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen.

As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above.

-----Ursprüngliche Nachricht-----
Von: Mladen Gogala [mailto:mgogala_at_adelphia.net] Gesendet: Mittwoch, 21. Mai 2003 07:22
An: Multiple recipients of list ORACLE-L Betreff: 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:

1. An array H of size M, M<N would be allocated. That array is called hash table.
2. A function f:A--> [1,..,M] would be defined. This function is called a hash function.
3. Each element a of the set A would be queued up to H[f(a)] , with H[f(a)] serving as a queue head.

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


 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stefan Jahnke
  INET: Stefan.Jahnke_at_bov.de

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