Query Transformations : Subquery unnesting(原创)

原创

wx6323d657ef2b3 2022-09-23 17:46:40 博主文章分类：Oracle Tuning ©著作权

©著作权归作者所有：来自51CTO博客作者wx6323d657ef2b3的原创作品，请联系作者获取转载授权，否则将追究法律责任

子查询取消嵌套子
查询在父语句的 WHERE 子句中出现时将嵌套这些子查询。当 Oracle 数据库评估具有嵌套子查询的语句时，它必须多次评估子查询部分，并且可能会忽略一些有效的访问路径或连接。
取消嵌套的子查询 unnest 将子查询的主体合并到包含它的语句的主体中，从而允许优化程序在评估访问路径和联接时将它们放在一起考虑。优化程序可以取消大多数子查询，但有一些例外。这些异常包括分层子查询和子查询，这些子查询包含 ROWNUM 伪列、集合运算符之一、嵌套聚合函数或对查询块的相关引用，该查询块不是子查询的直接外部查询块。
假设不存在任何限制，优化程序会自动取消嵌套以下一些（但不是全部）嵌套子查询：

不相关的 IN 子查询
IN 和存在相关的子查询，只要它们不包含聚合函数或 GROUP BY 子句

您可以通过指示优化程序取消嵌套其他类型的子查询来启用扩展子查询取消嵌套：

您可以通过在子查询中指定HASH_AJ或MERGE_AJ提示来取消嵌套不相关的 NOT IN 子查询。
您可以通过在子查询中指定 UNNEST 提示来取消嵌套其他子查询。

参数和提示

默认情况下，子查询未在 Oracle 8i 中发生。在 8i 中，隐藏参数“_unnest_subquery”默认为 false，而在 9i、10G 和 11G
中默认为 true。查询转换基于 9i 中的启发式规则。但是，从 10G R1 开始，默认情况下，查询转换是基于成本的，因为“_optimizer_cost_based_transformation”默认为线性。（存在与基于成本的查询转换相关的错误，导致巨大的性能下降）

我们可以避免子查询取消嵌套发生在会话级别，使用下划线“_unnest_subquery”参数
SQL>更改会话集“_unnest_subquery”=false;
我们还可以控制由 UNNEST/NO_UNNEST提示嵌套的子查询。
我们可以避免子查询取消嵌套发生在语句级别，使用提示OPT_PARAM（“_unnest_subquery”，“假”）
从t_order_or或1中选择/*+GATHER_PLAN_STATISTICS OPT_PARAM（“_unnest_subquery”，“false”）*/*，其中
cr_fk = “EUR”，or_totalamount =
（从t_order_or或2中选择max（or_totalamount），其中or2.cr_fk = or1.cr_fk）;
我们可以避免子查询取消嵌套和其他查询转换发生在语句级别，使用NO_QUERY_TRANSFORMATION提示
从t_order_or或1中选择/*+GATHER_PLAN_STATISTICS NO_QUERY_TRANSFORMATION*/*，其中
cr_fk= 'EUR'，or_totalamount=
（从t_order_or或2中选择max（or_totalamount）其中or2.cr_fk = or1.cr_fk）;

