[20211214]18c標量子查詢unnest.txt

lfree發表於2021-12-14

[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


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2847556/，如需轉載，請註明出處，否則將追究法律責任。

相關文章