Oracle 10.2.0.2 SE on W2K3.

Is this a bug with the optimizer in 10g:

I am seeing behaviour where constant filters are not being passed down
into views in certain situations.....

Q1. Query where filters are pushed successfully:

select count(*) from PS_TM_PEFF_V_SHDAY
where BUSINESS_UNIT = 'TMUK'
and tm_shop_code = 'AA1'
and tm_date = to_date('2006-07-28','YYYY-MM-DD');

Q2. Query where filters are not pushed:

select count(*) from PS_TM_PEFF_V_SHDAY A, PS_TM_PEFF_TWTCAL B
where A.BUSINESS_UNIT = 'TMUK'
and A.tm_shop_code = 'AA1'
and A.tm_date = to_date('2006-07-28','YYYY-MM-DD')
and A.BUSINESS_UNIT = B.BUSINESS_UNIT;

The queries differ only in that I have added one extra table into the
top level query.

According to the tkprof and the autotrace output, the first query
applies the filters on the base table contained in the view definition,
whereas in the second query the filters are being applied much later -
after the tables are joined. This is causing Oracle to join millions
of rows unneccesarily.

Its my understanding that 'constant' filters are always pushed into
views but Join Predicates can be passed under certain circumstances.

Any ideas why I amy be seeing this behaviour..

I can post the view definitions and full plans if needed....

Matt

mccmx@hotmail.com wrote:

> I can post the view definitions and full plans if needed....

That's an excellent idea! Why didn't you do it in the first place?

robert

<mccmx@hotmail.com> wrote in message
news:1156243313.655389.260830@b28g2000cwb.googlegr oups.com...
> Oracle 10.2.0.2 SE on W2K3.
>
> Is this a bug with the optimizer in 10g:
>
> I am seeing behaviour where constant filters are not being passed down
> into views in certain situations.....
>
> Q1. Query where filters are pushed successfully:
>
> select count(*) from PS_TM_PEFF_V_SHDAY
> where BUSINESS_UNIT = 'TMUK'
> and tm_shop_code = 'AA1'
> and tm_date = to_date('2006-07-28','YYYY-MM-DD');
>
> Q2. Query where filters are not pushed:
>
> select count(*) from PS_TM_PEFF_V_SHDAY A, PS_TM_PEFF_TWTCAL B
> where A.BUSINESS_UNIT = 'TMUK'
> and A.tm_shop_code = 'AA1'
> and A.tm_date = to_date('2006-07-28','YYYY-MM-DD')
> and A.BUSINESS_UNIT = B.BUSINESS_UNIT;
>
> The queries differ only in that I have added one extra table into the
> top level query.
>
> According to the tkprof and the autotrace output, the first query
> applies the filters on the base table contained in the view definition,
> whereas in the second query the filters are being applied much later -
> after the tables are joined. This is causing Oracle to join millions
> of rows unneccesarily.
>
> Its my understanding that 'constant' filters are always pushed into
> views but Join Predicates can be passed under certain circumstances.
>
> Any ideas why I amy be seeing this behaviour..
>
> I can post the view definitions and full plans if needed....
>
> Matt
>

As a wild guess, I'd go for the option
that Oracle is driving through the table
PS_TM_PEFF_TWTCAL B - and
therefore has to do some joins before
it can filter on whatever tables those
filters apply to - but it would be a good
idea to post the output from dbms_xplan()
and view definition.


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

> As a wild guess, I'd go for the option
> that Oracle is driving through the table
> PS_TM_PEFF_TWTCAL B - and
> therefore has to do some joins before
> it can filter on whatever tables those
> filters apply to - but it would be a good
> idea to post the output from dbms_xplan()
> and view definition.
>
>
> --
> Regards
>
> Jonathan Lewis

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.

It appears that we are not driving from PS_TM_PEFF_TWTCAL in the second
query as you suspected.


select count(*) from PS_TM_PEFF_V_SHDAY
where BUSINESS_UNIT = 'TMUK'
and tm_shop_code = 'AA1'
and tm_date = to_date('2006-07-28','YYYY-MM-DD')

