[20211214]18c标量子查询unnest.txt

来源：这里教程网时间：2026-03-03 17:18:40 作者：

[20211214]18c标量子查询unnest.txt --//18c支持标量子查询unnest，也就是改写为连接模式。通过例子说明。 --//先测试11g下的情况: 1.环境： SCOTT@book> @ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SCOTT@book> @sl all alter session set statistics_level = all; Session altered. SCOTT@book> select deptno,dname,(select max(sal) from emp where emp.deptno=dept.deptno) N10 from dept; DEPTNO DNAME N10 ---------- -------------- --------------------- 10 ACCOUNTING 5000 20 RESEARCH 3000 30 SALES 2850 40 OPERATIONS SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 54gxjxs8kx3w3, child number 1 ------------------------------------- select deptno,dname,(select max(sal) from emp where emp.deptno=dept.deptno) N10 from dept Plan hash value: 2018188441 ------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | ------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 4 |00:00:00.01 | 7 | 0 | | 1 | SORT AGGREGATE | | 4 | 1 | 7 | | | 4 |00:00:00.01 | 24 | 20 | |* 2 | TABLE ACCESS FULL| EMP | 4 | 66M| 445M| 216M (1)|722:44:36 | 14 |00:00:00.01 | 24 | 20 | | 3 | TABLE ACCESS FULL | DEPT | 1 | 4 | 52 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 7 | 0 | ------------------------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$2 2 - SEL$2 / EMP@SEL$2 3 - SEL$1 / DEPT@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("EMP"."DEPTNO"=:B1) --//你可以发现id=2,循环4次（有4个部门），如果开发这样写，如果循环次数很多会很慢的,逻辑读很很高。 2.18c呢？ TTT@xxxxxx/orcl> @ sl all alter session set statistics_level = all; Session altered. TTT@xxxxxx/orcl> select deptno,dname,(select max(sal) from emp where emp.deptno=dept.deptno) N10 from dept; DEPTNO DNAME N10 ---------- ---------------------------- --------------------- 10 ACCOUNTING 5000 20 RESEARCH 3000 30 SALES 2850 40 OPERATIONS TTT@xxxxxx/orcl> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 54gxjxs8kx3w3, child number 1 ------------------------------------- select deptno,dname,(select max(sal) from emp where emp.deptno=dept.deptno) N10 from dept Plan hash value: 2834279049 -------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 7 (100)| | 4 |00:00:00.01 | 10 | | | | | 1 | MERGE JOIN OUTER | | 1 | 4 | 156 | 7 (29)| 00:00:01 | 4 |00:00:00.01 | 10 | | | | | 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 4 | 52 | 2 (0)| 00:00:01 | 4 |00:00:00.01 | 4 | | | | | 3 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | | 1 (0)| 00:00:01 | 4 |00:00:00.01 | 2 | | | | |* 4 | SORT JOIN | | 4 | 3 | 78 | 5 (40)| 00:00:01 | 3 |00:00:00.01 | 6 | 2048 | 2048 | 2048 (0)| | 5 | VIEW | VW_SSQ_1 | 1 | 3 | 78 | 4 (25)| 00:00:01 | 3 |00:00:00.01 | 6 | | | | | 6 | HASH GROUP BY | | 1 | 3 | 21 | 4 (25)| 00:00:01 | 3 |00:00:00.01 | 6 | 1186K| 1186K| 1294K (0)| | 7 | TABLE ACCESS FULL | EMP | 1 | 14 | 98 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 6 | | | | -------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$C772B8D1 2 - SEL$C772B8D1 / DEPT@SEL$1 3 - SEL$C772B8D1 / DEPT@SEL$1 5 - SEL$683B0107 / VW_SSQ_1@SEL$7511BFD2 6 - SEL$683B0107 7 - SEL$683B0107 / EMP@SEL$2 Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("ITEM_1"="DEPT"."DEPTNO") filter("ITEM_1"="DEPT"."DEPTNO") --//你可以发现查询发生了改写，没有出现标量子查询。 --//select /*+ USE_HASH("VW_SSQ_1"@"SEL$7511BFD2") */deptno,dname,(select max(sal) from emp where emp.deptno=dept.deptno) N10 from dept; --//使用以上提示可以使用hash连接： Plan hash value: 2653503239 ------------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 7 (100)| | 4 |00:00:00.01 | 12 | | | | |* 1 | HASH JOIN OUTER | | 1 | 4 | 156 | 7 (15)| 00:00:01 | 4 |00:00:00.01 | 12 | 1695K| 1695K| 1087K (0)| | 2 | TABLE ACCESS FULL | DEPT | 1 | 4 | 52 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 6 | | | | | 3 | VIEW | VW_SSQ_1 | 1 | 3 | 78 | 4 (25)| 00:00:01 | 3 |00:00:00.01 | 6 | | | | | 4 | HASH GROUP BY | | 1 | 3 | 21 | 4 (25)| 00:00:01 | 3 |00:00:00.01 | 6 | 1186K| 1186K| 806K (0)| | 5 | TABLE ACCESS FULL| EMP | 1 | 14 | 98 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 6 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------------ TTT@xxxxxx/orcl> @expandz 54gxjxs8kx3w3 18 SELECT "A1"."DEPTNO" "DEPTNO","A1"."DNAME" "DNAME", (SELECT MAX("A2"."SAL") "MAX(SAL)" FROM "TTT"."EMP" "A2" WHERE "A2"."DEPTNO"="A1"."DEPTNO") "N10" FROM "TTT"."DEPT" "A1" PL/SQL procedure successfully completed. --//格式化如下： SELECT "A1"."DEPTNO" "DEPTNO","A1"."DNAME" "DNAME", ( SELECT MAX("A2"."SAL") "MAX(SAL)" FROM "TTT"."EMP" "A2" WHERE "A2"."DEPTNO" = "A1"."DEPTNO" ) "N10" FROM "TTT"."DEPT" "A1" --//展开并不能发现改写。 TTT@xxxxxx/orcl> @10053x 54gxjxs8kx3w3 1 PL/SQL procedure successfully completed. TTT@xxxxxx/orcl> @ ttt tracefile_identifier = /u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace/orclcdb_ora_7746_a54gxjxs8kx3w3.trc Final query after transformations:******* UNPARSED QUERY IS ******* SELECT "DEPT"."DEPTNO" "DEPTNO","DEPT"."DNAME" "DNAME","VW_SSQ_1"."MAX(SAL)" "N10" FROM (SELECT MAX("EMP"."SAL") "MAX(SAL)","EMP"."DEPTNO" "ITEM_1" FROM "TTT"."EMP" "EMP" GROUP BY "EMP"."DEPTNO") "VW_SSQ_1","TTT"."DEPT" "DEPT" WHERE "VW_SSQ_1"."ITEM_1"(+)="DEPT"."DEPTNO" kkoqbc: optimizing query block SEL$683B0107 (#2) --//格式化如下： SELECT "DEPT"."DEPTNO" "DEPTNO","DEPT"."DNAME" "DNAME","VW_SSQ_1"."MAX(SAL)" "N10" FROM ( SELECT MAX("EMP"."SAL") "MAX(SAL)","EMP"."DEPTNO" "ITEM_1" FROM "TTT"."EMP" "EMP" GROUP BY "EMP"."DEPTNO") "VW_SSQ_1","TTT"."DEPT" "DEPT" WHERE "VW_SSQ_1"."ITEM_1"(+) = "DEPT"."DEPTNO" --//你可以发现改写如上。看看什么参数控制这样的行为： SYS@xxxxxx/orclcdb> @ hide _optimizer_unnest_scalar_sq NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES_MODI ISSYS_MODIFIABLE ---------------------------------------- ------------------------------------------------------------------ ---------------------- ---------------------- ---------------------- ---------- ------------------ _optimizer_unnest_scalar_sq enables unnesting of of scalar subquery TRUE TRUE TRUE TRUE IMMEDIATE --//应该是_optimizer_unnest_scalar_sq隐含参数。 TTT@xxxxxx/orcl> select /*+ OPT_PARAM('_optimizer_unnest_scalar_sq', 'false') */ deptno,dname,(select max(sal) from emp where emp.deptno=dept.deptno) N10 from dept; DEPTNO DNAME N10 ---------- ---------------------------- --------------------- 10 ACCOUNTING 5000 20 RESEARCH 3000 30 SALES 2850 40 OPERATIONS TTT@xxxxxx/orcl> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID c8tfsck6a46z1, child number 0 ------------------------------------- select /*+ OPT_PARAM('_optimizer_unnest_scalar_sq', 'false') */ deptno,dname,(select max(sal) from emp where emp.deptno=dept.deptno) N10 from dept Plan hash value: 2018188441 --------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 9 (100)| | 4 |00:00:00.01 | 7 | | 1 | SORT AGGREGATE | | 4 | 1 | 7 | | | 4 |00:00:00.01 | 24 | |* 2 | TABLE ACCESS FULL| EMP | 4 | 5 | 35 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 24 | | 3 | TABLE ACCESS FULL | DEPT | 1 | 4 | 52 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 7 | --------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$2 2 - SEL$2 / EMP@SEL$2 3 - SEL$1 / DEPT@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("EMP"."DEPTNO"=:B1) --//还原原来11g的执行计划。 3.附上执行的脚本： $ cat 10053x.sql execute dbms_sqldiag.dump_trace(p_sql_id=>'&1',p_child_number=>&2,p_component=>'Compiler',p_file_id=>'a'||'&&1'); $ cat tpt/expandz.sql set long 40000 set serveroutput on column arg new_value arg set term off select decode(&2,11,'sql2','utility') arg from dual; set term on prompt declare l_sqltext clob := null; l_result clob := null; begin select sql_fulltext into l_sqltext from v$sqlarea where sql_id='&&1'; -- dbms_output.put_line(l_sqltext); -- dbms_sql2.expand_sql_text(l_sqltext,l_result); dbms_&arg..expand_sql_text(l_sqltext,l_result); dbms_output.put_line(l_result); end; / set serveroutput off