Re: Eliminating cartesian merge

Jonathan Lewis wrote:
>
> You mention in another post that the query runs quickly
> when you don't have the distinct - does the plan change
> significantly in that case ?

It eliminates one index range scan that just happens to be joined to the Cartesian product of two other tables. Is this ugly or what!

The original explain plan shows that oracle's estimating 7 rows to be processed by this range scan. The sql trace OTOH shows about 17 million rows being processed at that step! FYI here's the explain output of tkprof for the query.

SELECT
DISTINCT emplid, company, NAME

           FROM ps_empl_comp_srch4
          WHERE rowsecclass = :rsc
       ORDER BY emplid, company


Rows     Row Source Operation
-------  ---------------------------------------------------

   5023 SORT UNIQUE
   5024 FILTER
  36959 TABLE ACCESS BY INDEX ROWID PS_JOB

19679401     NESTED LOOPS
2382588      MERGE JOIN CARTESIAN
    468       TABLE ACCESS BY INDEX ROWID PSTREENODE
   3753        NESTED LOOPS
      8         TABLE ACCESS BY INDEX ROWID PS_SCRTY_TBL_DEPT
    102          INDEX RANGE SCAN PSBSCRTY_TBL_DEPT (object id 237347)
   3744         INDEX RANGE SCAN PSFPSTREENODE (object id 218406)
2382588       BUFFER SORT
   5091        INDEX FULL SCAN PS0NAMES (object id 233963)
   5093         SORT AGGREGATE
   5349          FILTER
   5349           INDEX RANGE SCAN PS_NAMES (object id 233962)
      1           SORT AGGREGATE
      1            FIRST ROW
      1             INDEX RANGE SCAN (MIN/MAX) PS_NAMES (object id 233962)
17296812      INDEX RANGE SCAN PS_JOB (object id 233312)
   6321    SORT AGGREGATE
   6321     FIRST ROW
   6321      INDEX RANGE SCAN (MIN/MAX) PSAJOB (object id 233317)
   5093    SORT AGGREGATE
   5093     FIRST ROW
   5093      INDEX RANGE SCAN (MIN/MAX) PSAJOB (object id 233317)
     31    FILTER
     31     TABLE ACCESS BY INDEX ROWID PS_SCRTY_TBL_DEPT
    601      INDEX RANGE SCAN PS_SCRTY_TBL_DEPT (object id 237345)

-- 
To reply by email remove "_nospam"