使用with子句優化程式碼中重複查詢
/*
好處： 1. 效能更好，一份複製（類似SYS_TMP...)，多份使用。
2. 結構清晰，預先定義。
3. 程式碼修改不必修改多處。
請注意觀察語句1和語句2執行計劃的差異，尤其是語句2的SYS_TEMP_0FD9D6605_3B91BA4這些奇怪的命名。其實這就表示是
複製在記憶體中的資料，一次複製，多次使用。
*/
drop table t_with;
CREATE TABLE T_WITH AS SELECT ROWNUM ID, A.* FROM DBA_SOURCE A WHERE ROWNUM < 100001;
SET autotrace traceonly
Set linesize 1000
--語句1
SELECT ID, NAME FROM T_WITH
WHERE ID IN
(SELECT MAX(ID) FROM T_WITH
UNION ALL
SELECT MIN(ID) FROM T_WITH
UNION ALL
SELECT TRUNC(AVG(ID)) FROM T_WITH);
執行計劃
----------------------------------------------------------------------------------
Plan hash value: 647530712
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 129 | 1382 (1)| 00:00:17 |
|* 1 | HASH JOIN | | 3 | 129 | 1382 (1)| 00:00:17 |
| 2 | VIEW | VW_NSO_1 | 3 | 39 | 1035 (1)| 00:00:13 |
| 3 | HASH UNIQUE | | 3 | 39 | 1035 (67)| 00:00:13 |
| 4 | UNION-ALL | | | | | |
| 5 | SORT AGGREGATE | | 1 | 13 | | |
| 6 | TABLE ACCESS FULL| T_WITH | 91060 | 1156K| 345 (1)| 00:00:05 |
| 7 | SORT AGGREGATE | | 1 | 13 | | |
| 8 | TABLE ACCESS FULL| T_WITH | 91060 | 1156K| 345 (1)| 00:00:05 |
| 9 | SORT AGGREGATE | | 1 | 13 | | |
| 10 | TABLE ACCESS FULL| T_WITH | 91060 | 1156K| 345 (1)| 00:00:05 |
| 11 | TABLE ACCESS FULL | T_WITH | 91060 | 2667K| 345 (1)| 00:00:05 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"="MAX(ID)")
Note
-----
- dynamic sampling used for this statement (level=2)
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
4969 consistent gets
0 physical reads
0 redo size
558 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
--語句2
WITH AGG AS (SELECT MAX(ID) MAX, MIN(ID) MIN, TRUNC(AVG(ID)) AVG FROM T_WITH)
SELECT ID, NAME FROM T_WITH
WHERE ID IN
( SELECT MAX FROM AGG UNION ALL SELECT MIN FROM AGG UNION ALL SELECT AVG FROM AGG);
執行計劃
--------------------------------------------------------------------------------------------------------
Plan hash value: 3705751949
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 129 | 697 (1)| 00:00:09 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6605_3B91BA4 | | | | |
| 3 | SORT AGGREGATE | | 1 | 13 | | |
| 4 | TABLE ACCESS FULL | T_WITH | 91060 | 1156K| 345 (1)| 00:00:05 |
|* 5 | HASH JOIN | | 3 | 129 | 352 (1)| 00:00:05 |
| 6 | VIEW | VW_NSO_1 | 3 | 39 | 6 (0)| 00:00:01 |
| 7 | HASH UNIQUE | | 3 | 39 | 6 (67)| 00:00:01 |
| 8 | UNION-ALL | | | | | |
| 9 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6605_3B91BA4 | 1 | 13 | 2 (0)| 00:00:01 |
| 11 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6605_3B91BA4 | 1 | 13 | 2 (0)| 00:00:01 |
| 13 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6605_3B91BA4 | 1 | 13 | 2 (0)| 00:00:01 |
| 15 | TABLE ACCESS FULL | T_WITH | 91060 | 2667K| 345 (1)| 00:00:05 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("ID"="MAX")
Note
-----
- dynamic sampling used for this statement (level=2)
統計資訊
----------------------------------------------------------
2 recursive calls
8 db block gets
2496 consistent gets
1 physical reads
600 redo size
558 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
好處： 1. 效能更好，一份複製（類似SYS_TMP...)，多份使用。
2. 結構清晰，預先定義。
3. 程式碼修改不必修改多處。
請注意觀察語句1和語句2執行計劃的差異，尤其是語句2的SYS_TEMP_0FD9D6605_3B91BA4這些奇怪的命名。其實這就表示是
複製在記憶體中的資料，一次複製，多次使用。
*/
drop table t_with;
CREATE TABLE T_WITH AS SELECT ROWNUM ID, A.* FROM DBA_SOURCE A WHERE ROWNUM < 100001;
SET autotrace traceonly
Set linesize 1000
--語句1
SELECT ID, NAME FROM T_WITH
WHERE ID IN
(SELECT MAX(ID) FROM T_WITH
UNION ALL
SELECT MIN(ID) FROM T_WITH
UNION ALL
SELECT TRUNC(AVG(ID)) FROM T_WITH);
執行計劃
----------------------------------------------------------------------------------
Plan hash value: 647530712
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 129 | 1382 (1)| 00:00:17 |
|* 1 | HASH JOIN | | 3 | 129 | 1382 (1)| 00:00:17 |
| 2 | VIEW | VW_NSO_1 | 3 | 39 | 1035 (1)| 00:00:13 |
| 3 | HASH UNIQUE | | 3 | 39 | 1035 (67)| 00:00:13 |
| 4 | UNION-ALL | | | | | |
| 5 | SORT AGGREGATE | | 1 | 13 | | |
| 6 | TABLE ACCESS FULL| T_WITH | 91060 | 1156K| 345 (1)| 00:00:05 |
| 7 | SORT AGGREGATE | | 1 | 13 | | |
| 8 | TABLE ACCESS FULL| T_WITH | 91060 | 1156K| 345 (1)| 00:00:05 |
| 9 | SORT AGGREGATE | | 1 | 13 | | |
| 10 | TABLE ACCESS FULL| T_WITH | 91060 | 1156K| 345 (1)| 00:00:05 |
| 11 | TABLE ACCESS FULL | T_WITH | 91060 | 2667K| 345 (1)| 00:00:05 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"="MAX(ID)")
Note
-----
- dynamic sampling used for this statement (level=2)
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
4969 consistent gets
0 physical reads
0 redo size
558 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
--語句2
WITH AGG AS (SELECT MAX(ID) MAX, MIN(ID) MIN, TRUNC(AVG(ID)) AVG FROM T_WITH)
SELECT ID, NAME FROM T_WITH
WHERE ID IN
( SELECT MAX FROM AGG UNION ALL SELECT MIN FROM AGG UNION ALL SELECT AVG FROM AGG);
執行計劃
--------------------------------------------------------------------------------------------------------
Plan hash value: 3705751949
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 129 | 697 (1)| 00:00:09 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6605_3B91BA4 | | | | |
| 3 | SORT AGGREGATE | | 1 | 13 | | |
| 4 | TABLE ACCESS FULL | T_WITH | 91060 | 1156K| 345 (1)| 00:00:05 |
|* 5 | HASH JOIN | | 3 | 129 | 352 (1)| 00:00:05 |
| 6 | VIEW | VW_NSO_1 | 3 | 39 | 6 (0)| 00:00:01 |
| 7 | HASH UNIQUE | | 3 | 39 | 6 (67)| 00:00:01 |
| 8 | UNION-ALL | | | | | |
| 9 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6605_3B91BA4 | 1 | 13 | 2 (0)| 00:00:01 |
| 11 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6605_3B91BA4 | 1 | 13 | 2 (0)| 00:00:01 |
| 13 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6605_3B91BA4 | 1 | 13 | 2 (0)| 00:00:01 |
| 15 | TABLE ACCESS FULL | T_WITH | 91060 | 2667K| 345 (1)| 00:00:05 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("ID"="MAX")
Note
-----
- dynamic sampling used for this statement (level=2)
統計資訊
----------------------------------------------------------
2 recursive calls
8 db block gets
2496 consistent gets
1 physical reads
600 redo size
558 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29135257/viewspace-2093848/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 查詢中讓優化器使用複合索引優化索引
- 查詢Set中重複的元素
- 在Django中查詢重複專案Django
- 查詢刪除表中重複記錄
- Excel查詢重複項Excel
- Python程式碼微優化之加快查詢Python優化
- 查詢集合的重複項
- SQL重複記錄查詢SQL
- 查詢優化優化
- MySQL: 使用explain 優化查詢效能MySqlAI優化
- 使用物化檢視查詢重寫 優化對於 UNION ALL檢視的CONNECT BY查詢優化
- oracle 查詢及刪除表中重複資料Oracle
- mysql 查詢及 刪除表中重複資料MySql
- 重構 - 程式碼優化技巧優化
- MySQL 查詢重複的資料MySql
- 數字查詢統計重複
- Oracle查詢重複資料與刪除重複記錄Oracle
- SQL使用模糊查詢like的優化SQL優化
- pgsql查詢優化之模糊查詢SQL優化
- Oracle in 查詢優化Oracle優化
- MySQL查詢優化MySql優化
- join 查詢優化優化
- HBase查詢優化優化
- 查詢優化器優化
- SQL查詢優化SQL優化
- 複合條件查詢的重構
- Oracle查詢重複資料與刪除重複記錄方法Oracle
- excel 查詢重複資料,且能提示那行與那行重複Excel
- Sql最佳化(十二)避免資料重複掃描(2) 使用with as子句提高效能SQL
- git stash關於程式碼中bug的查詢使用Git
- PostgreSQL 原始碼解讀（29）- 查詢語句#14（查詢優化-上拉子查詢）SQL原始碼優化
- excel查重是怎麼查的 excel表格怎麼查詢重複項Excel
- MySQL 的查詢優化MySql優化
- 分頁查詢優化優化
- MySQL 慢查詢優化MySql優化
- KunlunDB 查詢優化（一）優化
- MySQL優化COUNT()查詢MySql優化
- 優化sql查詢速度優化SQL