WITH AS and materialize hints
来源:互联网 发布:淘宝 消防工程师教材 编辑:程序博客网 时间:2024/06/06 03:40
FROM http://blog.csdn.net/zhaoyangjian724/article/details/17692199?ADUIN=604178834&ADSESSION=1388405668&ADTAG=CLIENT.QQ.5281_.0&ADPUBNO=26283
- 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
0 0
- WITH AS and materialize hints
- WITH AS and materialize hints
- WITH AS and materialize hints .
- with as与hint materialize的使用
- with materialize
- Creating hints with onMouseOver and onMouseOut`
- ORACLE使用WITH AS和HINT MATERIALIZE优化SQL解决FILTER效率低下
- ORACLE使用WITH AS和HINT MATERIALIZE优化SQL解决FILTER效率低下
- WITH+HInt MATERIALIZE 不见得有效
- Python之 with and as
- with as
- with as
- with as
- WITH AS
- with...as...
- WITH AS
- with...as
- WITH AS
- 一个建议
- Ubuntu Linux 环境变量PATH设置
- 初始Mapr(四)——准备每个节点
- HashMap的工作原理
- 发布v1.0.0.0 酷袋
- WITH AS and materialize hints
- unity3d学习笔记(四)--自定义角色控制脚本及平滑转身
- centos 命令行补全
- iPhone网络开发(二)之缓存管理
- [Android] Android开发优化之——对界面UI的优化(3)
- 脉宽调变的基本原理及其应用实例
- iPhone网络开发之如何使用NSURLConnection(一)
- qooxdoo框架环境搭建+登录页面
- 初始Mapr(五)——安装 Mapr 软件