术语
查询语句中的任何子查询块都可以称为子查询;但是，我们将术语子查询用于出现在“位置”、“选择”和“有”子句中的子查询块。一些 Oracle 文档使用术语“嵌套子查询”来表示我们所说的子查询。出现在 FROM 子句中的子查询块称为视图或派生表。
有许多方法可以对子查询进行分类。主要分类来自 SQL 语句中使用子查询的方式。WHERE 子句子句属于以下类型之一：单行、存在、不存在、任何或全部。单行子查询最多必须返回一行，而其他类型的子查询可以返回零行或多行。ANY 和 ALL 子查询与关系比较运算符一起使用：=、>、>=、<、<=和<>。在 SQL 中，集合运算符 IN 用作 =ANY 的简写，集合运算符 NOT IN 用作 <>ALL 的简写。
查询 A 显示了相关 EXISTS 子查询
A.SELECT
C.cust_last_name的示例，C.country_id
来自客户 C
（存在位置）（从销售 S
中选择 1
，其中 S.quantity_sold > 1000，
S.cust_id = C.cust_id）;
如果子查询中出现的列来自子查询未定义的表，则该列称为相关列。A 中的子查询是相关的，因为它指的是一个相关的列，C.cust_id，它来自客户，一个未由子查询定义的表。谓词S.cust_id = C.cust_id，称为相关条件或相关谓词。
考虑查询 B，它包含一个不相关的 ANY 子查询。请注意，查询 B 和 A 在语义上是等效的。
B.
选择C.cust_last_name，C.country_id
客户C
，其中C.cust_id=任何（
从销售S
中选择S.cust_id，其中S.quantity_sold >1000）;
B 中的子查询是不相关的，因为它不引用相关列。B 中的“C.cust_id = 任何S.cust_id”称为连接条件。
子查询求值
“不存在”子查询的计算结果为 TRUE（如果它不返回任何行）。ANY/ALL 子查询返回一组值，如果满足，则包含 ANY/ALL 子查询的谓词的计算结果将为 TRUE。例如，在查询 B 的 ANY 子查询的连接条件中，至少有一个S.cust_id值必须与 C.cust_id 匹配，
请注意，在 Oracle 中，非未嵌套的 ANY 和 ALL 子查询将分别转换为相关的“存在”和“不存在”子查询。
当相关子查询未取消嵌套时，对于外部表的每一行，将多次计算该子查询，以替换相关列的值（例如，A 中的customer.cust_id）。因此，子查询内的表访问和联接是重复执行的，每次调用和联接顺序都无法探索涉及子查询表和外部查询表。这种类型的评估也会抑制并行化。
XA 显示查询 A 的执行计划。此处的子查询取消嵌套已被禁用。请注意，非无嵌套子查询筛选器的文本显示在计划底部的谓词转储中。如执行计划所示，对于 CUSTOMERS 表的每一外行，子查询将被计算多次（即 50K）。
。
执行计划
----------------------------------------------------------
计划哈希值：1012411109

----------------------------------------------------------------------------------------------------------------------
|编号 |操作|名称|行|字节|成本（%CPU）|时间|启动|普斯托普|
----------------------------------------------------------------------------------------------------------------------
|0 |选择对账单| |1 |18 |2428K （1）|08：05：41 | | |
|*1|筛选| | | | | | | |
|2 |表访问完整|客户|55500 |97.5万|405 （1）|00：00：05 | | |
|3|分区范围所有| |1 |8 |54 （0）|00：00：01 |1 |28 |
|*4|按本地索引 ROWID 访问表|销售|1 |8 |54 （0）|00：00：01 |1 |28 |
|5 |位图转换为 rowids | | | | | | | |
|*6|位图索引单值|SALES_CUST_BIX | | | | |1 |28 |

----------------------------------------------------------------------------------------------------------------------谓词信息（由操作 ID 标识）：
---------------------------------------------------

1 - 筛选器（存在（从“SALES”S“中选择 /*+ NO_UNNEST */ 0，其中”S“。CUST_ID“=：B1 和
”S“。QUANTITY_SOLD“>1000））
4 - 过滤器（ ”S“。”QUANTITY_SOLD“>1000）
6 - 访问（”S“）。CUST_ID“=：B1）
存在，任何子查询
半连接通常用于取消嵌套存在和任何子查询。但是，在某些情况下，重复行不相关，内部联接也可用于取消嵌套 EXISTS 和任何子查询。在这里，我们通过以下非标准语法表示半连接：T1.x S= T2.y，其中 T1 是左表，T2 是半连接的右表。半联接的语义如下：一旦 T1.x 找到与任何 T2.y 值匹配项而不搜索进一步的匹配项，就会返回一行 T1。
考虑前面显示的查询 A.取消 A 中子查询的嵌套将生成查询 C，其中子查询的主体已合并到外部查询中。在这里，相关条件已转换为连接谓词;客户和销售在半连接中分别成为左表和右表。
C.
选择C.cust_last_name，
C.country_id客户C，销售S
，其中S.quantity_sold >1000和
C.cust_id S = S.cust_id;
C的执行计划如下图所示为XC。请注意计划XC和计划XA的成本之间的差异;回想一下，XA 是通过禁用取消嵌套而生成的。显然，具有非嵌套（XC）的计划更加最佳;成本已从2428K降至896。（查询 B 还生成与 XC 相同的计划。
新浪网.
执行计划
----------------------------------------------------------
计划哈希值：3983182601

