[20211214]18c標量子查詢unnest.txt
[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20200325]慎用標量子查詢.txt
- MYSQL學習筆記25: 多表查詢(子查詢)[標量子查詢,列子查詢]MySql筆記
- MYSQL count標量子查詢改left joinMySql
- [20211220]關於標量子查詢問題.txt
- Oracle常用抓取SQL-標量子查詢等OracleSQL
- mysql 標量子查詢和現金盤程式製作非法子查詢MySql
- [20180626]函式與標量子查詢14.txt函式
- [20180625]函式與標量子查詢13(補充)函式
- [20180612]函式與標量子查詢10.txt函式
- [20180611]函式與標量子查詢9.txt函式
- [20180607]函式與標量子查詢8.txt函式
- [20180602]函式與標量子查詢4.txt函式
- [20180602]函式與標量子查詢3.txt函式
- 20180601]函式與標量子查詢2.txt函式
- 影響Oracle標量子查詢效能的三個因素Oracle
- 【TUNE_ORACLE】列出有標量子查詢的SQL參考OracleSQL
- GreatSQL 最佳化技巧：將 MINUS 改寫為標量子查詢SQL
- [20210202]計算標量子查詢快取數量2.txt快取
- [20210201]19c計算標量子查詢快取數量.txt快取
- 遊標查詢
- 如何查詢上標
- 391、Java框架46 -【Hibernate - 查詢HQL、查詢Criteria、查詢標準SQL】 2020.10.19Java框架SQL
- SpringBoot整合Elasticsearch遊標查詢（scroll）Spring BootElasticsearch
- 二分查詢—包括查詢第一個目標元素和最後一個目標元素
- 流式查詢1. mybatis的遊標Cursor，分頁大資料查詢MyBatis大資料
- js查詢HTMLCollection物件中的下標JSHTML物件
- 裝置支援多箱號查詢，mybatis標籤MyBatis
- pytest標記：查詢測試策略、標記測試函式函式
- SQL查詢的：子查詢和多表查詢SQL
- mysql-分組查詢-子查詢-連線查詢-組合查詢MySql
- mysql查詢語句陣列下標擷取MySql陣列
- mybatis lambdaQuery 查詢條件導致空指標MyBatis指標
- 衛生紙國家標準查詢 All In One
- 解析JDBC使用查詢MySQL【非流式、流式、遊標】JDBCMySql
- 複雜查詢—子查詢
- 查詢——二分查詢
- [Golang實戰] 查詢docker search name的映象標籤GolangDocker
- Elasticsearch 按照標籤匹配個數優先排序查詢Elasticsearch排序