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