in-list expansion也被称作or expansion
--针对in后面是常量集合的另外一种处理方法。优化器会把目标sql中in后面的常量集合拆开，把里面的每个常量都提出来形成一个分支，各分支之间用union all来连接。即in-list expansion本质是把带in的目标sql等价写成union all连接的各个分支。
in-list expansion改写成union all连接分支后，各个分支可以各自走索引、分区裁剪、表连接等相关的执行计划而不互相干扰；缺点是，改写后，随着union all分支的递加，sql解析时间对增多。
#先禁掉in-list iterator特性
SQL> alter session set events '10142 trace name context forever';
SQL> alter session set events '10157 trace name context forever';
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP1',cascade=>true,no_invalidate=>false);
SQL> select /*+ use_concat +*/ * from emp1 where deptno in (10,20,30);
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID 2nk52krswqkxk, child number 0
-------------------------------------
select /*+ use_concat +*/ * from emp1 where deptno in (10,20,30)
Plan hash value: 1141053746
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP1 | 3 | 114 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_EMP1_DEPT | 3 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP1 | 5 | 190 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_EMP1_DEPT | 5 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| EMP1 | 6 | 228 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IDX_EMP1_DEPT | 6 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1_1 / EMP1@SEL$1
3 - SEL$1_1 / EMP1@SEL$1
4 - SEL$1_2 / EMP1@SEL$1_2
5 - SEL$1_2 / EMP1@SEL$1_2
6 - SEL$1_3 / EMP1@SEL$1_3
7 - SEL$1_3 / EMP1@SEL$1_3
......
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPTNO"=10)
5 - access("DEPTNO"=20)
7 - access("DEPTNO"=30)
in-list expansion后，上面的语句其实被改写成下面的格式了：
select * from emp1 where deptno=10 union all select * from emp1 where deptno=20 union all select * from emp1 where deptno=30 union all;
要想优化器不走in-list expansion，需要使用no_expand hint
SQL> select /*+ no_expand +*/ * from emp1 where deptno in (10,20,30); SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced')); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------- SQL_ID akwm5wds8km03, child number 0 ------------------------------------- select /*+ no_expand +*/ * from emp1 where deptno in (10,20,30) Plan hash value: 2226897347 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | |* 1 | TABLE ACCESS FULL| EMP1 | 10 | 380 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------
对于in后面常量集合中所含元素数量非常多的情形(或or条件比较多的sql，OR关联词在CBO解析时，会变形(EXPAND)为UNION ALL的关联语句)，可以考虑以下的优化方案：
1.使用no_expand hint不让cbo走in-list expansion类型的执行计划
2.将in后面的常量集合存储在一个中间表里，并将原始目标sql中的in改写成和这个表中间表做表连接
可以参考别人的优化示例：http://blog.itpub.net/26687597/viewspace-1207432/