Re: Optimizer not pushing filters down into view

<mccmx_at_hotmail.com> wrote in message
news:1156318515.451456.152010_at_i42g2000cwa.googlegroups.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/community/oracle_ace/ace1.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