关于查询转换的一些简单分析(三) (r3笔记第69天)

关于查询转换,已经讨论了视图合并和子查询解嵌套,还有谓词推进和物化视图查询重写也是查询转换中不可或缺的部分。 -->谓词推进 这个术语听起来高大上,有点故弄玄虚的味道。其实在我们的查询中已经潜移默化的使用到了。 比如下面的这个查询。我们定义了一个子查询 v,然后在外部的过滤条件中加入了过滤条件e.deptno=10 因为e.deptno=v.deptno 所以过滤条件也可以理解为v.depetno=10

select e.ename,e.sal,v.avg_salary
from emp  e,
(select deptno,avg(sal) avg_salary
from emp oe
group by  deptno)v
where e.deptno=v.deptno and e.sal>1000
and e.deptno=10

Execution  Plan
----------------------------------------------------------
Plan hash  value: 269884559
-----------------------------------------------------------------------------
|  Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time      |
-----------------------------------------------------------------------------
|    0 | SELECT STATEMENT     |      |     3 |   117 |     5  (20)| 00:00:01 |
|*   1 |  HASH JOIN           |      |     3 |   117 |     5  (20)| 00:00:01 |
|    2 |   VIEW               |      |     1 |    26 |     2   (0)| 00:00:01 |
|    3 |    HASH GROUP BY     |      |     1 |     7 |     2   (0)| 00:00:01 |
|*   4 |     TABLE ACCESS FULL| EMP  |     3 |    21 |     2   (0)| 00:00:01 |
|*   5 |   TABLE ACCESS FULL  | EMP  |     3 |    39 |     2   (0)| 00:00:01  |
-----------------------------------------------------------------------------
Predicate Information (identified by operation  id):
---------------------------------------------------
 1 - access("E"."DEPTNO"="V"."DEPTNO")
   4 - filter("DEPTNO"=10)
    5 - filter("E"."DEPTNO"=10 AND "E"."SAL">1000)

从上面的执行计划可以看到执行计划第2,3,4行是子查询v 相关的,第4行中的过滤条件,直接在子查询中嵌入了过滤条件depetno=10。 这种情况就是使用了谓词推进。 谓词推进在查询中都是自动调用的。但是如果碰到了rownum,算是它的死穴。我们来看看使用了rownum之后的执行计划。

select  e.ename,e.sal,v.avg_salary
from emp e,
(select deptno,avg(sal)  avg_salary
from emp oe where rownum<100
group by deptno  )v
where e.deptno=v.deptno and e.sal>1000
and e.deptno=10

Execution  Plan
----------------------------------------------------------
Plan hash  value: 1823415238
------------------------------------------------------------------------------
|  Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time      |
------------------------------------------------------------------------------
|    0 | SELECT STATEMENT      |      |     9 |   351 |     6  (34)| 00:00:01  |
|*  1 |  HASH JOIN            |      |     9 |   351 |     6  (34)|  00:00:01 |
|*  2 |   TABLE ACCESS FULL   | EMP  |     3 |    39 |     2    (0)| 00:00:01 |
|*  3 |   VIEW                |      |     3 |    78 |     3   (34)| 00:00:01 |
|   4 |    HASH GROUP BY      |      |     3 |    21 |      3  (34)| 00:00:01 |
|*  5 |     COUNT STOPKEY     |      |       |        |            |          |
|   6 |      TABLE ACCESS FULL| EMP  |    14 |     98 |     2   (0)| 00:00:01  |
------------------------------------------------------------------------------
Predicate Information (identified by operation  id):
---------------------------------------------------
 1 - access("E"."DEPTNO"="V"."DEPTNO")
   2 - filter("E"."DEPTNO"=10 AND  "E"."SAL">1000)
 3 -  filter("V"."DEPTNO"=10)
   5 - filter(ROWNUM<100)

这个执行计划中,因为使用了rownum,所以就没有使用到谓词推进。 对于谓词推进也可以考虑使用hint push_pred来实现。 -->查询重写

对于物化视图有几个主要的两点用得比较多,查询重写和视图刷新。 查询重写在查询转换中还是比较实用的。如果有海量的数据,更新频率不高。可以根据查询条件来定义物化视图,开启查询重写的功能。 创建一个物化视图。

create  materialized view mv_emp enable query  rewrite 
as
select e.ename,e.sal,v.avg_salary
from emp  e,
(select deptno,avg(sal) avg_salary
from emp oe 
group by deptno  )v
where e.deptno=v.deptno and e.sal>1000
and  e.deptno=10

然后使用查询,执行计划如下:

Execution  Plan
----------------------------------------------------------
Plan hash  value: 2244303076
---------------------------------------------------------------------------------------
|  Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)|  Time      |
---------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT             |        |     3 |    99 |     2   (0)|  00:00:01 |
|   1 |  MAT_VIEW REWRITE  ACCESS FULL| MV_EMP |     3 |    99 |     2   (0)| 00:00:01  |
---------------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-11-29

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

关于查询转换的一些简单分析(二) (r3笔记第68天)

关于查询转换的作用,在之前的一篇博文里面也大概说了视图合并和子查询嵌套的部分。http://blog.itpub.net/23718752/viewspace-...

291110
来自专栏乐沙弥的世界

高效SQL语句必杀技

        No SQL,No cost. SQL语句是造成数据库开销最大的部分。而不良SQL写法直接导致数据库系统性能下降的情形比比皆是。那么如何才能称得...

10520
来自专栏数据和云

深入解析:半连接与反连接的原理和等价改写方法

半连接的原理及等价改写 1. 什么是半连接 当两张表进行关联,只返回匹配上的数据并且只会返回一张的表的数据,半连接一般就是指的在子查询中出现 IN 和 EXI...

35470
来自专栏杨建荣的学习笔记

关于oracle中的反连接(r3笔记第95天)

在之前的章节中见到讨论过oracle中的半连接 http://blog.itpub.net/23718752/viewspace-1334483/ 与半连接相对...

22770
来自专栏杨建荣的学习笔记

关于表联结方法(二) (r4笔记第23天)

在比较经典的表联结方法中,nested loop join和hash join是比较常用的,对于sort-merge join来说,可能略微有些陌生。 在数...

29040
来自专栏java达人

mysql explain详解

对于经常使用mysql的兄弟们,对explain一定不会陌生。当你在一条SELECT语句前放上关键词EXPLAIN,MySQL解释它将如何处理SELECT,提供...

194100
来自专栏乐沙弥的世界

consistent gets减少,cost增加?

  在一条SQL语句中,当使用索引时,cosistent gets 减少,而cost增加。理论上在稳定后的执行计划中,physical reads为零值的前...

5210
来自专栏数据和云

突破常识:SQL增加DISTINCT后查询效率反而提高

杨廷琨,网名 yangtingkun 云和恩墨技术总监,Oracle ACE Director,ACOUG 核心专家 只要增加了DISTINCT关键字,Orac...

39660
来自专栏数据和云

明理知意:复合索引优化及索引访问原理

熊军(老熊) 云和恩墨西区总经理 Oracle ACED,ACOUG核心会员 这个案例发生在某天早上,运行在配置为128GB内存、64CPU的HP Superd...

30470
来自专栏杨建荣的学习笔记

一条简单的sql在11g和12c中的不同(r5笔记第2天)

今天在查看awr报告的时候,有一句很简单的sql语句引起了我的注意,因为它排在SQL Order by Reads的第2位。 Physical ReadsExe...

36340

扫码关注云+社区

领取腾讯云代金券