call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.04 0.03 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 2 0.48 0.49 0 25225 0
1
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 4 0.53 0.52 0 25225 0
1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 34

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)
50 HASH JOIN (cr=25122 pr=0 pw=0 time=487165 us)
50 HASH JOIN (cr=25119 pr=0 pw=0 time=485286 us)
50 HASH JOIN (cr=407 pr=0 pw=0 time=9918 us)
50 HASH JOIN (cr=405 pr=0 pw=0 time=9585 us)
71 HASH JOIN (cr=26 pr=0 pw=0 time=1989 us)
1 NESTED LOOPS OUTER (cr=3 pr=0 pw=0 time=113 us)
1 INDEX UNIQUE SCAN PS_TM_PEFF_SHPMSTR (cr=1 pr=0
pw=0 time=27 us)(object id 18039)
1 TABLE ACCESS BY INDEX ROWID PS_TM_PEFF_COEF (cr=2
pr=0 pw=0 time=61 us)
1 INDEX RANGE SCAN PS_TM_PEFF_COEF (cr=1 pr=0 pw=0
time=30 us)(object id 18031)
71 TABLE ACCESS FULL PS_TM_PEFF_CONT_DT (cr=23 pr=0
pw=0 time=407 us)
297 TABLE ACCESS FULL PS_TM_PEFF_TWTCAL (cr=379 pr=0 pw=0
time=9354 us)
1 TABLE ACCESS BY INDEX ROWID PS_TM_PEFF_TCJR (cr=2 pr=0
pw=0 time=98 us)
2 INDEX RANGE SCAN PS_TM_PEFF_TCJR (cr=1 pr=0 pw=0
time=28 us)(object id 18041)
297 VIEW PS_TM_PEFF_V_CCEH (cr=24712 pr=0 pw=0 time=473494
us)
297 HASH GROUP BY (cr=24712 pr=0 pw=0 time=471997 us)
7555 NESTED LOOPS OUTER (cr=24712 pr=0 pw=0 time=552006
us)
7555 TABLE ACCESS FULL PS_TM_PEFF_GPQCAL (cr=1633 pr=0
pw=0 time=106241 us)
7021 VIEW (cr=23079 pr=0 pw=0 time=391079 us)
7021 TABLE ACCESS BY INDEX ROWID PS_TM_PEFF_BNCHMRK
(cr=23079 pr=0 pw=0 time=270097 us)
8919 INDEX RANGE SCAN PSATM_PEFF_BNCHMRK (cr=15169 pr=0
pw=0 time=123954 us)(object id 18029)
1 TABLE ACCESS BY INDEX ROWID PS_TM_PEFF_MFCAL (cr=3 pr=0
pw=0 time=45 us)
1 INDEX UNIQUE SCAN PS_TM_PEFF_MFCAL (cr=2 pr=0 pw=0
time=25 us)(object id 18037)
1 SORT AGGREGATE (cr=2 pr=0 pw=0 time=78 us)
1 TABLE ACCESS BY INDEX ROWID PS_TM_PEFF_TCJR (cr=2 pr=0
pw=0 time=49 us)
2 INDEX RANGE SCAN PS_TM_PEFF_TCJR (cr=1 pr=0 pw=0
time=25 us)(object id 18041)
1 SORT AGGREGATE (cr=1 pr=0 pw=0 time=51 us)
1 FIRST ROW (cr=1 pr=0 pw=0 time=28 us)
1 INDEX RANGE SCAN (MIN/MAX) PS_TM_PEFF_COEF (cr=1 pr=0
pw=0 time=15 us)(object id 18031)
28 HASH JOIN (cr=100 pr=0 pw=0 time=4735 us)
207 TABLE ACCESS BY INDEX ROWID PS_TM_PEFF_TWTCAL (cr=77 pr=0
pw=0 time=6223 us)
565 NESTED LOOPS (cr=12 pr=0 pw=0 time=22604 us)
1 NESTED LOOPS (cr=5 pr=0 pw=0 time=118 us)
1 TABLE ACCESS BY INDEX ROWID PS_TM_PEFF_MFCAL (cr=3
pr=0 pw=0 time=36 us)
1 INDEX UNIQUE SCAN PS_TM_PEFF_MFCAL (cr=2 pr=0 pw=0
time=19 us)(object id 18037)
1 TABLE ACCESS BY INDEX ROWID PS_TM_PEFF_TCJR (cr=2 pr=0
pw=0 time=57 us)
2 INDEX RANGE SCAN PS_TM_PEFF_TCJR (cr=1 pr=0 pw=0
time=16 us)(object id 18041)
563 INDEX RANGE SCAN PS_TM_PEFF_TWTCAL (cr=7 pr=0 pw=0
time=2364 us)(object id 18045)
1 SORT AGGREGATE (cr=2 pr=0 pw=0 time=67 us)
1 TABLE ACCESS BY INDEX ROWID PS_TM_PEFF_TCJR (cr=2
pr=0 pw=0 time=42 us)
2 INDEX RANGE SCAN PS_TM_PEFF_TCJR (cr=1 pr=0 pw=0
time=17 us)(object id 18041)
71 TABLE ACCESS FULL PS_TM_PEFF_CONT_DT (cr=23 pr=0 pw=0
time=336 us)

