Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: RE: Always_semi_join

Re: RE: Always_semi_join

From: <rgaffuri_at_cox.net>
Date: Tue, 20 May 2003 10:03:17 -0800
Message-ID: <F001.0059E137.20030520100317@fatcity.com>


no. If you have two large tables and you need to return a large number of records, then HASH is advisable also. Someone correct me here, but primarily its based on.

  1. Relative tablesizes
  2. Overall tablesizes
  3. How many records will the query produce.

Could be wrong... its the rules I use.
>
> From: "Ramon E. Estevez" <restevez_at_blh.com.do>
> Date: 2003/05/20 Tue PM 01:38:10 EDT
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Subject: RE: Always_semi_join
>
> 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).
>
>

-- 
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).
Received on Tue May 20 2003 - 13:03:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US