关于谓词中or写法in写法以及union all改写地测试

在or或者in相关字段上创建索引，可以避免全表扫描，优化执行计划，Oracle使用inlist iterator提高了执行效率，下面是测试过程和执行计划展示。

1 in写法
select * from emp where deptno in (10,20,30);

Execution Plan
----------------------------------------------------------
Plan hash value: 2277710101

--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 532 | 2 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 14 | 532 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_EMP_DEPTNOO | 14 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

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

3 - access("DEPTNO"=10 OR "DEPTNO"=20 OR "DEPTNO"=30)

2 or写法
改写为or操作，执行计划一样。
SQL> select * from emp where deptno =10 or deptno=20 or deptno=30;

Execution Plan
----------------------------------------------------------
Plan hash value: 2277710101

--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 532 | 2 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 14 | 532 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_EMP_DEPTNOO | 14 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

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

3 - access("DEPTNO"=10 OR "DEPTNO"=20 OR "DEPTNO"=30)


3 union all写法
select * from emp where deptno =10 union all select * from emp where deptno =20 union all select * from emp where deptno =30;

SQL> select * from emp where deptno =10 union all select * from emp where deptno =20 union all select * from emp where deptno =30;

Execution Plan
----------------------------------------------------------
Plan hash value: 737767745

--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 570 | 6 (0)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 5 | 190 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_EMP_DEPTNOO | 5 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 5 | 190 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_EMP_DEPTNOO | 5 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 5 | 190 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IDX_EMP_DEPTNOO | 5 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

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

3 - access("DEPTNO"=10)
5 - access("DEPTNO"=20)
7 - access("DEPTNO"=30)


从cost计算结果和逻辑读看，使用in或者or操作的开销是一致的。而使用union all操作无论从逻辑读和cost值都不是好的选择，这里关键是
使用in或者or时oracle使用了 INLIST ITERATOR，对于每一个谓词选择使用索引找数据，而使用inlist iterator 函数来处理读取的回表数据，从而
拿到后的用户数据。