18.Oracle查询转换之-IN-LIST ITERATOR

1.什么是IN-LIST ITERATOR

优化器在处理带IN 的目前SQL时，可能也会用到查询转换，也就是 IN-LIST EXPANSION 
(又称为:OR_EXPANSION).
在oracle数据库里，IN 和OR 是等价的，优化器在处理带IN 的目标SQL时实际上会将
其转换为带OR 的等价改写SQL.

2.带IN的SQL语句示例。

--造数。
create table test02 (id int,addr varchar(100));
begin 
for i in 1..1000000 loop 
insert into test02 values(i,i||case when mod(i,3)=0 then '北京' when mod(i,4)=0 then '上海' when 
mod(i,5)=0 then '天津' else 'xxx' end);
if mod(i,1000)=0 then 
commit; 
end if; 
end loop; 
end; 

--函数 LNNVL
lnnvl(deptno in (10))= deptno is null + deptno<>10;

SQL> select * from test01 where id in(1,2);

	ID NAME 				 AGE
---------- ------------------------------ ----------
	 1 1xsq1				  19
	 2 2xsq2				  19
优化器处理带IN的SQL时,通常会采用如下四种方法:
1)使用 IN-LIST Iterator 
2)使用 IN-LIST Expansion 
3)使用 IN-LIST 
4)对IN做子查询展开，或者既做子查询展开又做视图合并。

3.INLIST ITERATRO注意点

(1)IN-LIST Iterator 
IN-LIST ITERATOR是针对IN 后面是常量集合的一种处理方法。此时优化器会遍历目前SQL 
中IN后面的常量集合中的每一个值，然后去做比较，看目标结果集中是否存在和这个值
匹配的记录。如果存在匹配的记录，则这个记录就会成为该SQL的最终返回结果集中
的一员；如果不存在匹配记录，则优化器会继续遍历IN后面的常量集合中下一个值，
直到该常量集合遍历完毕。

关于IN-LIST ITERATOR，有以下几点需要注意。
IN-LIST ITERATOR是Oracle针对目标SQL的IN 后面是常量集合的首选处理方法，它的处理效率
通常都会比IN-LIST-EXPANSION 高。
Oracle能用IN-LIST ITERATOR来处理IN 的前提条件是IN 所在列上一定要有索引。
不能强制让Oracle走IN-LIST ITERATOR类型的执行计划，Oracle也没有相关的强制走
IN-LIST ITERATOR的HINT,但可以通过联合设置10142 和 10157 事件来禁用IN-LIST ITERATOR;

SQL> select * from test01 where id in(1,2,3);

	ID NAME 				 AGE
---------- ------------------------------ ----------
	 1 1xsq1				  19
	 2 2xsq2				  19
	 3 3xsq3				  19

--执行计划中有IN-LIST ITERATOR;说明已经走了这个执行计划。
Execution Plan
----------------------------------------------------------
Plan hash value: 1470655483

---------------------------------------------------------------------------------------
| Id  | Operation		     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |	      |     3 |    72 |     6	(0)| 00:00:01 |
|   1 |  INLIST ITERATOR	     |	      |       |       | 	   |	      |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST01 |     3 |    72 |     6	(0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN	     | IDX_ID |     3 |       |     5	(0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   3 - access("ID"=1 OR "ID"=2 OR "ID"=3)

Note
-----
   - SQL plan baseline "SQL_PLAN_0ccnykasya4d77c9d57be" used for this statement


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


--OR 语句也走了IN-LIST ITERATOR的执行计划。
SQL> select * from test01 where id=1 or id=2 or id=3;

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1470655483

---------------------------------------------------------------------------------------
| Id  | Operation		     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |	      |     3 |    72 |     6	(0)| 00:00:01 |
|   1 |  INLIST ITERATOR	     |	      |       |       | 	   |	      |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST01 |     3 |    72 |     6	(0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN	     | IDX_ID |     3 |       |     5	(0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   3 - access("ID"=1 OR "ID"=2 OR "ID"=3)


Statistics
----------------------------------------------------------
	  6  recursive calls
	  4  db block gets
	 12  consistent gets
	  0  physical reads
	576  redo size
	748  bytes sent via SQL*Net to client
	520  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  3  rows processed

4.总结

说明IN-LIST ITERATOR类型的执行计划不仅适用于带IN的目标SQL,也能用于带OR的目标SQL，
因为他们是等价的。并且前提条件是 IN 所在的列上一定要有索引。。