【WITH Clause】使用WITH子句提高查詢統計效率-顛覆思維定勢
這裡介紹的的是一種SQL查詢方法，顛覆您日常以select開始的SQL查詢寫法。
這種神奇的使用方法的背後隱藏著效能的提升！
我們一起來體驗一下其中的奧妙。
1.建立表T，並簡單初始化幾條資料
sec@ora10g> create table t (x number(10), y number(10));
sec@ora10g> insert into t values (1,110);
sec@ora10g> insert into t values (2,120);
sec@ora10g> insert into t values (2,80);
sec@ora10g> insert into t values (3,150);
sec@ora10g> insert into t values (3,30);
sec@ora10g> insert into t values (3,60);
sec@ora10g> commit;
sec@ora10g> select * from t;
X Y
---------- ----------
1 110
2 120
2 80
3 150
3 30
3 60
6 rows selected.
2.需求描述
按照x列分組後統計y列的總值，我們的最終目標是選出比y列總值的三分之一大的那些分組統計資訊。
3.使用子查詢方式實現
這可能是大家最容易想到的方法。
sec@ora10g> SELECT x, SUM (y) AS total_y
2 FROM t
3 GROUP BY x
4 HAVING SUM (y) > (SELECT SUM (y) / 3 FROM t)
5 ORDER BY total_y
6 /
X TOTAL_Y
---------- ----------
2 200
3 240
4.WITH Clause方法閃亮登場
sec@ora10g> WITH secooler_sum AS (SELECT x, SUM (y) total_y
2 FROM t
3 GROUP BY x)
4 SELECT x, total_y
5 FROM secooler_sum
6 WHERE total_y > (SELECT SUM (total_y) / 3 FROM secooler_sum)
7 ORDER BY total_y
8 /
X TOTAL_Y
---------- ----------
2 200
3 240
是不是很神奇！這裡的查詢語句不是以select開始的，而是以“WITH”關鍵字開頭。可以認為Oracle在真正進行查詢之前預先構造了一個臨時表secooler_sum，之後我們便可多次使用它做進一步的分析和處理。
5.WITH Clause方法的優點
使用WITH Clause方法有什麼好處呢？
首先，增加了SQL的易讀性，如果構造了多個子查詢，結構會更清晰；
更重要的是：“一次分析，多次使用”，這也是為什麼會提供效能的地方，達到了“少讀”的目標。
6.知其所以然
為什麼WITH Clause方法會提高效率？透過檢視上面兩種方法的執行計劃便可略知一二。
1）使用子查詢的執行計劃
sec@ora10g> set autot trace exp
sec@ora10g> SELECT x, SUM (y) AS total_y
2 FROM t
3 GROUP BY x
4 HAVING SUM (y) > (SELECT SUM (y) / 3 FROM t)
5 ORDER BY total_y
6 /
Execution Plan
----------------------------------------------------------
Plan hash value: 4167292448
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 156 | 5 (40)| 00:00:01 |
| 1 | SORT ORDER BY | | 6 | 156 | 5 (40)| 00:00:01 |
|* 2 | FILTER | | | | | |
| 3 | HASH GROUP BY | | 6 | 156 | 5 (40)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T | 6 | 156 | 3 (0)| 00:00:01 |
| 5 | SORT AGGREGATE | | 1 | 13 | | |
| 6 | TABLE ACCESS FULL| T | 6 | 78 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(SUM("Y")> (SELECT SUM("Y")/3 FROM "T" "T"))
Note
-----
- dynamic sampling used for this statement
2）使用子WITH Clause的執行計劃
sec@ora10g> WITH secooler_sum AS (SELECT x, SUM (y) total_y
2 FROM t
3 GROUP BY x)
4 SELECT x, total_y
5 FROM secooler_sum
6 WHERE total_y > (SELECT SUM (total_y) / 3 FROM secooler_sum)
7 ORDER BY total_y
8 /
Execution Plan
----------------------------------------------------------
Plan hash value: 706070671
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 156 | 9 (23)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
| 3 | HASH GROUP BY | | 6 | 156 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T | 6 | 156 | 3 (0)| 00:00:01 |
| 5 | SORT ORDER BY | | 6 | 156 | 5 (20)| 00:00:01 |
|* 6 | VIEW | | 6 | 156 | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6660_23A196E | 6 | 156 | 2 (0)| 00:00:01 |
| 8 | SORT AGGREGATE | | 1 | 13 | | |
| 9 | VIEW | | 6 | 78 | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6660_23A196E | 6 | 156 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("TOTAL_Y"> (SELECT SUM("TOTAL_Y")/3 FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */
"C0" "X","C1" "TOTAL_Y" FROM "SYS"."SYS_TEMP_0FD9D6660_23A196E" "T1") "SECOOLER_SUM"))
Note
-----
- dynamic sampling used for this statement
可見，第一種使用子查詢的方法T表被掃描了兩次，而使用WITH Clause方法，T表僅被掃描一次。
這也是為什麼在大型資料倉儲系統中推薦使用WITH Clause方法進行查詢統計的原因，這樣可以大大的提高資料分析和查詢的效率。
另外，觀察WITH Clause方法執行計劃，其中“SYS_TEMP_0FD9D6660_23A196E”便是在執行過程中構造的中間統計結果臨時表。
7.Oracle官方文件中有關WITH Clause的簡單描述
Computation Using the WITH Clause
The WITH clause (formally known as subquery_factoring_clause) enables you to reuse the same query block in a SELECT statement when it occurs more than once within a complex query. WITH is a part of the SQL-99 standard. This is particularly useful when a query has multiple references to the same query block and there are joins and aggregations. Using the WITH clause, Oracle retrieves the results of a query block and stores them in the user's temporary tablespace. Note that Oracle Database does not support recursive use of the WITH clause.
參考連結：
8.小結
WITH Clause方法在資料倉儲或大資料量查詢中有著自己的優勢。
一切提高效能的措施都是推崇的。靈活掌握，裨益無限。在遇到具體問題時可善加利用。
Good luck.
secooler
10.03.22
-- The End --
這種神奇的使用方法的背後隱藏著效能的提升！
我們一起來體驗一下其中的奧妙。
1.建立表T，並簡單初始化幾條資料
sec@ora10g> create table t (x number(10), y number(10));
sec@ora10g> insert into t values (1,110);
sec@ora10g> insert into t values (2,120);
sec@ora10g> insert into t values (2,80);
sec@ora10g> insert into t values (3,150);
sec@ora10g> insert into t values (3,30);
sec@ora10g> insert into t values (3,60);
sec@ora10g> commit;
sec@ora10g> select * from t;
X Y
---------- ----------
1 110
2 120
2 80
3 150
3 30
3 60
6 rows selected.
2.需求描述
按照x列分組後統計y列的總值，我們的最終目標是選出比y列總值的三分之一大的那些分組統計資訊。
3.使用子查詢方式實現
這可能是大家最容易想到的方法。
sec@ora10g> SELECT x, SUM (y) AS total_y
2 FROM t
3 GROUP BY x
4 HAVING SUM (y) > (SELECT SUM (y) / 3 FROM t)
5 ORDER BY total_y
6 /
X TOTAL_Y
---------- ----------
2 200
3 240
4.WITH Clause方法閃亮登場
sec@ora10g> WITH secooler_sum AS (SELECT x, SUM (y) total_y
2 FROM t
3 GROUP BY x)
4 SELECT x, total_y
5 FROM secooler_sum
6 WHERE total_y > (SELECT SUM (total_y) / 3 FROM secooler_sum)
7 ORDER BY total_y
8 /
X TOTAL_Y
---------- ----------
2 200
3 240
是不是很神奇！這裡的查詢語句不是以select開始的，而是以“WITH”關鍵字開頭。可以認為Oracle在真正進行查詢之前預先構造了一個臨時表secooler_sum，之後我們便可多次使用它做進一步的分析和處理。
5.WITH Clause方法的優點
使用WITH Clause方法有什麼好處呢？
首先，增加了SQL的易讀性，如果構造了多個子查詢，結構會更清晰；
更重要的是：“一次分析，多次使用”，這也是為什麼會提供效能的地方，達到了“少讀”的目標。
6.知其所以然
為什麼WITH Clause方法會提高效率？透過檢視上面兩種方法的執行計劃便可略知一二。
1）使用子查詢的執行計劃
sec@ora10g> set autot trace exp
sec@ora10g> SELECT x, SUM (y) AS total_y
2 FROM t
3 GROUP BY x
4 HAVING SUM (y) > (SELECT SUM (y) / 3 FROM t)
5 ORDER BY total_y
6 /
Execution Plan
----------------------------------------------------------
Plan hash value: 4167292448
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 156 | 5 (40)| 00:00:01 |
| 1 | SORT ORDER BY | | 6 | 156 | 5 (40)| 00:00:01 |
|* 2 | FILTER | | | | | |
| 3 | HASH GROUP BY | | 6 | 156 | 5 (40)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T | 6 | 156 | 3 (0)| 00:00:01 |
| 5 | SORT AGGREGATE | | 1 | 13 | | |
| 6 | TABLE ACCESS FULL| T | 6 | 78 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(SUM("Y")> (SELECT SUM("Y")/3 FROM "T" "T"))
Note
-----
- dynamic sampling used for this statement
2）使用子WITH Clause的執行計劃
sec@ora10g> WITH secooler_sum AS (SELECT x, SUM (y) total_y
2 FROM t
3 GROUP BY x)
4 SELECT x, total_y
5 FROM secooler_sum
6 WHERE total_y > (SELECT SUM (total_y) / 3 FROM secooler_sum)
7 ORDER BY total_y
8 /
Execution Plan
----------------------------------------------------------
Plan hash value: 706070671
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 156 | 9 (23)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
| 3 | HASH GROUP BY | | 6 | 156 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T | 6 | 156 | 3 (0)| 00:00:01 |
| 5 | SORT ORDER BY | | 6 | 156 | 5 (20)| 00:00:01 |
|* 6 | VIEW | | 6 | 156 | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6660_23A196E | 6 | 156 | 2 (0)| 00:00:01 |
| 8 | SORT AGGREGATE | | 1 | 13 | | |
| 9 | VIEW | | 6 | 78 | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6660_23A196E | 6 | 156 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("TOTAL_Y"> (SELECT SUM("TOTAL_Y")/3 FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */
"C0" "X","C1" "TOTAL_Y" FROM "SYS"."SYS_TEMP_0FD9D6660_23A196E" "T1") "SECOOLER_SUM"))
Note
-----
- dynamic sampling used for this statement
可見，第一種使用子查詢的方法T表被掃描了兩次，而使用WITH Clause方法，T表僅被掃描一次。
這也是為什麼在大型資料倉儲系統中推薦使用WITH Clause方法進行查詢統計的原因，這樣可以大大的提高資料分析和查詢的效率。
另外，觀察WITH Clause方法執行計劃，其中“SYS_TEMP_0FD9D6660_23A196E”便是在執行過程中構造的中間統計結果臨時表。
7.Oracle官方文件中有關WITH Clause的簡單描述
Computation Using the WITH Clause
The WITH clause (formally known as subquery_factoring_clause) enables you to reuse the same query block in a SELECT statement when it occurs more than once within a complex query. WITH is a part of the SQL-99 standard. This is particularly useful when a query has multiple references to the same query block and there are joins and aggregations. Using the WITH clause, Oracle retrieves the results of a query block and stores them in the user's temporary tablespace. Note that Oracle Database does not support recursive use of the WITH clause.
參考連結：
8.小結
WITH Clause方法在資料倉儲或大資料量查詢中有著自己的優勢。
一切提高效能的措施都是推崇的。靈活掌握，裨益無限。在遇到具體問題時可善加利用。
Good luck.
secooler
10.03.22
-- The End --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-630132/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用Bulk Collect提高Oracle查詢效率Oracle
- 顛覆與自我顛覆，思域的進階之路
- 【索引】Oracle查詢指定索引提高查詢效率索引Oracle
- Oracle提高查詢效率的方法Oracle
- 提高mysql查詢效率的六種方法MySql
- 提高mysql查詢效率及一些使用技巧記錄MySql
- 提高查詢速度使用materizlizedZed
- 談談MYSQL索引是如何提高查詢效率的MySql索引
- 使用with子句優化程式碼中重複查詢優化
- 使用謂詞(NSPredicate)來提高集合遍歷與過濾查詢的效率
- 提高ORACLE資料庫的查詢統計速度（轉）Oracle資料庫
- NULL列時，如何使得IS NULL或者IS NOT NULL可以使用索引來提高查詢效率Null索引
- 徹底顛覆傳統資料中心的十大趨勢
- 使用RESULT CACHE加速SQL查詢效率SQL
- 谷歌在顛覆式定義電腦谷歌
- hdu4400 STL應用 查詢思維題
- 突破常識：SQL增加DISTINCT後查詢效率反而提高SQL
- 效率思維模式與Zombie Scrum模式Scrum
- 蘋果要用 Swift 語言顛覆傳統程式設計蘋果Swift程式設計
- sql 查詢效率SQL
- 用行雲管家實現IT統一運維管理，提高運維效率運維
- 量子計算：未來戰爭“顛覆者”
- 字母統計（陣列思維）陣列
- ElasticSearch在數十億級別資料下，如何提高查詢效率？Elasticsearch
- MySQL索引憑什麼能讓查詢效率提高這麼多？MySql索引
- 如何利用mysql5.7提供的虛擬列來提高查詢效率MySql
- 在大資料量下提高查詢效率的方法—ES搜尋引擎大資料
- 提高sql查詢速度SQL
- 提高SQL查詢效能SQL
- 提高count查詢速度
- 請教如何提高查詢系統的效能？
- 無伺服器計算； 新型付款模式顛覆傳統IT投資伺服器模式
- Axie Infinity使用區塊鏈顛覆了傳統遊戲 - Platformer區塊鏈遊戲Platform
- 提高 MongoDB 運維效率的實用技巧MongoDB運維
- 產品思維， UX設計下一個趨勢？UX
- 關聯查詢子查詢效率簡單比照
- cassandra查詢效率探討
- 會計CRM系統軟體提高公司管理效率