在优化器转换系列博客的第一部分，我们讨论了子查询展开。我们解释了子查询如何被评估，以及exists和any子查询的展开。
这是该系列的第二篇，我们将会讨论not exists和出现在where子句中的单行子查询的展开。我要再一次感谢rafi ---- 优化器的高级开发人员之一，他为本博文提供了内容。
快速回顾子查询展开的意义
子查询展开是一种优化，它转换子查询为外连接查询，从而允许优化器在访问路径，连接方法和连接顺序的选择时考虑子查询表。展开要么合并子查询为外部查询，要么转换为内联视图（译者注：出现在from子句中的子查询）
not exists 子查询
反连接总是用来展开not exists和all子查询。我们使用如下的非标准语法来表示反连接：t1.x a= t2.y，t1是反连接中的左表，t2是右表。反连接的语义是：只要t1.x在t2.y中的任一个值中找到匹配，则拒绝t1表中的这一行。只有当t1.x未匹配到t2.y中的任一个值时，才会返回t1表中的这一行。
考虑查询f，它是一个包含两个表的not exists子查询。
f.
select c.cust_last_name, c.country_id
from customers c
where not exists (select 1
from sales s, products p
where p.prod_id = s.prod_id and
p.prod_min_price > 90 and
s.cust_id = c.cust_id);
f中的子查询可以使用反连接展开；然而，子查询中的表是内连接，sales和products表必须在反连接前被执行。为了强制连接的顺序，产生了内联视图。该展开产生了查询g；这时，内联视图成为了反连接中的右表。
g.
select c.cust_last_name, c.country_id
from customers c,
(select s.cust_id as s_cust_id
from sales s, products p
where p.prod_id = s.prod_id and p.prod_min_price > 90) vw
where c.cust_id a= vw.s_cust_id;
g的执行计划如xg所示。在三种连接方法中（即，嵌套循环连接，hash连接和排序合并连接），hash连接被优化器选中来完成反连接。
xg.
explain_plan_for_g.png
单行聚合子查询
考虑查询h，它包含一个聚合的，相关的和单行的子查询。
h.
select c.cust_last_name, c.cust_income_level
from customers c
where c.cust_credit_limit < (select sum(s.amount_sold)
from sales s
where s.cust_id = c.cust_id);
对多个等值相关的谓词做聚合，类似于在子查询列上做group-by的聚合，然后与外层表在group-by列上做连接。h中的子查询是以消除相关并转换为group-by视图的方式来展开的，其与外层表customer是内连接。相关条件和连接条件都被转换为了连接谓词。该转换如i所示：
i.
select c.cust_last_name, c.cust_income_level
from customers c,
select sum(s.amount_sold) as sum_amt, s.cust_id
from sales s
group by s.cust_id) vw
where c.cust_credit_limit < vw.sum_amt and
c.cust_id = vw.cust_id;
i的执行计划如xi所示：
xi.
explain_plan_for_i.png
展开的有效性
每一个子查询，在被展开前，要通过一系列的有效性检查。优化器决定展开或者决定不展开一个子查询，可以通过指定适当的hint来跳过，但是有效性不可以被跳过。因为这将不能保证展开在语义上是等价的。
下面，我们列举了一些目前会使子查询展开无效的检查。注意，这个检查清单并不代表全部。
- 与非父表发生相关的子查询;例如，子查询sq3被sq2所包含（sq2是sq3的父亲），sq2又被sq1所包含，而sq3与sq1中涉及的列是相关的。
- group-by子查询是相关的;这种情况下，展开隐含是在group-by之后做连接，但特定两个操作次序的改变，并不总是对的。
- 连接和相关条件格式不佳（例如，它们的本地列与相关列出现在谓词的同一侧）并且子查询需要生成内联视图，当这样的谓词出现时，是不允许分离视图列和外层表列的。
- 对于反意连接（译者注：即or逻辑）子查询，出现在连接条件或相关条件的外层表列是不同的。
使用视图合并转换，oracle可以合并展开时生成的group-by或distinct内联视图，因此，当预期会有一个视图出现时，但在执行计划中可能却并没有出现任何视图。
总结
在这些博文中，我们尝试通过简单的示例查询，来演示不同类型子查询展开背后的基础理论。oracle可以处理更复杂的查询–在一层或多层中有多个子查询的语句，有多个表，相关或连接条件包含非等谓词和表达式，子查询包含集合操作符，带有group-by和count聚合函数的子查询，all子查询在连接条件列上含有可为空的列，以及反意连接子查询。
如果子查询的展开，并不需要生成内联视图，那么子查询总是展开的。因为这种展开通过允许更多的连接方法和连接次序，从而提供更有效率的执行计划。如果子查询中相关谓词中的本地列（例如查询a中的s.cust_id)上有索引，那么子查询会评估为类似于基于索引的嵌套循环连接。因此，在某些情况下，不展开也许会比生成内联视图，仅允许与外层表做排序合并和hash连接的展开更有效率。故而，oracle中，对于生成内联视图的子查询展开，是在基于成本的查询转换框架下，基于成本进行的。
再次感谢rafi在本博文中的贡献。请关注米乐app官网下载在该系列中的下一篇博文，是有关视图合并的。
原文链接：
原文内容：
optimizer transformations: subquery unesting part 2
january 1, 2020 | 4 minute read
maria colgan
distinguished product manager
in of our blog series on optimizer transformations we discussed subquery unesting. we explained how subqueries are evaluated, and the unnesting of exists and any subqueries.
here in part two of our series we will discuss the unnesting of not exists and single-row subqueries, which appear in the where clause. once again i need to give credit to rafi one of the senior optimizer developers for the content of this blog!
quick recap on what we mean by subquery unesting
subquery unnesting is an optimization that converts a subquery into a join in the outer query and allows the optimizer to consider subquery table(s) during access path, join method, and join order selection. unnesting either merges the subquery into the body of the outer query or turns it into an inline view.
not exists subqueries
anti-join is always used for unnesting not exists and all subqueries. we represent anti-join by the following non-standard syntax: t1.x a= t2.y, where t1 is the left table and t2 is the right table of the anti-join. the semantics of anti-join is the following: a row of t1 is rejected as soon as t1.x finds a match with any value of t2.y. a row of t1 is returned, only if t1.x does not match with any value of t2.y.
consider query f, which has a not exists subquery containing two tables.
f.
select c.cust_last_name, c.country_id
from customers c
where not exists (select 1
from sales s, products p
where p.prod_id = s.prod_id and
p.prod_min_price > 90 and
s.cust_id = c.cust_id);
the subquery in f can be unnested by using an anti-join; however, the inner join of the tables in the subquery, sales and products must take place before the anti-join is performed. an inline view is generated in order to enforce the join order. this unnesting produces query g; here the inline view becomes the right table of anti-join.
g.
select c.cust_last_name, c.country_id
from customers c,
(select s.cust_id as s_cust_id
from sales s, products p
where p.prod_id = s.prod_id and p.prod_min_price > 90) vw
where c.cust_id a= vw.s_cust_id;
the execution plan of g is shown as xg. of the three join methods (i.e., nested-loop, hash and sort-merge), the hash method was selected by the optimizer to do the anti-join.
xg.
explain_plan_for_g.png
single-row aggregated subqueries
consider query h that contains an aggregated, correlated, single-row subquery.
h.
select c.cust_last_name, c.cust_income_level
from customers c
where c.cust_credit_limit < (select sum(s.amount_sold)
from sales s
where s.cust_id = c.cust_id);
doing aggregation for multiple values of equality correlation predicate is like doing aggregation and group-by on the local column, and then joining the view with the outer table on the group-by columns. the subquery in h is unnested by decorrelating it and converting it into a group-by view, which is inner joined with the outer table, customer; here both the correlating and connecting conditions have been turned into join predicates. the transformed query is shown as i.
i.
select c.cust_last_name, c.cust_income_level
from customers c,
select sum(s.amount_sold) as sum_amt, s.cust_id
from sales s
group by s.cust_id) vw
where c.cust_credit_limit < vw.sum_amt and
c.cust_id = vw.cust_id;
xi shows the execution plan of i.
xi.
explain_plan_for_i.png
validity of unnesting
every subquery, before it can be unnested, goes through a set of validity checks. the optimizer decisions to unnest or not to unnest a subquery can be overridden by specifying an appropriate hint, but the validity requirements cannot be, since unnesting in such cases would not guarantee a semantically equivalent query.
in the following, we enumerate some important checks that currently invalidate subquery unnesting. note that this list of checks is by no means exhaustive.
-
subqueries that are correlated to non-parent; for example, subquery sq3 is contained by sq2 (parent of sq3) and sq2 in turn is contained by sq1 and sq3 is correlated to tables defined in sq1.
-
a group-by subquery is correlated; in this case, unnesting implies doing join after group-by. changing the given order of the two operations may not be always legal.
-
connecting or correlating conditions are not well-formed (e.g., they contains a mix of local and correlated columns on either side of the predicate) and the subquery requires inline view generation, as predicates of this kind do not allow separating out view columns and outer table columns.
-
for disjunctive subqueries, the outer columns in the connecting or correlating conditions are not the same.
using view-merging transformation, oracle may merge the group-by or distinct inline view generated during unnesting, and therefore the execution plan may not show any view even when a view is expected.
summary
in these posts we have tried to illustrate the basic ideas behind unnesting of different types of subquery by showing simple example queries. oracle can handle far more complex queries - query statements with multiple subqueries at one or more levels, multiple tables, correlated and connecting conditions containing inequality predicates and expressions, subqueries that contain set operators, subqueries with group-by and count aggregate function, all subqueries containing nullable columns in its connecting condition, and subqueries in disjunction.
if unnesting of a subquery does not require generation of an inline view, then the subquery is always unnested, because this unnesting provides a more efficient execution plan by allowing more join methods and join orders. if the local column (e.g., s.cust_id in a) in the correlating predicate of the subquery has an index on it, then the subquery evaluation becomes akin to doing index-based nested-loop join; and thus, in some cases, not unnesting may be more efficient than unnesting that generates an inline view and enables only sort-merge and hash join of the view with outer tables. therefore, in oracle, subquery unnesting which generates inline views, is done based on cost under the cost-based query transformation framework.
once again many thanks to rafi for all his work on this blog post. watch out for our next post in this series on view merging.