Re: Eliminating cartesian merge

"Chuck" <skilover_nospam_at_softhome.net> wrote in message news:1127918214.a86aa150f9acb770b1c7365331b2c2f4_at_bubbanews...
>
> Query_rewrite_enabled is already set to true. Here's the execution plan
> with the RULE hint. You'll probably need to turn of line wrapping for it
> to make any sense.
>
> ----------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows |
> Bytes | Cost |
> ----------------------------------------------------------------------------------------

Chuck,

Comparing the two plans, the most significant thing looks like the subquery pushing that the CBO has used to apply the subqueries in the view ps_person_name as early as possible. It's possible that this has resulted in a silly cardinality at that point in the plan.

You might want to test the no_push_subq hint, either in the select of the main query, or in the select of the query definition of the view ps_person_name to see if this gets you back to the RBO path.

You mention in another post that the query runs quickly when you don't have the distinct - does the plan change significantly in that case ?

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle - Volume 1: Fundamentals
On-shelf date: Nov 2005

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Sept 2005