Re: Eliminating cartesian merge

"Chuck" <skilover_nospam_at_softhome.net> wrote in message news:1127916742.b86beac2d919d6bb3fbc8b1ab6476a5c_at_bubbanews...
> Jonathan Lewis wrote:
>>
>> 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 might post the execution path that you get with the RBO - it might give us a hint about why the execution plan is different.

There are cases where nested subqueries
can be transformed in 9.2 - somewhere
there's probably a list things that can work.

One idle thoght - not relevant to anything that's visible - is that transitive closure can convert

    column1 = 'constant'
and column2 = column1
into

    column1 ='constant'
and column2 = 'constant'

at which point 9.2 will eliminate the
join predicate - which can introduce
a cartesian join. If you can find this
happening somewhere in the depths
of your views, then this particular
predicate elimination is stopped when
you query_rewrite_enabled = true
(don't ask why - it just is).

This does have a side effect on
cost and cardinality calculations,
of course, so it's not a great fix
to your problem, even if it does
work.

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