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=10union allselect * from emp1 where deptno=20union allselect * from emp1 where deptno=30union 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改写成和这个表中间表做表连接可以参考别人的优化示例：