Re: Eliminating cartesian merge

mccmx_at_hotmail.com wrote:
> Chuck,
>
> I've been fighting a similar battle with Peoplesoft HRMS and Oracle
> 9.2.0.6.... I have identified several 'bugs' with the Oracle optimizer
> over the past 6 months (all related to the optimizers <in>ability to
> handle subqueries).
>
> I would personally not create a RULE based outline for that one query
> because you will be adding an overhead to every single SQL statement
> during its parse phase. I would make sure that parse time is not a
> significant portion of your response time before implementing this
> solution.
>
> Is the query in question delivered or customized...?
>
> Matt
>

It's a delivered query and I'm probably hitting the same bug(s) you are. The CBO doesn't optimize queries against views containing subqueries very well. I'm not certain it's really a bug though as the 9i tuning manual specifically states that views containing subqueries are not mergeable and that's exactly what appears to be happening.

For testing purposes I rewrote one of the views converting the subqueries to joins, and saved it in a test schema. When I ran a query against this view from the test schema, the CBO did a much better job of optimizing the query, but it still didn't touch the performance I got from a RULE hint.

-- 
To reply by email remove "_nospam"