--------------------------------------------------------------------------------------------------
|编号 |操作|名称|行|字节|成本（%CPU）|时间|启动|普斯托普|

--------------------------------------------------------------------------------------------------|0 |选择对账单| |2 |52 |896 （2）|00：00：11 | | |
|*1|哈希加入半| |2 |52 |896 （2）|00：00：11 | | |
|2 |分区范围全部| |1 |8 |490 （2）|00：00：06 |1 |28 |
|*3|表访问完整|销售|1 |8 |490 （2）|00：00：06 |1 |28 |
|4 |表访问完整|客户|55500 |97.5万|405 （1）|00：00：05 | | |

--------------------------------------------------------------------------------------------------
谓词信息（由操作 ID 标识）：
---------------------------------------------------

1 - 访问（“S”）。CUST_ID“=”C“。CUST_ID“）
3 - 过滤器（”S“。QUANTITY_SOLD“>1000）
现在考虑查询 D，它包含一个不相关的 ANY 子查询，该子查询具有两个表。
D.
选择C.cust_last_name，C.country_id
客户C
，其中C.cust_id=任何（
从销售S中选择S.cust_id，产品P
，其中P.prod_id=S.prod_id，
P.prod_list_price >105）;
D 中的子查询可以通过使用半连接来取消嵌套;但是，子查询、销售和产品中的表的内部连接必须在执行半连接之前进行。因此，需要生成内联视图才能强制实施联接顺序。查询 E 显示了 D 的不嵌套变换。在这里，子查询被去相关并转换为内联视图，该视图成为半连接中的右表;并且相关谓词将转换为连接谓词。
E.
选择C.cust_last_name，
C.country_id来自客户C，
（
选择S.cust_id作为销售S，产品P
s_cust_id其中P.prod_id= S.prod_id和P.prod_list_price >105）大众
，其中C.cust_id S = VW.s_cust_id;
XE 显示 E 的执行计划。它有一个名为 VW_SQ_1 的优化器生成的内联视图。在三种可用的连接方法（即嵌套循环、哈希和排序合并）中，优化程序选择哈希方法来执行半连接。
XE
执行计划
----------------------------------------------------------
计划哈希值：4030918423

------------------------------------------------------------------------------------------------------------
|编号 |操作|名称|行|字节|温度|成本（%CPU）|时间|启动|普斯托普|
------------------------------------------------------------------------------------------------------------
|0 |选择对账单| |7059 |21.3万| |1987 （1）|00：00：24 | | |
|*1|哈希加入半| |7059 |21.3万|163.2万|1987 （1）|00：00：24 | | |
|2 |表访问完整|客户|55500 |97.5万| |405 （1）|00：00：05 | | |
|3|查看|VW_NSO_1 |84.9万|1000万| |493 （2）|00：00：06 | | |
|*4|哈希加入| |84.9万|1400万| |493 （2）|00：00：06 | | |
|*5|表访问完整|产品|67 |603 | |3 （0）|00：00：01 | | |
|6 |分区范围全部| |91.8万|807.5万| |486 （2）|00：00：06 |1 |28 |
|7 |表访问完整|销售|91.8万|807.5万| |486 （2）|00：00：06 |1 |28 |
------------------------------------------------------------------------------------------------------------

谓词信息（由操作 ID 标识）：
---------------------------------------------------

