Re: Eliminating cartesian merge

From: Chuck <skilover_nospam_at_softhome.net>
Date: Wed, 28 Sep 2005 10:19:20 -0400
Message-ID: <1127912927.e22e6b51bef15ac43fac298d0dfd0830@bubbanews>

Ed Prochak wrote:

> Chuck wrote:
> 

>>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?
>>

> 
> 

>>CREATE OR REPLACE VIEW ps_empl_comp_srch4

> 
> []
> 

>>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 (

> 
> [subqueries not related to ps_scrty_tbl_dept]
> 

>>AND job.appt_type <> '1'

> 
> 
> How about by not making a cartesian join? there isn't any join between
> ps_scrty_tbl_dept and the other tables. Is there a where condition
> missing??
> 

It's joined through one of the subqueries.

If run a vanilla query against the view, I get no cartesians. It's only when I add the WHERE criteria or the DISTINCT to the query that I start seeing cartesians.

-- 
To reply by email remove "_nospam"

Received on Wed Sep 28 2005 - 09:19:20 CDT