Re: Eliminating cartesian merge

Jonathan Lewis wrote:

> As a quick and dirty - you could disable
> complex view merging. 9.2 is better at
> it than 8.1, but sometimes the results are
> not an improvement.
>
> As an experiment, you could try:
> alter session set "_complex_view_merging" = false;

It's already been tried and didn't help.

>
> You could also try putting a no_merge hint into
> the query, perhaps using a global hints to reference
> objects inside the view.
>
>

I don't think that'll help either. IIRC a view that contains nested subqueries is non-mergeable anyway. But to be sure I did try a no_merge hint to no avail.

You are correct in that there is another view nested inside the view. Ps_person_view is a view on a single table names ps_names. It joins ps_names with itself via a subquery as these peoplsoft views are all prone to do, to select the proper row based on an effective date.

Now that Oracle owns Peoplsoft, I really do hope they convert all these crazy subqueries in to straight up joins. The optimizer seems to be able to handle them much better.

I'm about ready to create an outline for the query using a RULE hint (which returns very quickly). I really hate doing that though. IMO the optimizer ought to be able to figure these things out by itself.

-- 
To reply by email remove "_nospam"