1 - 访问（“C”。”CUST_ID“=”CUST_ID“）
4 - 访问（”P“。”PROD_ID“=”S“。PROD_ID“）
5 - 过滤器（”P“。PROD_LIST_PRICE“>105）

不存在子查询
反连接始终用于取消嵌套“不存在”和“所有子查询”。我们使用以下非标准语法表示反连接：T1.x A= T2.y，其中 T1 是左表，T2 是反连接的正确表。反联接的语义如下：一旦 T1.x 找到与任何 T2.y 值的匹配项，一行 T1 就会被拒绝。仅当 T1.x 与 T2.y 的任何值都不匹配时，才会返回一行 T1。
考虑查询 F，它具有一个不包含两个表的“不存在”子查询。
F.
选择C.cust_last_name，C.country_id
不存在的客户C
（从销售S中选择1
，产品P
，其中P.prod_id= S.prod_id，
P.prod_min_price >90和
S.cust_id = C.cust_id）;
F 中的子查询可以通过使用反连接来取消嵌套;但是，子查询、销售和产品中的表的内部连接必须在执行反连接之前进行。将生成一个内联视图，以便强制实施联接顺序。这种不嵌套生成查询 G;在这里，内联视图成为正确的反联接表。
G.
选择C.cust_last_name，
客户C C.country_id，
（
选择S.cust_id作为销售S，产品P
s_cust_id其中P.prod_id= S.prod_id和
P.prod_min_price >90）大众
，其中C.cust_id A = VW.s_cust_id;
G 的执行计划显示为 XG。在三种连接方法（即嵌套循环、哈希和排序合并）中，优化程序选择哈希方法来执行反连接。
新浪网.
执行计划
----------------------------------------------------------
计划哈希值：1110507518

------------------------------------------------------------------------------------------------------------
|编号 |操作|名称|行|字节|温度|成本（%CPU）|时间|启动|普斯托普|
------------------------------------------------------------------------------------------------------------
|0 |选择对账单| |55500 |168万| |2000 （1）|00：00：24 | | |
|*1|哈希加入反| |55500 |168万|163.2万|2000 （1）|00：00：24 | | |
|2 |表访问完整|客户|55500 |97.5万| |405 （1）|00：00：05 | | |
|3|查看|VW_SQ_1 |85.9万|1000万| |493 （2）|00：00：06 | | |
|*4|哈希加入| |85.9万|1400万| |493 （2）|00：00：06 | | |
|*5|表访问完整|产品|67 |603 | |3 （0）|00：00：01 | | |
|6 |分区范围全部| |91.8万|807.5万| |486 （2）|00：00：06 |1 |28 |
|7 |表访问完整|销售|91.8万|807.5万| |486 （2）|00：00：06 |1 |28 |

------------------------------------------------------------------------------------------------------------谓词信息（由操作 ID 标识）：
---------------------------------------------------

1 - 访问（“ITEM_1”=“C”。”CUST_ID“）
4 - 访问（”P“。PROD_ID“=”S“。PROD_ID“）
5 - 过滤器（”P“。PROD_MIN_PRICE“>90）
单行聚合子查询
考虑包含聚合的相关单行子查询的查询 H。
H.
选择C.cust_last_name，
从c C.cust_income_level美国供应商 C
WHERE C.cust_credit_limit < （从销售 S
中选择总和（S.amount_sold），
其中 S.cust_id = C.cust_id）;
对相等相关性谓词的多个值执行聚合类似于对本地列执行聚合和分组依据，然后在分组依据列上将视图与外部表联接。H 中的子查询通过去关联并将其转换为分组依据视图（内部与外部表 customer 连接）来取消嵌套;在这里，相关条件和连接条件都已转换为连接谓词。转换后的查询显示为 I.I.SELECT

C.cust_last_name，C.cust_income_level
来自客户 C，
选择总和（S.amount_sold）作为sum_amt，S.cust_id
来自销售 S
组 S.cust_id）大众，
其中 C.cust_credit_limit < VW.sum_amt 和
C.cust_id = VW.cust_id;
XI 显示了 I.XI
的执行计划。
执行计划
----------------------------------------------------------
计划哈希值：1157114491

