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