Re: Eliminating cartesian merge

From: Ed Prochak <ed.prochak_at_magicinterface.com>
Date: 28 Sep 2005 06:49:08 -0700
Message-ID: <1127915348.554437.318870@g43g2000cwa.googlegroups.com>

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?? Received on Wed Sep 28 2005 - 08:49:08 CDT