RE: Mysterious FILTER operation ;)

Hi Jurijs

>What the difference?
>What FILTER operation doing in this case?

I tested your statement on 10.1.0.3.

In SQL*Plus with EXPLAIN PLAN I have the same output as you. But, when I =
traced the execution with SQL trace the execution plan with the CBO is =
different...

Execution plan with CBO:

1       SORT AGGREGATE (cr=3D1193 pr=3D240 pw=3D0 time=3D1100912 us)
500,000   FILTER (cr=3D1193 pr=3D240 pw=3D0 time=3D9000191 us)
500,000     TABLE ACCESS FULL OBJ#(6453024) (cr=3D1193 pr=3D240 pw=3D0 =
time=3D1500197 us)

Execution plan with RBO:

1       SORT AGGREGATE (cr=3D2270 pr=3D1107 pw=3D0 time=3D4674225 us)
500,000   TABLE ACCESS BY INDEX ROWID OBJ#(6453024) (cr=3D2270 pr=3D1107 =
pw=3D0 time=3D17001609 us)
500,000     INDEX RANGE SCAN OBJ#(6453025) (cr=3D1115 pr=3D1107 pw=3D0 =
time=3D5499997 us) (object id 6453025)


It seams that EXPLAIN PLAN is lying to us!

HTH
Chris

PS: I know, I should not use the RBO with 10g ;-)
--
http://www.freelists.org/webpage/oracle-l

Other related posts: