1.定义：

The purpose of subquery unnesting is to inject semi- (IN, EXISTS), anti-join (NOT IN, NOT EXISTS), and scalar subqueries into the FROM clause of the containing query block, and to transform them into inline views. Some unnestings are performed as heuristic-based query transformations, and others are carried out as cost-based query transformations. The main reason for applying this query transformation is to enable all available join methods. In fact, without subquery unnesting, a subquery might have to be executed once for every row returned by the containing query block . Subquery unnesting can’t always be applied, though. For example, unnesting isn’t possible if a subquery contains some types of aggregation, or if it contains the rownum pseudocolumn. Semi- and anti-join subqueries containing set operators can only be unnested as of version 11.2. In addition, from version 12.1 onward, scalar subquery unnesting has been improved to process scalar subqueries in SELECT clauses

Unnesting a subquery can be summarized in two steps.

The first step, as shown in the following query, is to rewrite the subquery as an inline view. Note that what follows isn’t a valid SQL statement, because the operator implementing the semi-join (s=) isn’t available in the SQL syntax (it’s used internally by the SQL engine only):

The second step, as shown here, is to rewrite the inline view as a regular join

简单来说就是当sql出现嵌套子查询时，CBO会尝试将子查询展开，使子查询的表能与外部查询的表相关联。

2.分类：

SU - subquery unnesting

1)semi-join

当子查询前的where条件：exists/in/any
CBO会这样转换：SU: Transform an ANY subquery to semi-join or distinct

2)anti-join

当子查询前的where条件：not exists/not in/all
CBO会这样转换：SU: Transform ALL/NOTEXISTS subquery into a regular anti-join

3.参数(默认为true)

KSPPINM KSPPSTVL KSPPDESC
---------------------------------------- ---------- --------------------------------------------------
_unnest_subquery TRUE enables unnesting of complex subqueries
_optimizer_unnest_all_subqueries TRUE enables unnesting of every type of subquery

hint：unnest/no_unnest

4.限制

必要条件：对于semi-join和anti-join都有一个共同的限制就是_unnest_subquery和_optimizer_unnest_all_subqueries需要同时为true，而且子查询不能与or在一个查询块内,如：select ... from xxx where exists(subquery) or xxx

1)semi-join

A.子查询内只存在一个表的

这种情况属于启发式查询转化，只要满足上述的必要条件，CBO就会做子查询展开，而不去考虑cost。但是实验证明在没有统计信息采用动态采样的情况下，子查询是否展开还是会基于cost的。

例如下面例子：
select * from lxy_1 a where exists (select 1 from lxy b where a.tablespace_name=b.tablespace_name)
Plan hash value: 406768689
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| | 未展开cost=5
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| LXY_1 | 6 | 1170 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| LXY | 29 | 493 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)

select * from lxy_1 a where exists (select /*+unnest*/1 from lxy b where a.tablespace_name=b.tablespace_name)
Plan hash value: 1055283876
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 18 (100)| | 展开后cost=18
|* 1 | HASH JOIN SEMI | | 6 | 1272 | 18 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | LXY_1 | 6 | 1170 | 2 (0)| 00:00:01 |
| 3 | VIEW | VW_SQ_1 | 2921 | 49657 | 16 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| LXY | 2921 | 49657 | 16 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)

这里CBO选择了cost较低的不让子查询展开。

B.子查询内存在多表关联的

这种情况CBO会将子查询转化成一个内嵌视图后，再与外部查询做半连接，但是必须满足子查询展开后的COST<未子查询展开的COST

2）anti-join

A.NOT EXISTS

这种情况需要满足上述的必要条件，并且必须满足子查询展开后的COST<未子查询展开的COST

B.NOT IN/<>ALL

这种情况除了要需要满足上述的必要条件和子查询展开后的COST<未子查询展开的COST之外，由于对null敏感还需要满足下列条件：
(1)11G前必须连接列上有not null约束或者在sql上写明连接列is not null，CBO会对子查询展开做反连接(ANTI JOIN)
(2)11G以后只需要参数_optimizer_null_aware_antijoin=true，CBO会对子查询展开做一个针对null的反连接(ANTI NA JOIN)

注：11g之后如果_optimizer_null_aware_antijoin=false，但是连接列上有not null约束或者在sql上写明连接列is not null，CBO仍然会对子查询展开做一个正常的反连接(ANTI JOIN)

ITPub博客

Subquery Unnesting

1.定义：

2.分类：

1)semi-join

2)anti-join

3.参数(默认为true)

4.限制

1)semi-join

2）anti-join

最新文章