************************************************** ******************************

select count(*) from PS_TM_PEFF_V_SHDAY A, PS_TM_PEFF_TWTCAL B
where A.BUSINESS_UNIT = 'TMUK'
and A.tm_shop_code = 'AA1'
and A.tm_date = to_date('2006-07-28','YYYY-MM-DD')
and A.BUSINESS_UNIT = B.BUSINESS_UNIT

call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.03 0.04 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 2 12.32 12.44 97 532550 0
1
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 4 12.35 12.48 97 532550 0
1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 34

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=532550 pr=97 pw=0 time=12441458 us)
878280 HASH JOIN (cr=532550 pr=97 pw=0 time=18300707 us)
78 VIEW PS_TM_PEFF_V_SHDAY (cr=532388 pr=0 pw=0 time=12050906
us)
78 SORT UNIQUE (cr=532388 pr=0 pw=0 time=12050431 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)
50 HASH JOIN (cr=532285 pr=0 pw=0 time=12072912 us)
1 TABLE ACCESS BY INDEX ROWID PS_TM_PEFF_MFCAL (cr=3 pr=0
pw=0 time=58 us)
1 INDEX UNIQUE SCAN PS_TM_PEFF_MFCAL (cr=2 pr=0 pw=0
time=30 us)(object id 18037)
50 HASH JOIN (cr=532282 pr=0 pw=0 time=12010387 us)
50 HASH JOIN (cr=407 pr=0 pw=0 time=10350 us)
50 HASH JOIN (cr=405 pr=0 pw=0 time=9831 us)
71 HASH JOIN (cr=26 pr=0 pw=0 time=2209 us)
1 NESTED LOOPS OUTER (cr=3 pr=0 pw=0 time=109 us)
1 INDEX UNIQUE SCAN PS_TM_PEFF_SHPMSTR (cr=1 pr=0
pw=0 time=24 us)(object id 18039)
1 TABLE ACCESS BY INDEX ROWID PS_TM_PEFF_COEF (cr=2
pr=0 pw=0 time=60 us)
1 INDEX RANGE SCAN PS_TM_PEFF_COEF (cr=1 pr=0 pw=0
time=30 us)(object id 18031)
71 TABLE ACCESS FULL PS_TM_PEFF_CONT_DT (cr=23 pr=0
pw=0 time=410 us)
297 TABLE ACCESS FULL PS_TM_PEFF_TWTCAL (cr=379 pr=0
pw=0 time=9348 us)
1 TABLE ACCESS BY INDEX ROWID PS_TM_PEFF_TCJR (cr=2
pr=0 pw=0 time=97 us)
2 INDEX RANGE SCAN PS_TM_PEFF_TCJR (cr=1 pr=0 pw=0
time=27 us)(object id 18041)
297 VIEW PS_TM_PEFF_V_CCEH (cr=531875 pr=0 pw=0
time=12003688 us)
29560 HASH GROUP BY (cr=531875 pr=0 pw=0 time=12114210 us)
180056 NESTED LOOPS OUTER (cr=531875 pr=0 pw=0
time=11523675 us)
180035 TABLE ACCESS FULL PS_TM_PEFF_GPQCAL (cr=1633 pr=0
pw=0 time=720193 us)
139751 VIEW (cr=530242 pr=0 pw=0 time=10314447 us)
139751 TABLE ACCESS BY INDEX ROWID PS_TM_PEFF_BNCHMRK
(cr=530242 pr=0 pw=0 time=7085955 us)
180430 INDEX RANGE SCAN PSATM_PEFF_BNCHMRK (cr=361366
pr=0 pw=0 time=3409995 us)(object id 18029)
1 SORT AGGREGATE (cr=2 pr=0 pw=0 time=98 us)
1 TABLE ACCESS BY INDEX ROWID PS_TM_PEFF_TCJR (cr=2 pr=0
pw=0 time=51 us)
2 INDEX RANGE SCAN PS_TM_PEFF_TCJR (cr=1 pr=0 pw=0
time=19 us)(object id 18041)
1 SORT AGGREGATE (cr=1 pr=0 pw=0 time=110 us)
1 FIRST ROW (cr=1 pr=0 pw=0 time=31 us)
1 INDEX RANGE SCAN (MIN/MAX) PS_TM_PEFF_COEF (cr=1 pr=0
pw=0 time=16 us)(object id 18031)
28 HASH JOIN (cr=100 pr=0 pw=0 time=6539 us)
207 TABLE ACCESS BY INDEX ROWID PS_TM_PEFF_TWTCAL (cr=77
pr=0 pw=0 time=6477 us)
565 NESTED LOOPS (cr=12 pr=0 pw=0 time=27120 us)
1 NESTED LOOPS (cr=5 pr=0 pw=0 time=158 us)
1 TABLE ACCESS BY INDEX ROWID PS_TM_PEFF_MFCAL (cr=3
pr=0 pw=0 time=56 us)
1 INDEX UNIQUE SCAN PS_TM_PEFF_MFCAL (cr=2 pr=0 pw=0
time=31 us)(object id 18037)
1 TABLE ACCESS BY INDEX ROWID PS_TM_PEFF_TCJR (cr=2
pr=0 pw=0 time=76 us)
2 INDEX RANGE SCAN PS_TM_PEFF_TCJR (cr=1 pr=0 pw=0
time=24 us)(object id 18041)
563 INDEX RANGE SCAN PS_TM_PEFF_TWTCAL (cr=7 pr=0 pw=0
time=2369 us)(object id 18045)
1 SORT AGGREGATE (cr=2 pr=0 pw=0 time=67 us)
1 TABLE ACCESS BY INDEX ROWID PS_TM_PEFF_TCJR (cr=2
pr=0 pw=0 time=38 us)
2 INDEX RANGE SCAN PS_TM_PEFF_TCJR (cr=1 pr=0 pw=0
time=17 us)(object id 18041)
71 TABLE ACCESS FULL PS_TM_PEFF_CONT_DT (cr=23 pr=0 pw=0
time=348 us)
11260 INDEX FAST FULL SCAN PS_TM_PEFF_TWTCAL (cr=162 pr=97 pw=0
time=135775 us)(object id 18045)

