| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Optimizer not pushing filters down into view
> 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)
 |
 |