关于IN-LIST迭代
IN-list iterator (IN-list迭代)
字段有索引，in里有多少个值就执行多少次索引扫描。不管值是否在字段里存在~
SQL> create table t1 as select * from dba_objects;
表已创建。
SQL> create index idx_object_id on t1(object_id);
索引已创建。
SQL> select count(1) from t1 where object_id=1;
COUNT(1)
----------
0
SQL> select object_name,object_id from t1 where object_id in (1,2,3,4,5);
OBJECT_NAME OBJECT_ID
------------------------------ ----------
I_OBJ# 3
CLU$ 5
C_OBJ# 2
TAB$ 4
分析表:
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'HR',
tabname => 't1',
estimate_percent => 30,
method_opt => 'for all columns size repeat',
no_invalidate => FALSE,
degree => 8,
cascade => TRUE);
END;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------
SQL_ID 97md06hm8ktgp, child number 1
-------------------------------------
select object_name,object_id from t1 where object_id in (1,2,3,4,5)
Plan hash value: 2643144985
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 |00:00:00.01 | 11 |
| 1 | INLIST ITERATOR | | 1 | | 4 |00:00:00.01 | 11 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 5 | 5 | 4 |00:00:00.01 | 11 |
|* 3 | INDEX RANGE SCAN | IDX_OBJECT_ID | 5 | 5 | 4 |00:00:00.01 | 9 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(("OBJECT_ID"=1 OR "OBJECT_ID"=2 OR "OBJECT_ID"=3 OR "OBJECT_ID"=4 OR
"OBJECT_ID"=5))
21 rows selected.
相当于
SQL> select object_name, object_id
from t1
where (("OBJECT_ID" = 1 OR "OBJECT_ID" = 2 OR "OBJECT_ID" = 3 OR
"OBJECT_ID" = 4 OR "OBJECT_ID" = 5)) 2 3 4 ;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gpsm02nfqx6qz, child number 0
-------------------------------------
select object_name, object_id from t1 where (("OBJECT_ID" = 1 OR
"OBJECT_ID" = 2 OR "OBJECT_ID" = 3 OR "OBJECT_ID" = 4 OR
"OBJECT_ID" = 5))
Plan hash value: 2643144985
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 |00:00:00.01 | 11 |
| 1 | INLIST ITERATOR | | 1 | | 4 |00:00:00.01 | 11 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 5 | 5 | 4 |00:00:00.01 | 11 |
|* 3 | INDEX RANGE SCAN | IDX_OBJECT_ID | 5 | 5 | 4 |00:00:00.01 | 9 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(("OBJECT_ID"=1 OR "OBJECT_ID"=2 OR "OBJECT_ID"=3 OR "OBJECT_ID"=4 OR
"OBJECT_ID"=5))
23 rows selected.
