Oracle处理IN的几种方式

ゞ浴缸里的玫瑰 2023-01-09 15:40 230阅读 0赞

最近看到一些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次，

![format_png][]

之所以在子查询中用了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)由于该视图可做视图合并，既对其做了子查询展开，又对其做了视图合并。

视图合并的场景更复杂，我还有待学习，今天先写到这。

**近期更新的文章：**

《[如何搭建一支拖垮公司的技术团队？][Link 1]》

《[IP地址解析的规则][IP]》

《[MySQL的skip-grant-tables][MySQL_skip-grant-tables]》

《[国产数据库不平凡的一年][Link 2]》

《[Oracle要求顺序的top数据检索问题][Oracle_top]》

《[日常工作中碰到的几个技术问题][Link 3]》

《[了解一下sqlhc][sqlhc]》

《[Oracle的MD5函数介绍][Oracle_MD5]》

《[Oracle 19c的examples静默安装][Oracle 19c_examples]》

《[sqlplus登录缓慢的解决][sqlplus]》

《[VMWare 11安装RedHat Linux 7过程中碰到的坑][VMWare 11_RedHat Linux 7]》

《[COST值相同？是真是假？][COST]》

《[Oracle 11g的examples静默安装][Oracle 11g_examples]》

《[同名的同义词和视图解惑][Link 4]》

《[v$和v\_$的一些玄机][v_v]》

**文章分类和索引：**

《[公众号700篇文章分类和索引][700]》

[format_png]: /images/20221119/d0edb09f38d94f05a79859710b7e0d2d.png
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a527ddf52c310590250aea3a7f50e6ef0f3239a097a68a2f5506bae22bc618ce000e3440&idx=1&mid=2247491796&scene=21&sn=3fedb602e4d5acf03fc5460f288bf43c#wechat_redirect
[IP]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a53fddf52c29969fa924ed4644ebfabb1ce3d117443134060e4f7cc8f37e8177f5338700&idx=1&mid=2247491788&scene=21&sn=2790176fc4bd58abb337fd0ce786a190#wechat_redirect
[MySQL_skip-grant-tables]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a535ddf52c23f84ef003cdbadc868463c274971da2b6c79b5fe78660fc013b696c4f4339&idx=1&mid=2247491782&scene=21&sn=40c079eb9f84430e163ead1ee7b9fc85#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a549ddf52c5fb741cde2dad195f245871b0d7226a612ca326524d5af0968d674b64f0ac8&idx=1&mid=2247491770&scene=21&sn=d706bcda7de477177a15b14f64c99517#wechat_redirect
[Oracle_top]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a541ddf52c572d7c9c90b93b9f37674c07761a26b7b2566302f24fcb86636cae4e1c4cb8&idx=1&mid=2247491762&scene=21&sn=e92bdd8616515e6403c3ae3280142373#wechat_redirect
[Link 3]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a559ddf52c4f288da18bb70439b1e213a90f0587fdf2cd1d00c948f33f35be40de33bd05&idx=1&mid=2247491754&scene=21&sn=95533b540fe5809c9516468ad2f58c3e#wechat_redirect
[sqlhc]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a555ddf52c43093e82ff00b137c136c5aade5e902f84b523e609429a2de7bfacbd18ad67&idx=1&mid=2247491750&scene=21&sn=6ef9cc04a1a88217277d96072aa86fe2#wechat_redirect
[Oracle_MD5]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a57addf52c6c9d61af3dcf4f1ec9f6b93e51e6fd2d606c36fb8a243d70562c14cc525554&idx=1&mid=2247491721&scene=21&sn=8bdb65a2e11ef287f48d3d213642e4a8#wechat_redirect
[Oracle 19c_examples]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a588ddf52c9e16329e277f2dfdc74734b91bfde0770c545933fca6fb4d7cb224825169c4&idx=1&mid=2247491707&scene=21&sn=569f50f91b58c7cbcb652089e420ea11#wechat_redirect
[sqlplus]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a582ddf52c949d97894f8a2defe4b7fc21d58defa73dcae13f1cfe56d0cec41935b81c88&idx=1&mid=2247491697&scene=21&sn=ecb0e5cc2dc189be23279649b6aee945#wechat_redirect
[VMWare 11_RedHat Linux 7]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a593ddf52c85d1c3e8c3a8767e05f32dd38b61f3da14f3f8fd5521b60a6ca6fa081878c0&idx=1&mid=2247491680&scene=21&sn=27acee6fbe440c573488b4155314944d#wechat_redirect
[COST]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5afddf52cb9bd94da2c5abd6f5bdcefad9035eacccf0fb96db79ff09ec8c38ae9f1941a&idx=1&mid=2247491676&scene=21&sn=4c9c289836ef2d1774136a88705960c7#wechat_redirect
[Oracle 11g_examples]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5ccddf52cda2812771d563eea1db4c3b66f9891869920171bbe5650a45a2865bde0100e&idx=1&mid=2247491647&scene=21&sn=eca45fd1d7dffbf91ab199198adc1591#wechat_redirect
[Link 4]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5c9ddf52cdfe10f04528a52dc3522a0bce8e452ad8f28aa72546ac95c83399c23fbf563&idx=1&mid=2247491642&scene=21&sn=9f4541a6750ff754c17d6d9ddd1da09c#wechat_redirect
[v_v]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5c2ddf52cd4ed4a1a6de5d89f7a6638d8d9378c66739e63ebfd7f670002d76212eb88a2&idx=1&mid=2247491633&scene=21&sn=56f7465a50792828e5c679c69c2f6182#wechat_redirect
[700]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5dfddf52cc949a5b8b316ee905927ff399713b34aec7d806c656e8ada09bfb562c039ca&idx=1&mid=2247491628&scene=21&sn=83bd320bd28da84501fada2e8d515e77#wechat_redirect