----------------------------------------------------------------------------------------------------
|编号 |操作|名称|行|字节|成本（%CPU）|时间|启动|普斯托普|
----------------------------------------------------------------------------------------------------
|0 |选择对账单| |353 |22592 |921 （4）|00：00：12 | | |
|*1|哈希加入| |353 |22592 |921 （4）|00：00：12 | | |
|2 |查看| |7059 |17.9万|515 （7）|00：00：07 | | |
|3|按| |进行哈希分组7059 |70590 |515 （7）|00：00：07 | | |
|4 |分区范围全部| |91.8万|897.3万|489 （2）|00：00：06 |1 |28 |
|5 |表访问完整|销售|91.8万|897.3万|489 （2）|00：00：06 |1 |28 |
|6 |表访问完整|客户|55500 |205.9万|405 （1）|00：00：05 | | |

----------------------------------------------------------------------------------------------------
谓词信息（由操作 ID 标识）：
---------------------------------------------------

1 - 访问（“C”）。CUST_ID“=”大众“。CUST_ID“）
过滤器（”C“。CUST_CREDIT_LIMIT“<”大众“。SUM_AMT“）
取消
嵌套的有效性每个子查询在可以取消嵌套之前都要经过一组有效性检查。优化程序决定取消嵌套或不取消嵌套子查询可以通过指定适当的提示来覆盖，但有效性要求不能被覆盖，因为在这种情况下取消嵌套将不能保证语义上等效的查询。
在下文中，我们列举了一些重要的检查，这些检查当前使子查询取消嵌套无效。请注意，此检查列表绝非详尽无遗。

与非父级相关的子查询;例如，子查询 SQ3 由 SQ2（SQ3 的父级）包含，而 SQ2 又由 SQ1 包含，SQ3 与 SQ1 中定义的表相关。
分组依据子查询是相关的;在这种情况下，取消嵌套意味着在分组后加入。更改两个操作的给定顺序可能并不总是合法的。
连接或关联条件的格式不正确（例如，它们在谓词的两侧包含局部和相关列的混合），并且子查询需要内联视图生成，因为此类谓词不允许分离出视图列和外部表列。
对于分离子查询，连接或关联条件中的外列不相同。
使用视图合并转换，Oracle 可以合并在取消嵌套期间生成的分组依据或不同的内联视图，因此，即使需要视图，执行计划也可能不会显示任何视图。

摘要
在这些帖子中，我们试图通过显示简单的示例查询来说明不同类型的子查询的解音背后的基本思想。Oracle 可以处理更复杂的查询 - 在一个或多个级别具有多个子查询的查询语句、多个表、包含不等式谓词和表达式的相关和连接条件、包含集合运算符的子查询、具有分组依据和 COUNT 聚合函数的子查询、在其连接条件中包含可为 null 列的所有子查询，以及析取中的子查询。
如果取消嵌套子查询不需要生成内联视图，则子查询始终是未嵌套的，因为这种取消嵌套通过允许更多的连接方法和连接订单来提供更有效的执行计划。如果子查询的相关谓词中的局部列（例如，A 中的S.cust_id）在其上具有索引，则子查询计算类似于执行基于索引的嵌套循环连接;因此，在某些情况下，不取消嵌套可能比取消嵌套更有效，因为取消嵌套会生成内联视图，并且仅允许视图与外部表的排序合并和哈希联接。因此，在 Oracle 中，生成内联视图的子查询解嵌套是根据基于成本的查询转换框架下的成本完成的。

参考至：https://blogs.oracle.com/optimizer/entry/optimizer_transformations_subquery_unesting_part_1

https://blogs.oracle.com/optimizer/entry/optimizer_transformations_subquery_unesting_part_2 http://www.online-database.eu/index.php/sql-tuning/177-subquery-unnesting

http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries008.htm#SQLRF52358

本文原创，转载请注明出处、作者

如有错误，欢迎指正