Query Transformation-子查询篇


  • subquery unnesting
  • Push Subquery
  • Filter


subquery unnesting:

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





  • 子查询包含rownum
  • 子查询和or操作符在同一个查询块(select … from xxx where exists(subquery) or xxx)
  • 对于简单子查询_optimizer_unnest_all_subqueries必须为true
  • 对于复杂子查询必须_unnest_subquery和_optimizer_unnest_all_subqueries均为true,并且需要比较转换后的cost小于转换前的cost


  • exists/in/any => SU: Transform an ANY subquery to semi-join or distinct
  • not exists/not in/all => SU: Transform ALL/NOTEXISTS subquery into a regular anti-join


执行计划中并未出现semi-join的原因是,semi-join被转换为了inter-join,outline里面可以看到该hint SEMI_TO_INNER(@”SEL$5DA710D3″ “T2″@”SEL$2”)



执行计划为RIGHT SEMI,这个功能可以让子查询部分成为HASH JOIN的构造结果集,由参数_right_outer_hash_enable控制,默认为true


对于anti-join需要注意的是,对于not in的处理:

  • 11G前必须连接列上有not null约束或者在sql上写明连接列is not null,CBO会对子查询展开做反连接(ANTI JOIN)
  • 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)

12c之前对于标量子查询通常都需要改写为外连接,,12cr1提供了标量子查询的展开,scalar subquery => SU: Unnesting scalar subquery query block SEL$2 (#2),由参数_optimizer_unnest_scalar_sq控制,但并不是所有标量子查询都能做展开。

  • 对于聚合操作(非count)的标量子查询展开为启发式查询展开,展开成为外连接形式
  • 对于count(*)的标量子查询不允许展开,10053查看拒绝理由为SU: bypassed: Scalar subquery has null-mutating select item.
  • 非聚合操作的标量子查询不允许展开,10053查看拒绝理由为SU: bypassed: Scalar subquery may return more than one row


Push Subquery:

By default, if Oracle cannot unnest a subquery it postpones executes of that subquery to the end of the execution plan.
Historically the push_subq hint told Oracle to run any outstanding subqueries at the earliest possible moment. But in 10g this changes (for the better). Imagine I have two subqueries in the query; it is quite possible that the optimum execution plan is to run one early and the other late, but the original implementation of push_subq is an ‘all or nothing’ implementation. 10g allows you to be selective about which subqueries should be pushed.




  • 子查询带有聚合函数的(MAX/MIN/SUM/…)
  • 有多个子查询,有某个子查询可以过滤掉大量的数据,但是无法子查询展开,子查询推入可以选择该子查询被优先执行。
  • 对于未能子查询展开的sql,通常子查询都是在最后一步执行,即多表关联完后才去执行子查询,此时如果子查询可以过滤大量数据即可减少后续连接的成本,子查询推进可以让子查询先关联后再与后续的表进行连接。多表关联附带子查询。


当子查询不能展开,并且没有推入时,只能通过filter来执行,并且只能在执行计划的最后一步执行子查询。filter这个操作在《Cost Based Oracle Fundamental》有介绍。它的独特之处在于会在内存中维护一个了hash table,使之成为加强版的NESTED LOOP,filter的执行性能跟关联列值distinct数有关。

Oracle limits the size of the in-memory hash table (presumably to stop excessive memory consumption in unlucky cases).In 8i and 9i the limit on the size of the hash table seems to be 256 entries, in 10g it seems to be
1,024.This means the performance of a subquery filter can be affected by the number of different driving values that exist, the order in which they appear in the pass through the driving table,and the actual values. If the hash table is simply too small, or you have driving values that just happen to cause excessive collisions on the hash table, then you may execute the subquery far more frequently than is strictly necessary.


Subquery Coalescing:

  • The purpose of subquery coalescing is to combine equivalent semi- and anti-join subqueries into a single query block. The main reason for applying this heuristic-based query transformation, which is available as of version 11.2, is to reduce the number of table accesses, and thus to reduce the number of joins.
  • Coalescing two subqueries of the same type might drastically reduce the number of logical I/O as far as it can eliminate an entire table access. Coalescing two subqueries of different types might pre-empt the CBO from taking advantage of the unnesting transformation. Fortunately if you know how to coalesce two different subqueries you will know how to de-coalesce them to allow the CBO taking advantage of unnesting the subqueries with their main query blocks.

Subquery Coalescing属于启发式查询转换,意思是将多个子查询等价合并到同一个子查询中,由参数_optimizer_coalesce_subqueries控制。这个功能我感觉是为了避免sql开发人员sql写的烂设计的。。。能有效避免多余的资源消耗。




此条目发表在Oracle, Oralce performance分类目录,贴了, , 标签。将固定链接加入收藏夹。


您的电子邮箱地址不会被公开。 必填项已用*标注