in-list expansion

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/