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> -- //www.freelists.org/webpage/oracle-l