【SQL】使用分析函式與關聯子查詢的比較
由於業務需要，需實現如下功能的sql語句：查詢出一個表T中每個不同id值所對應的最大val值。
---構造環境。
SQL> insert into t values (1,2,1);
已建立 1 行。
SQL> insert into t values (1,2,3);
已建立 1 行。
SQL> insert into t values (1,3,4);
已建立 1 行。
SQL> insert into t values (1,3,5);
已建立 1 行。
SQL> insert into t values (2,3,5);
已建立 1 行。
SQL> insert into t values (2,3,4);
已建立 1 行。
SQL> insert into t values (2,3,6);
已建立 1 行
SQL> insert into t values (2,3,56);
已建立 1 行。
SQL> commit;
提交完成。
SQL> select * from t;
ID STATE VAL
---------- ---------- ----------
1 2 1
1 2 3
1 3 4
1 3 5
2 3 5
2 3 4
2 3 6
2 3 56
已選擇8行。
-----------------------------------------
SQL> select id,state ,val
2 from (select id ,state,val, row_number()
3 over (partition by id order by val desc ) rn from t )
4 where rn=1;
ID STATE VAL
---------- ---------- ----------
1 3 5
2 3 56 ---結果符合要求
-----檢視使用分析函式的執行計劃。
SQL> set autot on
SQL> select id,state ,val
2 from (select id ,state,val, row_number()
3 over (partition by id order by val desc ) rn from t )
4 where rn=1;
ID STATE VAL
---------- ---------- ----------
1 3 5
2 3 56
----查詢出 id 為1對應的最大值 5 ，id 為2 時對應的最大值56
----使用分析函式的執行計劃
執行計劃
----------------------------------------------------------
Plan hash value: 3047187157
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 368 | 4 (25)| 00:00:01 |
|* 1 | VIEW | | 8 | 368 | 4 (25)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK| | 8 | 264 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T | 8 | 264 | 3 (0)| 00:00:01 |
----注意這裡使用了 WINDOW SORT PUSHED RANK，只進行了一次FTS
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"=1)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "ID" ORDER BY
INTERNAL_FUNCTION("VAL") DESC )<=1)
Note
-----
- dynamic sampling used for this statement
統計資訊
----------------------------------------------------------
76 recursive calls
0 db block gets
21 consistent gets
0 physical reads
.......
3 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> set linesize 999
SQL> /
ID STATE VAL
---------- ---------- ----------
1 3 5
2 3 56
執行計劃
----------------------------------------------------------
Plan hash value: 3047187157
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 368 | 4 (25)| 00:00:01 |
|* 1 | VIEW | | 8 | 368 | 4 (25)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK| | 8 | 264 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T | 8 | 264 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"=1)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "ID" ORDER BY
INTERNAL_FUNCTION("VAL") DESC )<=1)
Note
----
- dynamic sampling used for this statement
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets -------邏輯讀為 7
0 physical reads
0 redo size
568 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
---沒有使用分析函式的執行計劃，可以看出此計劃執行了兩次全表掃描（見下面的例子)，而使用了分析函式的情況下，只是掃描了一次！
---顯然當資料量巨大時，兩種方式的資源消耗差別是巨大的。
SQL> select id,state ,val from
2 t a where a.val in
3 (select max(b.val) from t b
4 where b.id =a.id group by id ); ---在業務處理時應該慎用關聯子查詢！
ID STATE VAL
---------- ---------- ----------
1 3 5
2 3 56
執行計劃
----------------------------------------------------------
Plan hash value: 2845706984
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 6 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | T | 8 | 264 | 3 (0)| 00:00:01 |
|* 3 | FILTER | | | | | |
| 4 | SORT GROUP BY NOSORT| | 1 | 26 | 3 (0)| 00:00:01|
|* 5 | TABLE ACCESS FULL | T | 1 | 26 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "T" "B" WHERE "B"."ID"=:B1 GROUP
BY "ID" HAVING MAX("B"."VAL")=:B2))
3 - filter(MAX("B"."VAL")=:B1)
5 - filter("B"."ID"=:B1)
Note
-----
- dynamic sampling used for this statement
統計資訊
----------------------------------------------------------
296 recursive calls
0 db block gets
203 consistent gets ---邏輯讀為 203 是使用分析函式的30倍！
0 physical reads
11 sorts (memory)
0 sorts (disk)
2 rows processed
總結：相關子查詢對於外部查詢的每一個值都會有一個結果與其對應，其計算的過程是這樣的：
1.掃描外查詢的第一條記錄
2.掃描子查詢，並將第一條記錄的對應值傳給子查詢，由此計算出子查詢的結果
3.根據子查詢的結果，返回外查詢的結果。
4.重複上述動作，開始掃描外查詢的第二條記錄，第三條記錄，直至全部掃描完畢。
所以，當使用分析函式能夠滿足業務需求時，最好使用分析函式。處理大量業務資料時，相關子查詢是相當耗費資源的。慎用！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-673105/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- sql語法相關子查詢與非相關子查詢SQL
- 區分關聯子查詢和非關聯子查詢
- exist-in和關聯子查詢-非關聯子查詢
- SQL -- 使用聯結還是子查詢?SQL
- SQL 部分函式的使用，子查詢，group by，虛擬欄位，case……SQL函式
- 什麼是SQL 語句中相關子查詢與非相關子查詢SQL
- 幾個分析函式的比較函式
- 關聯子查詢的用處
- 查詢oracle比較慢的session和sqlOracleSessionSQL
- 在關聯子查詢中in與exists的區別
- 查詢 分析硬解析較高的sql，SQL
- 關聯子查詢 Correlated Subqueries
- 子串查詢函式strstr函式
- 關聯查詢子查詢效率簡單比照
- 查詢oracle比較慢的session和SQL[轉]OracleSessionSQL
- SQL查詢中用到的函式SQL函式
- oracle的sql查詢分析函式-高階部分-分析函授over()子句OracleSQL函式
- 呼叫鏈與日誌關聯的探索式查詢
- MySQL exists關聯子查詢SQL效能及其低下最佳化之等值子查詢轉換MySql
- sql-server不相關子查詢SQLServer
- sql-server相關子查詢SQLServer
- SQL查詢的：子查詢和多表查詢SQL
- 不使用分析函式的累計統計查詢函式
- SQL三表左關聯查詢SQL
- 兩表關聯查詢：sql、mybatisSQLMyBatis
- Python—Django：關於在Django框架中對資料庫的查詢函式，查詢集和關聯查詢PythonDjango框架資料庫函式
- sql子查詢SQL
- 呼叫鏈與日誌的關聯式跟蹤查詢
- BST查詢結構與折半查詢方法的實現與實驗比較
- JavaScript 匿名函式與具名函式執行效率比較JavaScript函式
- js命名函式與匿名函式執行速度比較JS函式
- 使用正規表示式檢驗sql語句(只支援select(不含函式,子查詢)) (轉)SQL函式
- 【索引】oracle查詢使用索引和不使用索引的比較索引Oracle
- 相關子查詢&非相關子查詢概念
- MySQL聯結查詢和子查詢MySql
- 字串比較的常用函式字串函式
- SQL聯合查詢中的關鍵語法SQL
- SQL--子查詢SQL