with as 查詢效能記載
With as短語也叫做子查詢部分，定義一個sql片斷，該sql片斷會被整個sql語句所用到。
With as 查詢在有的時候，是為了讓SQL語句的可讀性更高些，有的時候也可以大幅提高sql的查詢時間。
對於sql中需要重複查詢的表可以使用WITH AS短語，則只要執行一遍即可。如果WITH AS短語所定義的表名被呼叫兩次以上，則最佳化器會自動將WITH AS短語所獲取的資料放入一個TEMP表裡，如果只是被呼叫一次，則不會。
而提示materialize則是強制將WITH AS短語裡的資料放入一個全域性臨時表裡。很多查詢透過這種方法都可以提高速度。
with as查詢特別對於UNION ALL比較有用。做報表時很有可能需要union all同一個表來分列顯示資料資訊，而union all 表的名稱又相同，如果不使用with as，oracle會重複掃描相同的表，而對於sql的調整方面減少查詢明顯可以提高sql的執行效率。
做個測試來看看實際執行效果：
create table t (x number(10), y number(10));
建立一個t_sequence序列
create sequence t_sequence
increment by 1
start with 1
maxvalue 9999
cycle
nocache
往t表中插入1W條資料
declare
i number;
begin
for i in 1..10000 loop
insert into t values(t_sequence.currval,t_sequence.nextval);
commit;
end loop;
end;
例如此時需要查詢按x分組的y的總和在y的總數的1/3以上和1/2以下的分組資訊
Select x,sum(y) cn from t
Group by x
Having sum(y)>(select 1/3*sum(y) from t) and sum(y)
可能一般都會這麼去寫，實際可以看出對錶執行了三個全表掃描
With s as
(select x,sum(y) cn from t
group by x)
Select x,sum(y) from s
Where cn> (select 1/3*sum(cn) from s) and cn
看看兩個sql的執行計劃吧。
SQL> Select x,sum(y) cn from t
2 Group by x
3 Having sum(y)>(select 1/3*sum(y) from t) and sum(y)
4 ;
已用時間: 00: 00: 00.56
執行計劃
----------------------------------------------------------
Plan hash value: 3810713582
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 156 | 4 (25)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 6 | 156 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T | 6 | 156 | 3 (0)| 00:00:01 |
| 4 | SORT AGGREGATE | | 1 | 13 | | |
| 5 | TABLE ACCESS FULL | T | 6 | 78 | 3 (0)| 00:00:01 |
| 6 | SORT AGGREGATE | | 1 | 13 | | |
| 7 | TABLE ACCESS FULL| T | 6 | 78 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SUM("Y")> (SELECT .333333333333333333333333333333333333333
3*SUM("Y") FROM "T" "T") AND SUM("Y")< (SELECT .5*SUM("Y") FROM "T
"
"T"))
Note
-----
- dynamic sampling used for this statement
統計資訊
----------------------------------------------------------
315 recursive calls
0 db block gets
107 consistent gets
14 physical reads
0 redo size
499 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> alter system flush buffer_cache;
系統已更改。
已用時間: 00: 00: 00.01
SQL> alter system flush shared_pool;
系統已更改。
已用時間: 00: 00: 00.04
SQL> With s as
2 (select x,sum(y) cn from t
3 group by x)
4 Select x,cn from s
5 Where cn> (select 1/3*sum(cn) from s) and cn
已用時間: 00: 00: 02.26
執行計劃
----------------------------------------------------------
Plan hash value: 2829026223
--------------------------------------------------------------------------------
------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
--------------------------------------------------------------------------------
------------------------
| 0 | SELECT STATEMENT | | 6 | 156 |
10 (10)| 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 | VIEW | | 6 | 156 |
2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660B_2C190C | 6 | 156 |
2 (0)| 00:00:01 |
| 7 | SORT AGGREGATE | | 1 | 13 |
| |
| 8 | VIEW | | 6 | 78 |
2 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660B_2C190C | 6 | 156 |
2 (0)| 00:00:01 |
| 10 | SORT AGGREGATE | | 1 | 13 |
| |
| 11 | VIEW | | 6 | 78 |
2 (0)| 00:00:01 |
| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660B_2C190C | 6 | 156 |
2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("CN"> (SELECT .3333333333333333333333333333333333333333*SUM("CN")
FROM (SELECT
/*+ CACHE_TEMP_TABLE ("T1") */ "C0" "X","C1" "CN" FROM "SYS"."SYS_
TEMP_0FD9D660B_2C190C" "T1")
"S") AND "CN"< (SELECT .5*SUM("CN") FROM (SELECT /*+ CACHE_TEMP_T
ABLE ("T1") */ "C0" "X","C1"
"CN" FROM "SYS"."SYS_TEMP_0FD9D660B_2C190C" "T1") "S"))
Note
-----
- dynamic sampling used for this statement
統計資訊
----------------------------------------------------------
2819 recursive calls
11 db block gets
1136 consistent gets
143 physical reads
1428 redo size
499 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
49 sorts (memory)
0 sorts (disk)
2 rows processed
往t表插入40W資料
declare
i number;
begin
for i in 1..400000 loop
insert into t values(t_sequence.currval,t_sequence.nextval);
commit;
end loop;
end;
T表的資料量有40w左右
SQL> select x,sum(y) from t group by x
2 having sum(y)>(select sum(y)*1/3 from t) and sum(y)
3 ;
未選定行
已用時間: 00: 00: 02.69
執行計劃
----------
統計資訊
----------------------------------------------------------
336 recursive calls
1 db block gets
1774 consistent gets
766 physical reads
176 redo size
323 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> alter system flush buffer_cache;
系統已更改。
已用時間: 00: 00: 00.07
SQL> With s as
2 (select x,sum(y) cn from t
3 group by x)
4 Select x,cn from s
5 Where cn> (select 1/3*sum(cn) from s) and cn
未選定行
已用時間: 00: 00: 01.26
執行計劃
---------
711 recursive calls
30 db block gets
1267 consistent gets
806 physical reads
1428 redo size
319 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
11 sorts (memory)
0 sorts (disk)
0 rows processed
t表資料量第一種查詢對錶T進行了三次全掃描，由於t表資料量比較小，而全表掃描的影響對這個查詢效率影響不大，而常規的生產庫中資料量肯定不是這個數量級的，一旦資料量增大，全表掃描消耗對整個系統的消耗那是很可怕的。
（其實檢視user_segments中關於T表段的bytes也只有6M），常規的查詢對錶的掃描所消耗的資源已經不可忽視，採取with as查詢靈活運用with as建立臨時表，這也是為什麼在大型資料倉儲系統中推薦使用WITH Clause方法進行查詢統計的原因，這樣可以大大的提高資料分析和查詢的效率。
[@more@]來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25362835/viewspace-1055856/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 查詢oracle效能SQLOracleSQL
- 提高SQL查詢效能SQL
- SQL查詢效能分析SQL
- EntityFramework優化：查詢效能Framework優化
- 【轉載】為什麼忘記commit也會造成select查詢的效能問題MIT
- [Mysql 查詢語句]——查詢指定記錄MySql
- MYSQL學習筆記25: 多表查詢(子查詢)[標量子查詢,列子查詢]MySql筆記
- 高效能的Mysql讀書筆記系列之六（查詢效能優化）MySql筆記優化
- MySQL 查詢效能分析之 ExplainMySqlAI
- 效能優化之分頁查詢優化
- ClickHouse的查詢效能優勢
- 全文查詢的效能優化優化
- 效能優化查詢語句優化
- 查詢效能提升3倍！Apache Hudi 查詢優化了解下？Apache優化
- CQengine高效能記憶體資料快取查詢框架記憶體快取框架
- 記如何在預載入中指定查詢的欄位
- 資料的儲存和查詢分離不利查詢效能 - thenewstack
- 使用Django annotation，提升django查詢效能Django
- MySQL: 使用explain 優化查詢效能MySqlAI優化
- mysql查詢效能優化總結MySql優化
- 子查詢與join效能差異
- SQLServer效能優化之查詢提示SQLServer優化
- MYSQL學習筆記26: 多表查詢|子查詢MySql筆記
- 轉載 ]查詢Windows記憶體洩露的幾種方法Windows記憶體洩露
- 查詢鎖表記錄
- Greenplum點查(按PK查詢)效能與提升空間
- 用hash cluster表提高查詢效能 (一)
- 索引為什麼能提供查詢效能...索引
- MySQL系列-- 4. 查詢效能優化MySql優化
- 效能查詢並殺CPU高耗程式
- MYSQL高效能學習查詢心得MySql
- MongoDB分頁查詢的方法及效能MongoDB
- MongoDB 分頁查詢的方法及效能MongoDB
- 【oracle 效能優化】組合索引查詢。Oracle優化索引
- oracle效能優化（二）-調整查詢Oracle優化
- 百萬行資料查詢效能比較
- 一次效能問題原因查詢
- Spring Data Jpa 的簡單查詢多表查詢HQL，SQL ，動態查詢， QueryDsl ，自定義查詢筆記SpringSQL筆記