View definitions:

I've trimmed out the very long select list to help readability....

PS_TM_PEFF_V_SHDAY:

SELECT /*+ ORDERED USE_HASH(D) PUSH_PRED(D) */ .....
FROM PS_TM_PEFF_V_COEF E
, PS_TM_PEFF_CONT_DT C
, PS_TM_PEFF_TWTCAL A
, PS_TM_PEFF_TCJR B
, PS_TM_PEFF_V_CCEH D
, PS_TM_PEFF_MFCAL F
WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT
AND d.business_unit = f.business_unit
AND d.tm_date = f.tm_date
AND A.CMS_LABOR_TYPE = 'A'
AND A.CMS_LABOR_TYPE = B.TM_LABOR_CLASS
AND B.TM_EFFECT_DT = (
SELECT MAX(TM_EFFECT_DT)
FROM PS_TM_PEFF_TCJR B1
WHERE B1.BUSINESS_UNIT = B.BUSINESS_UNIT
AND B1.TM_LABOR_CLASS = B.TM_LABOR_CLASS
AND B1.TM_EFFECT_DT <= A.TWT_DATE)
AND C.BUSINESS_UNIT = A.BUSINESS_UNIT
AND C.REP_COST_CENTER_CD = A.REP_COST_CENTER_CD
AND (C.TM_EFF_START_DATE <= A.TWT_DATE
AND (C.TM_EFF_STOP_DATE >= A.TWT_DATE
OR C.TM_EFF_STOP_DATE IS NULL))
AND D.BUSINESS_UNIT = A.BUSINESS_UNIT
AND D.REP_COST_CENTER_CD = A.REP_COST_CENTER_CD
AND D.TM_DATE = A.TWT_DATE
AND D.DEPTID = A.COST_CENTER_CD
AND D.CMS_LABOR_TYPE = A.CMS_LABOR_TYPE
AND E.BUSINESS_UNIT = D.BUSINESS_UNIT
AND E.TM_SHOP_CODE = C.TM_SHOP_CODE
AND (E.TM_EFF_START_DATE = (
SELECT MAX(E1.TM_EFF_START_DATE)
FROM PS_TM_PEFF_COEF E1
WHERE E.BUSINESS_UNIT = E1.BUSINESS_UNIT
AND E.TM_SHOP_CODE = E1.TM_SHOP_CODE
AND E1.TM_EFF_START_DATE <= D.TM_DATE)
OR E.TM_EFF_START_DATE IS NULL)
UNION
SELECT .......
FROM PS_TM_PEFF_TWTCAL A
, PS_TM_PEFF_MFCAL D
, PS_TM_PEFF_CONT_DT B
, PS_TM_PEFF_TCJR C
WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT
AND A.BUSINESS_UNIT = D.BUSINESS_UNIT
AND A.TWT_DATE = D.TM_DATE
AND A.REP_COST_CENTER_CD = B.REP_COST_CENTER_CD
AND (B.TM_EFF_START_DATE <= A.TWT_DATE
AND (B.TM_EFF_STOP_DATE >= A.TWT_DATE
OR B.TM_EFF_STOP_DATE IS NULL))
AND A.CMS_LABOR_TYPE NOT IN ('B2','A')
AND A.TWT_TYPE = 'DA'
AND C.BUSINESS_UNIT = A.BUSINESS_UNIT
AND C.TM_LABOR_CLASS = 'B'
AND C.TM_EFFECT_DT = (
SELECT MAX(TM_EFFECT_DT)
FROM PS_TM_PEFF_TCJR C1
WHERE C1.BUSINESS_UNIT = C.BUSINESS_UNIT
AND C1.TM_LABOR_CLASS = C.TM_LABOR_CLASS
AND C1.TM_EFFECT_DT <= A.TWT_DATE);

