Re: Eliminating cartesian merge

Couldn't help but notice:
AND ( job.effdt >= TO_DATE (TO_CHAR (SYSDATE, 'YYYY-MM-DD'), 'YYYY-MM-DD')

Why so complicated? I would code:

AND ( job.effdt >= TRUNC(SYSDATE)

-- 
Terry Dykstra
Canadian Forest Oil Ltd.


"Chuck" <skilover_nospam_at_softhome.net> wrote in message
news:1127909324.69cd14524dfb36100993fe390fb40c75_at_bubbanews...


> Oracle 9.2.0.5

>

> I've got a query on a Peoplesoft HR database where the optimizer insists

on


> picking a Cartesian join between two tables. All stats are up to date and

> there are no missing join criteria, so why is it picking a artesian? It's

an


> OLTP query that runs for 2 minutes. Placing a RULE hint on it eliminates

the


> artesian, and causes it to run in milliseconds. What can be done to force

> the optimizer not to choose a artesian?

>

> SELECT DISTINCT emplid, company, NAME

>            FROM ps_empl_comp_srch4 z

>           WHERE rowsecclass = :1

>        ORDER BY emplid, company;

>

> ps_empl_com_srch4 is a view defined as follows:

>

> CREATE OR REPLACE VIEW ps_empl_comp_srch4

> (emplid,

> company,

> rowsecclass,

> access_cd,

> NAME,

> name_ac,

> last_name_srch

> )

> AS

> SELECT a.emplid, job.company, sec.rowsecclass, sec.access_cd, a.NAME,

> a.name_ac, a.last_name_srch

> FROM ps_person_name a, ps_job job, ps_scrty_tbl_dept sec

> WHERE a.emplid = job.emplid

> AND sec.access_cd = 'Y'

> AND EXISTS (

> SELECT 'X'

> FROM pstreenode tn

> WHERE tn.setid = sec.setid

> AND tn.setid = job.setid_dept

> AND tn.tree_name = 'DEPT_SECURITY'

> AND tn.effdt = sec.tree_effdt

> AND tn.tree_node = job.deptid

> AND tn.tree_node_num BETWEEN sec.tree_node_num

> AND sec.tree_node_num_end

> AND NOT EXISTS (

> SELECT 'X'

> FROM ps_scrty_tbl_dept sec2

> WHERE sec.rowsecclass = sec2.rowsecclass

> AND sec.setid = sec2.setid

> AND sec.tree_node_num <> sec2.tree_node_num

> AND tn.tree_node_num BETWEEN sec2.tree_node_num

> AND sec2.tree_node_num_end

> AND sec2.tree_node_num BETWEEN sec.tree_node_num

> AND sec.tree_node_num_end))

> AND (   job.effdt >=

> TO_DATE (TO_CHAR (SYSDATE, 'YYYY-MM-DD'), 'YYYY-MM-DD')

> OR (    job.effdt =

> (SELECT MAX (job2.effdt)

> FROM ps_job job2

> WHERE job.emplid = job2.emplid

> AND job.empl_rcd = job2.empl_rcd

> AND job2.effdt <=

> TO_DATE(TO_CHAR (SYSDATE, 'YYYY-MM-DD'),

'YYYY-MM-DD'))


> AND job.effseq =

> (SELECT MAX (job3.effseq)

> FROM ps_job job3

> WHERE job.emplid = job3.emplid

> AND job.empl_rcd = job3.empl_rcd

> AND job.effdt = job3.effdt)

> ))

> AND job.appt_type <> '1'

>

> Execution plan is below...

>

>

> Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

>

> SELECT STATEMENT Optimizer Mode=CHOOSE 1  11

>   SORT UNIQUE 1  164  8

>     FILTER

>       TABLE ACCESS BY INDEX ROWID SYSADM.PS_JOB 1  38  2

>         NESTED LOOPS 1  164  7

>           MERGE JOIN CARTESIAN 1  126  6

>             TABLE ACCESS BY INDEX ROWID SYSADM.PSTREENODE 1  48  2

>               NESTED LOOPS 1  89  3

>                 TABLE ACCESS BY INDEX ROWID SYSADM.PS_SCRTY_TBL_DEPT 2  82

2


>                   INDEX RANGE SCAN SYSADM.PSBSCRTY_TBL_DEPT 60  2

>                 INDEX RANGE SCAN SYSADM.PSFPSTREENODE 26  1

>             BUFFER SORT 2  74  4

>               INDEX FULL SCAN SYSADM.PS0NAMES 2  74  11

>                 SORT AGGREGATE 1  19

>                   FILTER

>                     INDEX RANGE SCAN SYSADM.PS_NAMES 1  19  3

>                     SORT AGGREGATE 1  19

>                       FIRST ROW 1  19  3

>                         INDEX RANGE SCAN (MIN/MAX) SYSADM.PS_NAMES 5 K 3

>           INDEX RANGE SCAN SYSADM.PS_JOB 7  2

>       SORT AGGREGATE 1  17

>         FIRST ROW 1  17  3

>           INDEX RANGE SCAN (MIN/MAX) SYSADM.PSAJOB 36 K 3

>       SORT AGGREGATE 1  20

>         FIRST ROW 1  20  3

>           INDEX RANGE SCAN (MIN/MAX) SYSADM.PSAJOB 36 K 3

>       FILTER

>         TABLE ACCESS BY INDEX ROWID SYSADM.PS_SCRTY_TBL_DEPT 1  31  2

>           INDEX RANGE SCAN SYSADM.PS_SCRTY_TBL_DEPT 1  2