With as主要体现在增加sql易读性和减少IO成本
1、with as增加易读性比较好理解，比如查询业务部分所有人的平均薪水
SQL> select mgr,avg(sal) avg_sal from emp group by mgr;
MGR AVG_SAL
---------- ----------
7839 2758.33333
5000
7782 1300
7698 1310
7902 800
7566 3000
7788 1100
7 rows selected.
SQL> with avgsal as (select mgr,avg(sal) avg_sal from emp group by mgr)
2 select * from avgsal;
7 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4067220884
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 48 | 4 (25)| 00:00:01 |
| 1 | HASH GROUP BY | | 6 | 48 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 112 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
715 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7 rows processed
2、with as还有一个比较突出的作用就是减少重复部分IO成本，oracle会自动对with as的结果集构造一个临时表，后续oracle可以扫描这个临时表来降低IO消耗
下面我们来构造一个with as的改写的案例
SQL> SELECT COUNT (*) cnt, object_type
2 FROM t01
3 WHERE object_type = 'TABLE'
4 GROUP BY object_type
5 UNION ALL
6 SELECT COUNT (*), object_type
7 FROM t01
8 WHERE object_type = 'INDEX'
9 GROUP BY object_type
10 UNION ALL
11 SELECT COUNT (*), object_type
12 FROM t01
13 WHERE object_type = 'VIEW'
14 GROUP BY object_type;
CNT OBJECT_TYPE
---------- -------------------
1610 TABLE
1721 INDEX
3672 VIEW
Execution Plan
----------------------------------------------------------
Plan hash value: 2988477672
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 27 | 463 (67)| 00:00:06 |
| 1 | UNION-ALL | | | | | |
| 2 | SORT GROUP BY NOSORT| | 1 | 9 | 154 (1)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | T01 | 1734 | 15606 | 154 (1)| 00:00:02 |
| 4 | SORT GROUP BY NOSORT| | 1 | 9 | 154 (1)| 00:00:02 |
|* 5 | TABLE ACCESS FULL | T01 | 1734 | 15606 | 154 (1)| 00:00:02 |
| 6 | SORT GROUP BY NOSORT| | 1 | 9 | 154 (1)| 00:00:02 |
|* 7 | TABLE ACCESS FULL | T01 | 1734 | 15606 | 154 (1)| 00:00:02 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("OBJECT_TYPE"='TABLE')
5 - filter("OBJECT_TYPE"='INDEX')
7 - filter("OBJECT_TYPE"='VIEW')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2088 consistent gets
0 physical reads
0 redo size
662 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
SQL> WITH a AS ( SELECT COUNT (*) cnt, object_type
2 FROM t01
3 GROUP BY object_type)
4 SELECT cnt, object_type
5 FROM a
6 WHERE a.object_type = 'TABLE'
7 UNION ALL
8 SELECT cnt, object_type
9 FROM a
10 WHERE a.object_type = 'INDEX'
11 UNION ALL
12 SELECT cnt, object_type
13 FROM a
14 WHERE a.object_type = 'VIEW';
CNT OBJECT_TYPE
---------- -------------------
1610 TABLE
1721 INDEX
3672 VIEW
Execution Plan
----------------------------------------------------------
Plan hash value: 48651815
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 87 | 2088 | 6 (67) | 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
| 3 | HASH GROUP BY | | 29 | 261 | 158 (4)| 00:00:02 |
| 4 | TABLE ACCESS FULL | T01 | 50293 | 442K| 154 (1)| 00:00:02 |
| 5 | UNION-ALL | | | | | |
|* 6 | VIEW | | 29 | 696 | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660F_2EC7C2 | 29 | 261 | 2 (0)| 00:00:01 |
|* 8 | VIEW | | 29 | 696 | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660F_2EC7C2 | 29 | 261 | 2 (0)| 00:00:01 |
|* 10 | VIEW | | 29 | 696 | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660F_2EC7C2 | 29 | 261 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("A"."OBJECT_TYPE"='TABLE')
8 - filter("A"."OBJECT_TYPE"='INDEX')
10 - filter("A"."OBJECT_TYPE"='VIEW')
Statistics
----------------------------------------------------------
102 recursive calls
11 db block gets
719 consistent gets
1 physical reads
1584 redo size
662 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
with as的执行计划中存在了TEMP TABLE TRANSFORMATION和LOAD AS SELECT，这里是将with as组成的结果集转换为了一个临时表，而后面的查询则可以使用这个临时表，由于这个临时表一般比原来的表或者表连接的数据量小，所以无论IO还是cpu成本都相对原sql语句都减小了。
oracle一般会在重复使用两次的地方自动将with as转化为临时表，类似使用物化视图的思想，比如有些sql语句我们无法将其优化，将sql语句查询返回的结果集（with as也是如此）物化为一个实体的表，而这个表的访问成本大大小于之前返回该结果集的方式（比如访问该结果集要采取全表扫描、多表关联hash join、nested loop等）
3、同样即使with as转换的结果集我们只访问一次，也可以加上hint materialize将其构造为临时表。
SQL> WITH a AS ( SELECT /*+materialize*/COUNT (*) cnt, object_type
2 FROM t01
3 GROUP BY object_type)
4 SELECT cnt, object_type
5 FROM a where a.object_type='TABLE';
CNT OBJECT_TYPE
---------- -------------------
1610 TABLE
Execution Plan
----------------------------------------------------------
Plan hash value: 2073943260
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29 | 696 | 160 (4)| 00:00:02 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
| 3 | HASH GROUP BY | | 29 | 261 | 158 (4)| 00:00:02 |
| 4 | TABLE ACCESS FULL | T01 | 50293 | 442K| 154 (1)| 00:00:02 |
|* 5 | VIEW | | 29 | 696 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6616_2EC7C2 | 29 | 261 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("A"."OBJECT_TYPE"='TABLE')
Statistics
----------------------------------------------------------
102 recursive calls
10 db block gets
713 consistent gets
1 physical reads
1468 redo size
586 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4、with as优化实践
生产环境的一个sql语句：
SELECT t1.contactid,
t1.skillid,
t2.turntime starttime,
t2.intime
FROM call.base_contactstate t1,
( SELECT t.contactid,
MIN (t.starttime) turntime,
MAX (t.starttime) intime
FROM call.base_contactstate t
WHERE t.eventid = 806355209 AND t.contactstatus = 2
GROUP BY t.contactid) t2
WHERE t1.contactid = t2.contactid
AND t1.starttime = t2.intime
AND t1.eventid = 806355209
AND t1.contactstatus = 2;
Execution Plan
----------------------------------------------------------
Plan hash value: 2829800415
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 145 | | 76953 (2)| 00:15:24 |
|* 1 | FILTER | | | | | | |
| 2 | HASH GROUP BY | | 1 | 145 | | 76953 (2)| 00:15:24 |
|* 3 | HASH JOIN | | 114K| 15M| 8272K| 76943 (2)| 00:15:24 |
|* 4 | TABLE ACCESS FULL| BASE_CONTACTSTATE | 109K| 6977K| | 38029 (2)| 00:07:37 |
|* 5 | TABLE ACCESS FULL| BASE_CONTACTSTATE | 109K| 8587K| | 38029 (2)| 00:07:37 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T1"."STARTTIME"=MAX("T"."STARTTIME"))
3 - access("T1"."CONTACTID"="T"."CONTACTID")
4 - filter("T"."EVENTID"=806355209 AND "T"."CONTACTSTATUS"=2)
5 - filter("T1"."EVENTID"=806355209 AND "T1"."CONTACTSTATUS"=2)
Statistics
----------------------------------------------------------
666 recursive calls
0 db block gets
324498 consistent gets
336648 physical reads
0 redo size
52016865 bytes sent via SQL*Net to client
397768 bytes received via SQL*Net from client
36118 SQL*Net roundtrips to/from client
21 sorts (memory)
0 sorts (disk)
541747 rows processed
这个sql语句如果想避免base_contactstate的全表扫描很难，但是我们注意到全表扫描了两次base_contactstate表，我们尝试利用下with as物化这个表的部分结果集为临时表来减小IO成本。
WITH t AS (SELECT contactid, skillid, starttime
FROM call.base_contactstate
WHERE eventid = 806355209 AND contactstatus = 2)
SELECT t1.contactid,
t1.skillid,
t2.turntime starttime,
t2.intime
FROM t t1,
( SELECT t.contactid,
MIN (t.starttime) turntime,
MAX (t.starttime) intime
FROM t
GROUP BY t.contactid) t2
WHERE t1.contactid = t2.contactid AND t1.starttime = t2.intime
Execution Plan
----------------------------------------------------------
Plan hash value: 1354327316
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 183 | | 41280 (2)| 00:08:16 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | |
| 2 | LOAD AS SELECT | | | | | | |
|* 3 | TABLE ACCESS FULL | BASE_CONTACTSTATE | 109K| 7299K| | 38029 (2)| 00:07:37 |
|* 4 | HASH JOIN | | 1 | 183 | 9984K| 3251 (1)| 00:00:40 |
| 5 | VIEW | | 109K| 8694K| | 227 (2)| 00:00:03 |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66ED_3363B5A2 | 109K| 6225K| | 227 (2)| 00:00:03 |
| 7 | VIEW | | 109K| 10M| | 1942 (1)| 00:00:24 |
| 8 | HASH GROUP BY | | 109K| 6977K| 8232K| 1942 (1)| 00:00:24 |
| 9 | VIEW | | 109K| 6977K| | 227 (2)| 00:00:03 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66ED_3363B5A2 | 109K| 6225K| | 227 (2)| 00:00:03 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("EVENTID"=806355209 AND "CONTACTSTATUS"=2)
4 - access("T1"."CONTACTID"="T2"."CONTACTID" AND "T1"."STARTTIME"="T2"."INTIME")
Statistics
----------------------------------------------------------
1937 recursive calls
5572 db block gets
173790 consistent gets
173540 physical reads
1768 redo size
52132593 bytes sent via SQL*Net to client
397768 bytes received via SQL*Net from client
36118 SQL*Net roundtrips to/from client
44 sorts (memory)
0 sorts (disk)
541747 rows processed
这里我们用with as改写后，逻辑读和cost成本都降低了接近一半的样子
5、with as的改写后，oracle的优化器会计算是否用临时表的方式来完成查询，如果临时表的执行计划计算而来的cost较大，oracle还是会选择之前的方式，即使已经改写成了with as的临时表的sql语句
SQL> SELECT COUNT (1)
2 FROM (SELECT s.*
3 FROM call.HF_DM_SAMPLE s
4 INNER JOIN
5 ( SELECT biz_sheet_no bizno, MAX (project_task_times) times
6 FROM call.HF_DM_SAMPLE
7 WHERE project_id = '000000000001'
8 GROUP BY biz_sheet_no) tb_tmp
9 ON tb_tmp.bizno = s.biz_sheet_no
10 AND tb_tmp.times = s.project_task_times
11 AND s.blank0 LIKE '%160173576%') c__;
Plan hash value: 1949914683
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 13469 (1)| 00:02:42 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | | 1431 | | 13469 (1)| 00:02:42 |
|* 3 | FILTER | | | | | |
| 4 | HASH GROUP BY | | 1431 | 95877 | 13469 (1)| 00:02:42 |
|* 5 | HASH JOIN | | 10011 | 655K| 13468 (1)| 00:02:42 |
|* 6 | TABLE ACCESS FULL| HF_DM_SAMPLE | 8478 | 314K| 6709 (1)| 00:01:21 |
|* 7 | TABLE ACCESS FULL| HF_DM_SAMPLE | 165K| 4677K| 6757 (2)| 00:01:22 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("S"."PROJECT_TASK_TIMES"=MAX("PROJECT_TASK_TIMES"))
5 - access("BIZ_SHEET_NO"="S"."BIZ_SHEET_NO")
6 - filter("S"."BLANK0" LIKE '%160173576%' AND "S"."BLANK0" IS NOT NULL)
7 - filter("PROJECT_ID"='000000000001')
SQL> WITH a AS (SELECT
biz_sheet_no,
project_task_times,
project_id,
blank0
FROM call.HF_DM_SAMPLE)
SELECT COUNT (1)
FROM (SELECT a.*
FROM a
INNER JOIN
( SELECT biz_sheet_no bizno, MAX (project_task_times) times
FROM a
WHERE a.project_id = '000000000001'
GROUP BY biz_sheet_no) tb_tmp
ON tb_tmp.bizno = a.biz_sheet_no
AND tb_tmp.times = a.project_task_times
AND a.blank0 LIKE '%160173576%') c__;
Plan hash value: 1949914683
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 13469 (1)| 00:02:42 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | | 1431 | | 13469 (1)| 00:02:42 |
|* 3 | FILTER | | | | | |
| 4 | HASH GROUP BY | | 1431 | 95877 | 13469 (1)| 00:02:42 |
|* 5 | HASH JOIN | | 10011 | 655K| 13468 (1)| 00:02:42 |
|* 6 | TABLE ACCESS FULL| HF_DM_SAMPLE | 8478 | 314K| 6709 (1)| 00:01:21 |
|* 7 | TABLE ACCESS FULL| HF_DM_SAMPLE | 165K| 4677K| 6757 (2)| 00:01:22 |
---------------------------------------------------------------------------------------
看出这里改写为with as临时表的sql语句后，cbo还是用的之前的执行计划的方式，而我们必须加上hint materialize的优化器才会采用临时表的方式完成查询。
SQL> WITH a AS (SELECT /*+materialize*/
2 biz_sheet_no,
3 project_task_times,
4 project_id,
5 blank0
6 FROM call.HF_DM_SAMPLE)
7 SELECT COUNT (1)
8 FROM (SELECT a.*
9 FROM a
10 INNER JOIN
11 ( SELECT biz_sheet_no bizno, MAX (project_task_times) times
12 FROM a
13 WHERE a.project_id = '000000000001'
14 GROUP BY biz_sheet_no) tb_tmp
15 ON tb_tmp.bizno = a.biz_sheet_no
16 AND tb_tmp.times = a.project_task_times
17 AND a.blank0 LIKE '%160173576%') c__;
Execution Plan
----------------------------------------------------------
Plan hash value: 2209692010
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 425 | | 14130 (1)| 00:02:50 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | |
| 2 | LOAD AS SELECT | | | | | | |
| 3 | TABLE ACCESS FULL | HF_DM_SAMPLE | 174K| 6640K| | 6759 (2)| 00:01:22 |
| 4 | SORT AGGREGATE | | 1 | 425 | | | |
|* 5 | HASH JOIN | | 24908 | 10M| 13M| 7371 (1)| 00:01:29 |
| 6 | VIEW | | 143K| 11M| | 3590 (1)| 00:00:44 |
| 7 | HASH GROUP BY | | 143K| 13M| 18M| 3590 (1)| 00:00:44 |
|* 8 | VIEW | | 174K| 15M| | 209 (3)| 00:00:03 |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6603_FFCC105A | 174K| 6640K| | 209 (3)| 00:00:03 |
|* 10 | VIEW | | 174K| 56M| | 209 (3)| 00:00:03 |
| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6603_FFCC105A | 174K| 6640K| | 209 (3)| 00:00:03 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("TB_TMP"."BIZNO"="A"."BIZ_SHEET_NO" AND "TB_TMP"."TIMES"="A"."PROJECT_TASK_TIMES")
8 - filter("A"."PROJECT_ID"='000000000001')
10 - filter("A"."BLANK0" LIKE '%160173576%' AND "A"."BLANK0" IS NOT NULL)
概述：执行计划中如果出现重复的大表扫描表连接部分，要联想到是否能够通过with as物化这个结果集为一个临时表。
最后修改时间：2020-02-12 08:28:46
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
