Mysterious FILTER operation ;)

Win2000 9.2.0.6 (tested on 9.2.0.4/SPARC Solaris as well)
FULL TEST text see at the end of letter.
I just trying to understand what FILTER operation doing in case described 
below.
I have ran the same SQL two times (with and without stats, CBO/RBO)

There is index range scan caused by ?where n between :v_p1 and :v_p2;? 
predicate usage.
Only difference between two runs is additional FILTER operation added by 
CBO.

What mysterious for me is why operation added by CBO reduce LIO as well as 
execution time.

R1 (CBO)
Elapsed: 00:00:00.03
       1193  consistent gets
          0  physical reads

R2 (RBO)
Elapsed: 00:00:00.05
       2270  consistent gets
          0  physical reads


It?s seams to me (before this day ;), I understood how clasic RS works, 
but I can?t understood what 
|*  2 |   FILTER                      |             |       |       |  |
   2 - filter(TO_NUMBER(:Z)<=TO_NUMBER(:Z))
operation are doing in this case.

Thanks in advance,
Jurijs

Cuted testcase for better reader understanding.
---- BEGIN preparation step
create table  testrs (n number, v varchar2(1000)) tablespace users;
begin for f in 1..500000 loop
INSERT INTO testrs values (f, f);
end loop; end;
/
create index testrs_i1 on testrs (n) nologging;
var v_p1 number; 
var v_p2 number;
begin :v_p1:=1; :v_p2:=500000; end;
/
---- END preparation step

---- FIRST RUN
exec 
stats.gather_table_stats(ownname=>'DROPME',tabname=>'TESTRS',cascade=>TRUE);

explain plan for select count(v) from testrs where n between :v_p1 and 
:v_p2;
select * from table(dbms_xplan.display);

-----------------------------------------------------------------------------
| Id  | Operation                     |  Name       | Rows  | Bytes | Cost 
 |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     1 |    11 | 14 |
|   1 |  SORT AGGREGATE               |             |     1 |    11 |  |
vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
|*  2 |   FILTER                      |             |       |       |  |
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|   3 |    TABLE ACCESS BY INDEX ROWID| TESTRS      |  1250 | 13750 | 14 |
|*  4 |     INDEX RANGE SCAN          | TESTRS_I1   |  2250 |       | 8 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
   2 - filter(TO_NUMBER(:Z)<=TO_NUMBER(:Z))
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
   4 - access("TESTRS"."N">=TO_NUMBER(:Z) AND "TESTRS"."N"<=TO_NUMBER(:Z))


set timing on autotrace on stat
select count(v) from testrs where n between :v_p1 and :v_p2;

  COUNT(V)
----------
    500000

Elapsed: 00:00:00.03

Statistics
----------------------------------------------------------
...
       1193  consistent gets
          0  physical reads
...


---- SECOND RUN
exec dbms_stats.delete_table_stats(ownname=>'DROPME', tabname=>'TESTRS');
explain plan for select count(v) from testrs where n between :v_p1 and 
:v_p2;
select * from table(dbms_xplan.display);

----------------------------------------------------------------------------
| Id  | Operation                    |  Name       | Rows  | Bytes | Cost 
|
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |       |       | |
|   1 |  SORT AGGREGATE              |             |       |       | |
|   2 |   TABLE ACCESS BY INDEX ROWID| TESTRS      |       |       | |
|*  3 |    INDEX RANGE SCAN          | TESTRS_I1   |       |       | |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("TESTRS"."N">=TO_NUMBER(:Z) AND "TESTRS"."N"<=TO_NUMBER(:Z))


set timing on autotrace on stat
select count(v) from testrs where n between :v_p1 and :v_p2;

  COUNT(V)
----------
    500000

Elapsed: 00:00:00.05

Statistics
----------------------------------------------------------
...
       2270  consistent gets
          0  physical reads
...


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

Best regards,
Jurijs
























------ FULL TEST TEXT --------
DROPME:jozh> drop table testrs;
Table dropped.

DROPME:jozh> create table  testrs (n number, v varchar2(1000)) tablespace 
users;
Table created.

DROPME:jozh> begin for f in 1..500000 loop
DROPME:jozh> INSERT INTO testrs values (f, f);
DROPME:jozh> end loop; end;
DROPME:jozh> /
PL/SQL procedure successfully completed.

DROPME:jozh> alter session set sort_area_size=100000000;
Session altered.

DROPME:jozh> create index testrs_i1 on testrs (n) nologging;
Index created.

DROPME:jozh> exec 
dbms_stats.gather_table_stats(ownname=>'DROPME',tabname=>'TESTRS',cascade=>TRUE);
PL/SQL procedure successfully completed.

DROPME:jozh> var v_p1 number;
DROPME:jozh> var v_p2 number;
DROPME:jozh> begin
DROPME:jozh> :v_p1:=1;
DROPME:jozh> :v_p2:=500000;
DROPME:jozh> end;
DROPME:jozh> /
PL/SQL procedure successfully completed.

DROPME:jozh> explain plan for select count(v) from testrs where n between 
:v_p1 and :v_p2;
Explained.

DROPME:jozh> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------
| Id  | Operation                     |  Name       | Rows  | Bytes | Cost 
 |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     1 |    11 | 14 |
|   1 |  SORT AGGREGATE               |             |     1 |    11 |  |
|*  2 |   FILTER                      |             |       |       |  |
|   3 |    TABLE ACCESS BY INDEX ROWID| TESTRS      |  1250 | 13750 | 14 |
|*  4 |     INDEX RANGE SCAN          | TESTRS_I1   |  2250 |       | 8 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TO_NUMBER(:Z)<=TO_NUMBER(:Z))
   4 - access("TESTRS"."N">=TO_NUMBER(:Z) AND "TESTRS"."N"<=TO_NUMBER(:Z))

Note: cpu costing is off

18 rows selected.

DROPME:jozh> 
DROPME:jozh> set timing on autotrace on stat
DROPME:jozh> select count(v) from testrs where n between :v_p1 and :v_p2;

  COUNT(V)
----------
    500000

Elapsed: 00:00:00.03

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1193  consistent gets
          0  physical reads
          0  redo size
        379  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


DROPME:jozh> set autotrace off timing off
DROPME:jozh> exec dbms_stats.delete_table_stats(ownname=>'DROPME', 
tabname=>'TESTRS');
PL/SQL procedure successfully completed.

DROPME:jozh> explain plan for select count(v) from testrs where n between 
:v_p1 and :v_p2;

Explained.

DROPME:jozh> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------
| Id  | Operation                    |  Name       | Rows  | Bytes | Cost 
|
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |       |       | |
|   1 |  SORT AGGREGATE              |             |       |       | |
|   2 |   TABLE ACCESS BY INDEX ROWID| TESTRS      |       |       | |
|*  3 |    INDEX RANGE SCAN          | TESTRS_I1   |       |       | |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("TESTRS"."N">=TO_NUMBER(:Z) AND "TESTRS"."N"<=TO_NUMBER(:Z))

Note: rule based optimization

16 rows selected.




DROPME:jozh> set timing on autotrace on stat
DROPME:jozh> select count(v) from testrs where n between :v_p1 and :v_p2;

  COUNT(V)
----------
    500000

Elapsed: 00:00:00.05

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2270  consistent gets
          0  physical reads
          0  redo size
        379  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

DROPME:jozh>

--
http://www.freelists.org/webpage/oracle-l

Other related posts: