最近看到一些IN语句的优化案例，有些环节不是很理解，重读一下dbsnake的书中关于IN处理方式的介绍，以下内容算是学习笔记。
Oracle优化器在处理带IN的目标SQL时，通常会采用这四种方式，
1. 使用IN-List Iterator。
2. 使用IN-List Expansion。
3. 使用IN-List Filter。
4. 对IN做子查询展开/视图合并。
我们通过实验，逐一认识。时髦一下，我们选择Oracle 19c，作为测试环境，些许环节可能和书中11g的操作现象略有出入，SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
1. 使用IN-List Iterator
IN-List Iterator是针对IN后面是常量集合的一种处理方法。简单来讲，优化器会遍历目标SQL中IN后面的常量集合中的每一个值，然后进行比较，以此确定目标结果集中是否存在和这个值匹配的记录。存在，则该记录成为SQL返回结果集的一员，不存在，则继续遍历IN后面常量集合中的下一个值，直到该常量集合遍历完成。
使用IN-List Iterator有几点值得注意，
(1) IN-List Iterator是IN后面是常量集合的首选方法。
(2) IN-List Iterator处理IN的前提条件，是IN所在的列上一定要有索引。
(3) 没有强制走IN-List Iterator的HINT，但可以通过联合设置10142和10157事件禁掉IN-List Iterator。
执行测试语句，SQL> select * from employees where department_id in (10,20,30);
他的执行计划，可以看到INLIST ITERATOR，通过谓词，IN确实用OR进行改写，这两者是等价的，SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 9 | 621 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 9 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(("DEPARTMENT_ID"=10 OR "DEPARTMENT_ID"=20 OR "DEPARTMENT_ID"=30))
P.S.
可能有些朋友注意到了，id=3索引范围扫描，得到rowid，id=2需要根据rowid回表，正常来讲，操作就是“TABLE ACCESS BY INDEX ROWID”，但是此处标记TABLE ACCESS BY INDEX ROWID BATCHED，这是什么意思？
查了下资料，这是12c开始提供的新特性，The BATCHED access shown in Step 1 means that the database retrieves a few rowids from the index, and then attempts to access rows in block order to improve the clustering and reduce the number of times that the database must access a block.Oracle官方
这句话的直译，该操作是数据库为了从索引中获取一些rowid，接着，试着按照块顺序存取块中的数据行，以便用来改善聚集效果和减少对一个数据块存取的次数。
翻译成人类语言，之前，当我们通过索引获取的rowid回表获取相应数据行时，都是读一个rowid回表获取一次相应数据行，然后，再读一个rowid，再回表获取一次相应数据行，这样一直读取完所有所需数据。当不同rowid对应的数据行存储在一个数据块中时，就可能会发生对同一表数据块的多次读取，尤其是当索引的聚集因子比较高时，这是必然结果，从而浪费了系统资源。Oracle 12c中的新特性，通过对rowid对应的数据块号进行排序，然后回表读取相应数据行，从而避免了对同一表数据块的多次重复读取，改善了SQL语句性能，降低了资源消耗。
该特性通过隐藏参数“_optimizer_batch_table_access_by_rowid”控制，默认值为true，即为开启。
除此之外，开头我们说了，IN-List Iterator处理IN的前提条件，是IN所在的列上一定要有索引。如果我们删除department_id字段的索引，SQL> drop index EMP_DEPARTMENT_IX;
Index dropped.
此时执行计划，就改成了全表扫描了，证明了连接列上存在索引，是IN-List Iterator使用的前提，SQL> select * from employees where department_id in (10,20,30);
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 9 | 621 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("DEPARTMENT_ID"=10 OR "DEPARTMENT_ID"=20 OR
"DEPARTMENT_ID"=30))
2. 使用IN-List Expansion
因为IN和OR在Oracle中是等价的，所以IN-List Expansion和OR Expansion是等价的，他是处理IN后面常量集合的另一种方法。简单来讲，优化器会将目标SQL中IN后面的常量集合拆开，将每个常量都提出来形成一个分支，分支之间使用UNION ALL来连接，即将IN的SQL等价改写成UNION ALL连接的各个分支。
拆成各个分支，好处就是每个分支可以用自己的索引、分区剪裁等特性，互不干扰。坏处就是此时需要对等价改写后的每个UNION ALL分支都执行同样的解析、确定执行计划的工作，因此SQL的解析时间会随着UNION ALL分支的递增而递增，可以想象，如果IN后面的常量集合数量很多，仅解析时间，就会很长，所以通常情况下，IN-List Iterator的效率高于IN-List Expansion。从另外的角度讲，Oracle的CBO是根据成本值选择执行计划的，只有当经过IN-List Expansion等价改写的SQL成本值低于IN-List Iterator，Oracle才会对SQL采用IN-List Expansion。
我们尝试让SQL强制使用IN-List Expansion，SQL> select /*+ use_concat */ * from employees where department_id in (10, 20, 30);
他还是使用的IN-List Iterator，并未采用IN-List Expansion，----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 9 | 621 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 9 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(("DEPARTMENT_ID"=10 OR "DEPARTMENT_ID"=20 OR "DEPARTMENT_ID"=30))
这就说明了CBO认为IN-List Expansion的成本高于IN-List Iterator，所以没采用IN-List Expansion。
为了证明这点，可以禁用IN-List Iterator，SQL> alter session set events '10142 trace name context forever';
Session altered.
SQL> alter session set events '10157 trace name context forever';
Session altered.
可以看到，id=1是CONCATENATION，其含义就相当于UNION ALL，从执行计划、谓词信息，能看出是将IN的常量值，拆成了三段，分别都用到了department_id的索引然后使用CONCATENATION进行合并，SQL> select /*+ use_concat */ * from employees where department_id in (10, 20, 30);
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 69 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 2 | 138 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 2 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 6 | 414 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 6 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPARTMENT_ID"=10)
5 - access("DEPARTMENT_ID"=20)
7 - access("DEPARTMENT_ID"=30)
针对hr用户的employees测试表数据，在19c使用no_expand强制不做IN-List Expansion和不带任何HINT，select * from employees where department_id in (10, 20, 30);
select /*+ no_expand */ * from employees where department_id in (10, 20, 30);
都是用全表扫描，说明CBO认为他的成本是最低的，从另一个角度看，说明IN后面的常量集合除了IN-List Iterator(已经禁掉)和IN-List Expansion(强制no_expand不用)两种方式，就只能使用全表扫描了，-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 9 | 621 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("DEPARTMENT_ID"=30 OR "DEPARTMENT_ID"=20 OR
"DEPARTMENT_ID"=10))
既然IN-List Expansion是将IN值拆开执行，更合适的场景，可能就是拆开的每个SQL可以用上不同的索引，例如，employees的manager_id和department_id都存在普通索引，我们推测他应该能用上IN-List Expansion，但是有些出乎意料了，他是对两个索引字段扫描，然后用了BITMAP CONVERSION FROM ROWIDS，再进行的BITMAP OR，SQL> select * from employees where manager_id=201 or department_id=10;
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 (100)| | 2 |00:00:00.01 | 4 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 2 | 3 (0)| 00:00:01 | 2 |00:00:00.01 | 4 |
| 2 | BITMAP CONVERSION TO ROWIDS | | 1 | | | | 2 |00:00:00.01 | 2 |
| 3 | BITMAP OR | | 1 | | | | 1 |00:00:00.01 | 2 |
| 4 | BITMAP CONVERSION FROM ROWIDS | | 1 | | | | 1 |00:00:00.01 | 1 |
|* 5 | INDEX RANGE SCAN | EMP_MANAGER_IX | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 |
| 6 | BITMAP CONVERSION FROM ROWIDS | | 1 | | | | 1 |00:00:00.01 | 1 |
|* 7 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 |
-------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("MANAGER_ID"=201)
7 - access("DEPARTMENT_ID"=10)
出现BITMAP CONVERSION TO ROWIDS，未必就一定意味着有bitmap index，走了bitmap index，就像这个例子，这个操作只是一种数据转换方法，而不是数据访问方法。
引述一段惜分飞大师对BITMAP CONVERSION FROM ROWIDS的介绍，Oracle将btree索引中获得的rowid信息通过BITMAP CONVERSION FROM ROWIDS的步骤转换成bitmap进行匹配,然后匹配完成后通过BITMAP CONVERSION TO ROWIDS再转换出rowid获得数据或者回表获得数据。这种现象出现的原因是因为Oracle的cbo是根据cost来决定大小来选择合适的执行计划，当他计算获得通过bitmap的方式执行的时候cost会更小，他就会选择使用这样的执行计划。一般出现这样的情况，都是因为对表建立的不适当的index导致，特别是对表中的唯一度不高的列建立了index，然后Oracle就有可能选择两个这样的列转为为bitmap来执行。根据Oracle的执行计划，肯定是cost最小的，但是他很多时候忽略了一致性读等其他条件，导致这个执行计划并非像Oracle想象的那样最优，因为把btree index转为为bitmap index执行，需要消耗更多的cpu，特别是在cpu比较紧张的系统中，所以这样的情况如果发生在oltp系统中，一般都需要解决。
强制使用IN-List Expansion，cost确实高了1，SQL> select /*+ use_concat */ * from employees where manager_id=201 or department_id=10;
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 (100)| | 2 |00:00:00.01 | 5 |
| 1 | CONCATENATION | | 1 | | | | 2 |00:00:00.01 | 5 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 1 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 1 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |
|* 4 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 1 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |
|* 5 | INDEX RANGE SCAN | EMP_MANAGER_IX | 1 | 1 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPARTMENT_ID"=10)
4 - filter(LNNVL("DEPARTMENT_ID"=10))
5 - access("MANAGER_ID"=201)
P.S. 这两个SQL是等价的，SQL> select /*+ use_concat */ * from employees where manager_id=201 or department_id=10;
SQL> select * from hr.employees where manager_id=201
union all
select * from hr.employees where department_id=10 and lnnvl(manager_id=201);
IN-List Expansion作为SQL执行计划的选择之一，毕竟他的好处就在于拆开的UNION ALL分支就可以使用各自的索引、分区剪裁、表连接等，能不能用，就看他和其他执行计划的成本比较了。
IN后面的常量集合元素很多的时候，如果使用IN-List Expansion，仅解析时间就会很长，执行效率会受到影响，通常可以采用两种解决方案，
(1) 使用no_expand，不让CBO使用IN-List Expansion类型的执行计划，他可能选择IN-List Iterator或者全表扫描。
(2) 将IN后面的常量集合存储在中间表中，将原SQL中的IN改写成和这个中间表做表连接，替代IN-List Expansion。
3. 使用IN-List Filter
(1)和(2)介绍的两种IN处理方式是针对IN跟着常量集合的，如果是子查询，就会使用(3)和(4)的处理形式。
首先，第一种处理IN跟着子查询的方式是IN-List Filter，他的意思是将子查询中的结果集作为过滤条件，并且执行FILTER类型的执行计划。
FILTER的执行过程，包括三个步骤，
(1) 得到一个驱动结果集。
(2) 根据过滤条件，从上述结果集中滤除不满足条件的记录。
(3) 结果集中剩下的记录就会返回给用户或者进入下一个执行步骤。
如下这条SQL，他的执行计划，就是FILTER，按照上述步骤，驱动结果集是employees的所有记录，过滤条件就是子查询的结果集，在employees中过滤不满足条件的记录，SQL> select employee_id, hire_date from employees where department_id in (select /*+ no_unnest */ department_id from departments where department_name='Shipping' and rownum < 10);
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 36 (100)| | 45 |00:00:00.01 | 64 |
|* 1 | FILTER | | 1 | | | | 45 |00:00:00.01 | 64 |
| 2 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 3 (0)| 00:00:01 | 107 |00:00:00.01 | 10 |
|* 3 | FILTER | | 12 | | | | 1 |00:00:00.01 | 54 |
|* 4 | COUNT STOPKEY | | 12 | | | | 12 |00:00:00.01 | 54 |
|* 5 | TABLE ACCESS FULL| DEPARTMENTS | 12 | 1 | 3 (0)| 00:00:01 | 12 |00:00:00.01 | 54 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(IS NOT NULL)
3 - filter("DEPARTMENT_ID"=:B1)
4 - filter(ROWNUM<10)
5 - filter("DEPARTMENT_NAME"='Shipping')
这种FILTER类型，外部查询(employees)结果集中的每一条记录，该子查询都会被当作一个独立的执行单元来执行一次，但是次数可能未必像Nested Loop嵌套循环连接中外部查询结果集有多少记录，子查询就执行多少次。
因为从上面的执行计划，我们看到，驱动结果集记录数是107，但是被驱动表并未访问107次，而是12次，
之所以在子查询中用了no_unnest的HINT，因为不让Oracle对子查询做子查询展开是FILTER类型执行计划的前提。
4. 对IN做子查询展开/视图合并
第二种处理IN跟着子查询的方式就是做子查询展开/视图合并。他是指优化器对目标SQL的IN后面的子查询做子查询展开，或者既做子查询展开又做视图合并。
(1) 子查询展开
他是指优化器不再将目标SQL中的子查询当作一个独立的处理单元来单独执行，而是将该子查询转换为他自身和外部查询之间等价的表连接。
这种等价表连接有两种形式，
(a) 子查询拆开，即将子查询中的表、视图从子查询中拿出来，和外部查询中的表、视图做连接。
(b) 不拆开，但是会将子查询转换为一个内嵌视图(Inline View)，然后再和外部查询中的表、视图做表连接。Oracle 10g以上，只有当改写的SQL成本值小于原SQL成本值时，才会进行子查询展开。
(a) 子查询拆开
Oracle会确保子查询展开所对应的表连接的正确性，要求转换后的SQL和原SQL语义上是等价的。不是所有的子查询都可以展开，对于这些SQL，Oracle还是会将其作为一个独立的处理单元来执行。
之所以SQL可能会做子查询展开，因为如果原SQL不做子查询展开，通常情况下该子查询会在执行计划的最后一步才执行，并且使用FILTER类型的执行计划，在(3)中我们介绍过。外部查询结果集的多少，就会决定子查询执行的次数，执行效率可能不会很高，尤其子查询是两张表或者多张表关联的时候，子查询展开往往会比FILTER的效率高。
Oracle子查询前的where条件如果是如下这些条件之一，SQL满足一定条件后就可以做子查询展开，
(1) SINGLE-ROW(=、、<=、>=、<>)
(2) EXISTS
(3) NOT EXISTS
(4) IN
(5) NOT IN
(6) ANY
(7) ALL
对IN子查询，他其实和ANY、EXISTS可以等价转换的，例如，select t1.cust_last_name, t1.cust_id
from customers t1
where exists (select 1 from sales t2 where t2.amount_sold > 1710 and t2.cust_id = t1.cust_id);
从语义上，和以下的ANY、EXISTS等价，select t1.cust_last_name, t1.cust_id
from customers t1
where t1.cust_id = any (select t2.cust_id from sales t2 where t2.amount_sold > 1710);
select t1.cust_last_name, t1.cust_id
from customers t1
where exists (select 1 from sales t2 where t2.amount_sold > 1710 and t2.cust_id = t1.cust_id);
我们看下SQL不做子查询展开的执行计划，会按照(3)中的FILTER，外层表CUSTOMERS行数55500，子查询重复执行55500次，导致Cost达到1721K，SQL> select t1.cust_last_name, t1.cust_id from customers t1 where t1.cust_id
in (select *+ no_unnest */ t2.cust_id from sales t2 where
t2.amount_sold > 1710)
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1721K(100)| | | | 720 |00:00:02.83 | 2315K| 1454 |
|* 1 | FILTER | | 1 | | | | | | 720 |00:00:02.83 | 2315K| 1454 |
| 2 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 | 405 (1)| 00:00:05 | | | 55500 |00:00:00.02 | 1502 | 1454 |
| 3 | PARTITION RANGE ALL | | 55500 | 2 | 39 (0)| 00:00:01 | 1 | 28 | 720 |00:00:02.78 | 2314K| 0 |
|* 4 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 1540K| 2 | 39 (0)| 00:00:01 | 1 | 28 | 720 |00:00:02.59 | 2314K| 0 |
| 5 | BITMAP CONVERSION TO ROWIDS | | 879K| | | | | | 817K|00:00:01.38 | 1769K| 0 |
|* 6 | BITMAP INDEX SINGLE VALUE | SALES_CUST_BIX | 879K| | | | 1 | 28 | 33908 |00:00:01.11 | 1769K| 0 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
4 - filter("T2"."AMOUNT_SOLD">1710)
6 - access("T2"."CUST_ID"=:B1)
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT*+ NO_UNNEST */ 0 FROM "SALES" "T2" WHERE "T2"."CUST_ID"=:B1 AND
"T2"."AMOUNT_SOLD">1710))
4 - filter("T2"."AMOUNT_SOLD">1710)
6 - access("T2"."CUST_ID"=:B1)
如果允许子查询展开，他用的就是哈希半连接(IN的语义就是只要子查询有1条满足条件的，就会返回第一条，即使存在满足条件的多条记录)，细致的朋友，可能会注意到，子查询的条件，从>1710改为>700，返回的记录数会更多了，但是执行比上述子查询不展开要更快，原因就是子查询只执行了一次，不再是55500次，CUSTOMERS和SALSES结果集，进行哈希半连接，SQL> select t1.cust_last_name, t1.cust_id
from customers t1
where t1.cust_id in (select t2.cust_id from sales t2 where t2.amount_sold > 700);
---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1583 (100)| | | | 4739 |00:00:00.11 | 3406 | 3073 |
|* 1 | HASH JOIN SEMI | | 1 | 7059 | 1583 (1)| 00:00:19 | | | 4739 |00:00:00.11 | 3406 | 3073 |
| 2 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 | 405 (1)| 00:00:05 | | | 55500 |00:00:00.01 | 1456 | 1454 |
| 3 | PARTITION RANGE ALL| | 1 | 560K| 526 (2)| 00:00:07 | 1 | 28 | 39256 |00:00:00.07 | 1950 | 1619 |
|* 4 | TABLE ACCESS FULL | SALES | 28 | 560K| 526 (2)| 00:00:07 | 1 | 28 | 39256 |00:00:00.07 | 1950 | 1619 |
---------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."CUST_ID"="T2"."CUST_ID")
3 - filter("T2"."AMOUNT_SOLD">700)
这种子查询展开，实际上将其改写成了，两表关联，semi只是展示形式，不能实际执行，select t1.cust_last_name, t1.cust_id
from customers t1, sales t2
where t1.cust_id semi= t2.cust_id and t2.amount_sold > 700;
改为等价的内连接形式就是，select t1.cust_last_name, t1.cust_id
from customers t1,
(select distinct cust_id cust_id from sales where amount_sold > 700) t2
where t1.cust_id = t2.cust_id;
如果子查询的连接字段(例如sales的cust_id)是主键或者存在唯一性索引，换句话说，不存在重复值，上述子查询展开就可以不是哈希半连接，而是内连接。我们测试下，首先，将sales中cust_id唯一值存入一张新表sales_test，SQL> create table sales_test as select * from sales
2 where rowid in (select rid from
3 (select rowid rid, row_number() over(partition by cust_id order by rowid) rn from sales)
4 where rn = 1);
Table created.
增加主键，SQL> alter table sales_test add constraint pk_sales_test primary key(cust_id);
Table altered.
此时执行计划就是哈希连接，没出现semi关键字了，SQL> select t1.cust_last_name, t1.cust_id
from customers t1
where t1.cust_id in (select t2.cust_id from sales_test t2 where t2.amount_sold > 700);
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 417 (100)| | 1970 |00:00:00.03 | 1623 | 1454 |
|* 1 | HASH JOIN | | 1 | 1970 | 417 (1)| 00:00:06 | 1970 |00:00:00.03 | 1623 | 1454 |
|* 2 | TABLE ACCESS FULL| SALES_TEST | 1 | 1970 | 12 (0)| 00:00:01 | 1970 |00:00:00.01 | 37 | 0 |
| 3 | TABLE ACCESS FULL| CUSTOMERS | 1 | 55500 | 405 (1)| 00:00:05 | 55500 |00:00:00.02 | 1586 | 1454 |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."CUST_ID"="T2"."CUST_ID")
2 - filter("T2"."AMOUNT_SOLD">700)
(b) 不拆开子查询
子查询展开的第二种形式，就是不拆开子查询，但是会将子查询转换为一个内嵌视图(Inline View)，然后再和外部查询中的表、视图做表连接。
如下SQL，子查询是sales和products两表连接，两者进行哈希连接的结果集，产生一个视图，VW_NSO_1(VW应该是View的缩写，NSO可以理解为Nested Subquery Optimizing)，然后这个视图和外层customers进行哈希半连接，SQL> select t1.cust_last_name, t1.cust_id
from customers t1
where t1.cust_id in (select t2.cust_id from sales t2, products t3
where t2.prod_id = t3.prod_id and t2.amount_sold > 700);
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1664 (100)| | | | 4739 |00:00:00.13 | 3407 | 3074 |
|* 1 | HASH JOIN SEMI | | 1 | 7059 | 1664 (1)| 00:00:20 | | | 4739 |00:00:00.13 | 3407 | 3074 |
| 2 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 | 405 (1)| 00:00:05 | | | 55500 |00:00:00.01 | 1456 | 1454 |
| 3 | VIEW | VW_NSO_1 | 1 | 560K| 528 (2)| 00:00:07 | | | 39256 |00:00:00.09 | 1951 | 1620 |
|* 4 | HASH JOIN | | 1 | 560K| 528 (2)| 00:00:07 | | | 39256 |00:00:00.08 | 1951 | 1620 |
| 5 | INDEX FULL SCAN | PRODUCTS_PK | 1 | 72 | 1 (0)| 00:00:01 | | | 72 |00:00:00.01 | 1 | 1 |
| 6 | PARTITION RANGE ALL| | 1 | 560K| 526 (2)| 00:00:07 | 1 | 28 | 39256 |00:00:00.07 | 1950 | 1619 |
|* 7 | TABLE ACCESS FULL | SALES | 28 | 560K| 526 (2)| 00:00:07 | 1 | 28 | 39256 |00:00:00.07 | 1950 | 1619 |
-------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."CUST_ID"="CUST_ID")
4 - access("T2"."PROD_ID"="T3"."PROD_ID")
7 - filter("T2"."AMOUNT_SOLD">700)
比较一下，如果禁止子查询展开，子查询的两张表，做了嵌套循环连接然后和外层的表进行FILTER，Cost很高，效率很低，SQL> select t1.cust_last_name, t1.cust_id
from customers t1
where t1.cust_id in (select *+ no_unnest */ t2.cust_id from sales t2, products t3
where t2.prod_id = t3.prod_id and t2.amount_sold > 700);
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 677K(100)| | | | 37 |00:00:33.65 | 659K| 656K|
|* 1 | FILTER | | 1 | | | | | | 37 |00:00:33.65 | 659K| 656K|
| 2 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 | 405 (1)| 00:00:05 | | | 455 |00:00:00.01 | 17 | 28 |
| 3 | NESTED LOOPS | | 455 | 50 | 15 (0)| 00:00:01 | | | 37 |00:00:35.79 | 702K| 698K|
| 4 | PARTITION RANGE ALL| | 455 | 2 | 15 (0)| 00:00:01 | 1 | 28 | 37 |00:00:35.79 | 702K| 698K|
|* 5 | TABLE ACCESS FULL | SALES | 12091 | 2 | 15 (0)| 00:00:01 | 1 | 28 | 37 |00:00:35.85 | 703K| 699K|
|* 6 | INDEX UNIQUE SCAN | PRODUCTS_PK | 37 | 24 | 0 (0)| | | | 37 |00:00:00.01 | 37 | 1 |
------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
5 - filter(("T2"."CUST_ID"=:B1 AND "T2"."AMOUNT_SOLD">700))
6 - access("T2"."PROD_ID"="T3"."PROD_ID")
(a)和(b)两种子查询展开的区别是，
(a)只要原始SQL能展开子查询，不会考虑子查询展开的成本，就会按照子查询展开来执行。
(b)不拆开子查询，但会将其作为一个内嵌视图的子查询展开，只当改写的SQL成本值小于原始SQL，才会进行子查询展开。
IN跟着子查询除了可以做子查询展开，还可做视图合并，顾名思义，前提是子查询中包含视图，有两种情形，
(a)由于该视图不能做视图合并，只对其做了子查询展开。
(b)由于该视图可做视图合并，既对其做了子查询展开，又对其做了视图合并。
视图合并的场景更复杂，我还有待学习，今天先写到这。
近期更新的文章：
文章分类和索引：
5856
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
