<mccmx@hotmail.com> wrote in message
news:1156318515.451456.152010@i42g2000cwa.googlegr oups.com...
>
> The dbms_xplan output doesn't display well, I've posted the tkprof
> output which is a little better. In the first query the filters are
> being pushed down into PS_TM_PEFF_GPQCAL table(180,000 rows). This
> trace is from a testcase, the real database has over 12 Million rows in
> that table. In the second query the filters are not being pushed.
>

> Rows Row Source Operation
> ------- ---------------------------------------------------
> 1 SORT AGGREGATE (cr=25225 pr=0 pw=0 time=493892 us)
> 78 VIEW PS_TM_PEFF_V_SHDAY (cr=25225 pr=0 pw=0 time=494473 us)
> 78 SORT UNIQUE (cr=25225 pr=0 pw=0 time=494154 us)
> 78 UNION-ALL (cr=25225 pr=0 pw=0 time=489378 us)
> 50 FILTER (cr=25125 pr=0 pw=0 time=487689 us)


> 78 UNION-ALL PARTITION (cr=532388 pr=0 pw=0 time=12111746 us)
> 50 FILTER (cr=532288 pr=0 pw=0 time=12073561 us)


General clues:

The predicates you supplied can be propagated
and moved by transitive closure, but I can't tell
whether that's relevant because you have to look
at the dbms_xplan() output to see what predicates
Oracle is using. Note particularly that your join
predicate CAN disappear, or be supplemented by
an extra constant predicate.

It is possible that some accidental side effect of
moving your predicate
> where A.BUSINESS_UNIT = 'TMUK'
through the join predicate
> and A.BUSINESS_UNIT = B.BUSINESS_UNIT
to become
> and B.BUSINESS_UNIT = 'TMUK'
may have stopped Oracle from also moving it down
properly into the PS_TM_PEFF_V_CCEH view.

Note also that that view is defined with an ANSI
join, and there are bugs in ANSI transformations -
so it is possible that you have hit a problem as
Oracle implemented a PARTITION VIEW approach
to your query (see UNION ALL PARTIION - line 4
of the second plan), and then failed to handle the ANSI
view inside one half of the UNION ALL.


You also have a set of hints that may be causing problems.
You don't have enough to fix an execution path - you only
have enough to deal with a couple of details in a couple of
places. It is possible that some aspect of your hint set is
making Oracle do something inappropriate.



--
Regards

Jonathan Lewis
http://www.oracle.com/technology/com...ce1.html#lewis

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

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html