Mysterious FILTER operation ;)
- From: J.Velikanovs@xxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 3 Jan 2005 20:19:16 +0200
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
Other related posts:
