Oracle 查询转换-04 Subquery Unnesting

在子查询展开中，优化器将嵌套查询转换为等效的连接语句，然后优化连接。此转换使优化器能够在访问路径、连接方法和连接顺序选择期间考虑子查询表。优化器只有在保证 子查询展开和原始语句返回一样的结果 ，并且子查询不包含诸如AVG之类的聚合函数的情况下才能执行此转换。

查看Subquery Unnesting相关参数

select a.ksppinm name, b.ksppstvl value, a.ksppdesc description

  from x$ksppi a, x$ksppcv b

 where a.indx = b.indx

   and a.ksppinm in

       ('_unnest_subquery', '_optimizer_unnest_all_subqueries');

通过10053查看Subquery Unnesting是如何改写SQL的：

SQL> conn sh/sh@cjcpdb

SQL> alter session set tracefile_identifier='10053G';

SQL> alter session set events '10053 trace name context forever ,level 1';

SQL> SELECT * FROM sales WHERE cust_id IN (SELECT cust_id FROM customers);

SQL> alter session set events '10053 trace name context off';

[oracle@cjcos01 trace]$ ls *10053*

cjcdb_ora_9410_10053G.trc  cjcdb_ora_9410_10053G.trm

[oracle@cjcos01 trace]$ vim cjcdb_ora_9410_10053G.trc 

搜索关键字：Final query after transformations

SELECT "SALES"."PROD_ID"       "PROD_ID",

       "SALES"."CUST_ID"       "CUST_ID",

       "SALES"."TIME_ID"       "TIME_ID",

       "SALES"."CHANNEL_ID"    "CHANNEL_ID",

       "SALES"."PROMO_ID"      "PROMO_ID",

       "SALES"."QUANTITY_SOLD" "QUANTITY_SOLD",

       "SALES"."AMOUNT_SOLD"   "AMOUNT_SOLD"

  FROM "SH"."CUSTOMERS" "CUSTOMERS", "SH"."SALES" "SALES"

 WHERE "SALES"."CUST_ID" = "CUSTOMERS"."CUST_ID";

查看执行计划：

SQL> set line 150

SQL> set autotrace traceonly

SQL> SELECT * FROM sales WHERE cust_id IN (SELECT cust_id FROM customers); 

禁用Subquery Unnesting，查看执行计划

SQL> SELECT * FROM sales WHERE cust_id IN (SELECT /*+ no_unnest */ cust_id FROM customers);