什么是子查询展开？

子查询展开是一种查询优化技术，它可以将一个包含子查询的查询语句转换为等效的、不包含子查询的查询语句，从而提高查询的执行效率。
子查询展开的具体含义是将子查询中的SQL语句嵌入到外部查询语句中，从而构成一个新的查询语句。这个新的查询语句会使用到原子查询中用到的表，条件和操作符等，从而达到与原子查询相同的功能。
子查询展开可以将复杂的逻辑转换为更简单、更高效的查询计划，并减少多次访问数据库的次数。

为什么要使用子查询展开？

好了，我们知道什么是子查询展开了，接下来让我们来探讨一下为什么要使用子查询展开。
因为如果原SQL不做子查询展开，那么通常情况下该子查询就会在其执行计划的最后一步才被执行，并且会走FILTER类型的执行计划，这也就意味着对于外部查询所在结果集中的每一条记录，该子查询都会被当作一个独立的执行单元来执行一次，外部查询所在的结果集有多少条记录，该子查询就会被执行多少次。
子查询展开后优化器就会有其他更多、更高效的执行路径(比如哈希连接)可以选择。使用子查询展开也就大大提高查询性能，尤其是当子查询中的数据量较大时。相比于执行多条独立的查询语句，将子查询展开后，可以减少数据库对物理存储的访问次数，从而减少I/O开销，使查询更加高效。

子查询展开的执行计划分类举例

现在，让我给你举个例子，来说明子查询展开对应的执行计划分类。考虑以下这个查询语句：

1
2
3
4
5
6
select t.cust_first_name, t.cust_last_name
  from CUSTOMERS t
 where t.customer_id in (select /*+ no_unnest*/
                          s.cust_id
                           from sales s
                          where s.amount_sold > 700)

这个查询语句中使用了一个子查询来获取sales表中amount_sold>700的cust_id。在执行计划中，Oracle会走FILTER类型的执行计划，如下所示：

image-20231203103214190

NO_UNNEST Hint使优化器不对该子查询进行展开

当做子查询展开时，Oracle会把那些外部where条件为“EXISTS”、“IN”或“=ANY”的子查询转换为对应的半连接(SemiJoin)。使得查询性能更高效。

image-20231203103339122

如果连接字段的值存在不重复的情况，比如有唯一索引，那么该SQL在子查询展开时也可以改写成内连接

当做子查询展开时，Oracle经常会把那些外部where条件为“NO EXISTS”、“NOT IN”或“<>ALL”的子查询转换为对应的反连接(AntiJoin)。

image-20231203105925213

当子查询中存在表连接时，只能子查询内部先做表连接，然后再通主查询进行半连接或反连接，这时Oracle会对子查询生成一个内嵌视图，如下所示：

1
2
3
4
5
6
7
select t.cust_first_name, t.cust_last_name
  from CUSTOMERS t
 where t.customer_id not in
       (select s.cust_id
          from sales s, products p
         where s.prod_id = p.prod_id
           and s.amount_sold > 700)

对应执行计划如下：

image-20231203110605176

子查询展开的限制和注意事项

在使用子查询展开时，也需要注意一些限制和注意事项。

  1. 子查询展开所对应的等价改写SQL和原SQL在语义上一定要是完全等价的。如果改写后的SQL和原SQL并不一定能保持语义上的完全等价，这种类型的子查询就不能做子查询展开。
  2. 对于不拆开子查询但是会把它转换为一个内嵌视图的子查询展开，只有当经过子查询展开后的等价改写SQL的成本值小于原SQL的成本值时，Oracle才会对目标SQL执行子查询展开。
  3. 这里需要注意的是，将子查询拆开，把该子查询中的表、视图从子查询中拿出来，然后和外部查询中的表、视图做表连接这种情况,即使是在Oracle 10g及其以后的版本中，Oracle也不会考虑子查询展开的成本，即Oracle此时会认为这种情形下子查询展开的效率始终比不展开的效率要高，而不管经过子查询展开后的等价改写SQL的成本值是否小于原SQL的成本值。