PS_TM_PEFF_V_CCEH:

SELECT /*+ NO_MERGE */ ......
FROM PS_TM_PEFF_BNCHMRK A RIGHT OUTER JOIN PS_TM_PEFF_GPQCAL B ON
A.BUSINESS_UNIT = B.BUSINESS_UNIT
AND A.REP_COST_CENTER_CD = B.REP_COST_CENTER_CD
AND A.SRG_CODE = B.SRG_CODE
AND A.PART_NUM = B.PART_NUM
AND A.PART_SFX_CD = B.PART_SFX_CD
AND A.PROCESS_CODE = B.PROCESS_CODE
AND ( (B.TM_DATE >= A.TM_EFFECT_DT
AND (B.TM_DATE <= A.EFF_STOP_DATE
OR A.EFF_STOP_DATE IS NULL) )
OR A.TM_EFFECT_DT IS NULL )
GROUP BY B.BUSINESS_UNIT ,B.REP_COST_CENTER_CD ,B.DEPTID
,B.TM_DATE ,B.CMS_LABOR_TYPE ,B.CMS_SHIFT;

PS_TM_PEFF_V_COEF:

SELECT .....
FROM
ps_tm_peff_shpmstr a,
ps_tm_peff_coef b
WHERE a.business_unit = b.business_unit (+)
AND a.tm_shop_code = b.tm_shop_code (+);

<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

> General clues:
>
> 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.
>
>

Interesting point. Because when I run the exact same SQL against the
same data set in 9.2.0.4, the filters are pushed OK. And the plan
shows UNION ALL in 9.2.0.4 where it shows UNION ALL PARTITION in
10.2.0.2.

So it may be a bug in the 10g version...!

Matt

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

Jonathan,

I appreciate you taking a look at this, thanks.

I think you've hit the nail on the head.... When you mentioned
transitive closure you got me thinking.

I first tried running the query with a join condition which differed
from the filter condition. This pushed the filters down into the
table.

So I now have a workaround which is to add a ||'' (i.e. a null string)
to the end of each joined column.

This fixes the issue (as a workaround).

I'm still waiting for Oracle support to tell me whether this is a bug
or a 'feature'.

Thanks very much for your time.

Matt

Oracle support have submitted my TAR to development as a potential bug.

Workaround is to set the following:

alter session set "_push_join_union_view" = false;

Matt