Eliminating cartesian merge

From: Chuck <skilover_nospam_at_softhome.net>
Date: Wed, 28 Sep 2005 09:19:16 -0400
Message-ID: <1127909324.69cd14524dfb36100993fe390fb40c75@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  	 	      	             	

Received on Wed Sep 28 2005 - 08:19:16 CDT