Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Mysterious FILTER operation ;)

Mysterious FILTER operation ;)

From: <J.Velikanovs_at_alise.lv>
Date: Mon, 3 Jan 2005 20:19:16 +0200
Message-ID: <OF0B3A5158.ACEA8075-ONC2256F7E.00646D79-C2256F7E.0064F892@alise.lv> 





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






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




...






| 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





























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


Received on Mon Jan 03 2005 - 12:22:08 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US