WITH AS and materialize hints

时间:2023-04-24 22:40:56
WITH AS: 就是将一个子查询部分独立出来,有时候是为了提高SQL语句的可读性,有时候是为了提高SQL语句性能。
如果一个SQL语句中,某个表会被访问多次,而且每次访问的限制条件一样的话,就可以使用with as来提高性能。
注意:如果 with as 短语没有被调用2次以上,CBO就不会讲这个短语获取的数据放入temp表,如果想要讲数据放入temp表需要使用materialize hint
如果 with as 短语被调用了2次以上,CBO会自动将 with as 短语的数据放入一个临时表,这个时候不用写materialize hint 举个例子(本例基于Scott用户) SQL> explain plan for
with a as (select /*+ materialize */ ename,job,deptno from emp where sal>(select avg(sal) from emp))
select * from a ;
2 3
Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 2006423466 -------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 8 (0)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6605_E16CE | | | | |
|* 3 | TABLE ACCESS FULL | EMP | 1 | 21 | 3 (0)| 00:00:01 |
| 4 | SORT AGGREGATE | | 1 | 4 | | |
| 5 | TABLE ACCESS FULL | EMP | 14 | 56 | 3 (0)| 00:00:01 |
| 6 | VIEW | | 1 | 26 | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6605_E16CE | 1 | 17 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):
--------------------------------------------------- 3 - filter("SAL"> (SELECT AVG("SAL") FROM "EMP" "EMP")) 19 rows selected. 去掉 /*+ materialize */ ,由于只访问了一次a,所以CBO不会将a的查询结果生成一个临时表 SQL> explain plan for
with a as (select ename,job,deptno from emp where sal>(select avg(sal) from emp))
select * from a ; 2 3 Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 1876299339 ----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL | EMP | 1 | 21 | 3 (0)| 00:00:01 |
| 2 | SORT AGGREGATE | | 1 | 4 | | |
| 3 | TABLE ACCESS FULL| EMP | 14 | 56 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------- Predicate Information (identified by operation id):
--------------------------------------------------- 1 - filter("SAL"> (SELECT AVG("SAL") FROM "EMP" "EMP")) 15 rows selected. WITH AS 语句调用一次 使用多次 需要写hints 如果 表 只 扫描 1次,你些materialize hints 结果读了一次 还写入temp, 再从temp读出来
临时表写入是1次,但是读要多次。 继续测试:
SQL> explain plan for
with a as (select ename,job,deptno from emp where sal>(select avg(sal) from emp))
select * from a union all select * from a; 2 3 Explained. SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------------
Plan hash value: 2575088720 --------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 52 | 4 (50)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6601_4DC46A | | | | |
|* 3 | TABLE ACCESS FULL | EMP | 1 | 39 | 3 (0)| 00:00:01 |
| 4 | SORT AGGREGATE | | 1 | 13 | | |
| 5 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 |
| 6 | UNION-ALL | | | | | |
| 7 | VIEW | | 1 | 26 | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6601_4DC46A | 1 | 26 | 2 (0)| 00:00:01 |
| 9 | VIEW | | 1 | 26 | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6601_4DC46A | 1 | 26 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):
--------------------------------------------------- 3 - filter("SAL"> (SELECT AVG("SAL") FROM "EMP" "EMP")) Note
-----
- dynamic sampling used for this statement (level=2) 26 rows selected. 充分证明 :
1.当with as 语句没有被调用2次以上时,如果表需要访问多次,那么需要加hints /*+ materialize */ 2.如果with as 语句被调用2次以上时,自动会将 with as 短语的数据放入一个临时表,这个时候不用写materialize hint