Re: Eliminating cartesian merge

"Terry Dykstra" <tddykstra_at_forestoil.ca> wrote in message news:y0y_e.276338$tt5.43955_at_edtnps90...
> Couldn't help but notice:
> AND ( job.effdt >= TO_DATE (TO_CHAR (SYSDATE, 'YYYY-MM-DD'),
> 'YYYY-MM-DD')
>
> Why so complicated? I would code:
>
> AND ( job.effdt >= TRUNC(SYSDATE)
>

Interesting little detail that, because in 9.2, trunc(sysdate) is a known constant, but
the other expression is an unknown with
a selectivity of 5%. It's a change that could result in a completely different
execution plan.

I'm still thinking about the original query, by the way. It looks like there may be
other views in there as well - there
are more max() events going on than
seem to be visible, and too many tables
in the plan.

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;

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

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