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

在sql解析器中,在生成执行计划的时候,会在多个执行计划中选择最优的计划,在这个过程中,查询转换就是一个很重要的过程。 虽然最终的执行结果没有变化,但是从优化器的角度来看,查询转换的结果会更好。 大体来说查询转换有以下几种类型。 视图合并 子查询解嵌套 谓词推进 物化视图查询重写 比如 select *from emp where deptno in (select deptno from dept) 会在查询转换中转换为下面的样子. select e.* from emp e,dept d where e.deptno=d.deptno; 从查询转换的角度来说,两者的查询效果是基本一致的。 --视图合并 select * from emp e, (select deptno from emp) s_view where e.deptno=s_view.deptno;

70 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3638257876 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 71 | 2911 | 5 (20)| 00:00:01 | |* 1 | HASH JOIN | | 71 | 2911 | 5 (20)| 00:00:01 | | 2 | TABLE ACCESS FULL| EMP | 14 | 532 | 2 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| EMP | 14 | 42 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("E"."DEPTNO"="DEPTNO")

来比较一下是否视图合并的结果。 select * from emp e, (select /*+no_merge*/ deptno from emp) s_view where e.deptno=s_view.deptno

70 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2243607326 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 71 | 3621 | 5 (20)| 00:00:01 | |* 1 | HASH JOIN | | 71 | 3621 | 5 (20)| 00:00:01 | | 2 | TABLE ACCESS FULL | EMP | 14 | 532 | 2 (0)| 00:00:01 | | 3 | VIEW | | 14 | 182 | 2 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL| EMP | 14 | 42 | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("E"."DEPTNO"="S_VIEW"."DEPTNO")

可以比较上面两个查询的时候,会发现第二个执行计划的VIEW关键字表示,执行计划是保持原样的,会单独处理视图。 使用圆括号把子查询括起来不一定在sql执行的时候会保证子查询块会单独执行。 这个时候可以根据要求来选择是否选择视图合并。 视图合并在大体来说性能还是不错的,不过在使用order by, 聚合函数,分析函数,group等等的时候,视图合并的功能还是会受到限制或者无法启用。 而且在试图合并的功能受到限制的时候,如果硬要启用视图合并功能,可能查询的结果会有一些变化,这个时候需要你来判断是否确定要启用视图合并。 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;

12 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 269884559 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 546 | 6 (34)| 00:00:01 | |* 1 | HASH JOIN | | 14 | 546 | 6 (34)| 00:00:01 | | 2 | VIEW | | 3 | 78 | 3 (34)| 00:00:01 | | 3 | HASH GROUP BY | | 3 | 21 | 3 (34)| 00:00:01 | | 4 | TABLE ACCESS FULL| EMP | 14 | 98 | 2 (0)| 00:00:01 | |* 5 | TABLE ACCESS FULL | EMP | 13 | 169 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("E"."DEPTNO"="V"."DEPTNO") 5 - filter("E"."SAL">1000)

select /*+ merge(v)*/ 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;

12 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 4115741206 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 68 | 2176 | 6 (34)| 00:00:01 | | 1 | HASH GROUP BY | | 68 | 2176 | 6 (34)| 00:00:01 | |* 2 | HASH JOIN | | 68 | 2176 | 5 (20)| 00:00:01 | |* 3 | TABLE ACCESS FULL| EMP | 13 | 325 | 2 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL| EMP | 14 | 98 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------

Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("E"."DEPTNO"="DEPTNO") 3 - filter("E"."SAL">1000)

--子查询解嵌套 子查询嵌套和视图合并的不同之处在于视图合并是在select xxx from的部分,而子查询嵌套式在where xxxx的部分。 select *from emp where deptno in (select deptno from dept) 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 532 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 14 | 532 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("DEPTNO" IS NOT NULL)

先来比较一下是否启用子查询嵌套的区别 ,如果不需要启用子查询嵌套,可以使用Hint no_unnest来引导。 select *from emp where deptno in (select /*+ no_unnest*/deptno from dept) 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1783302997 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 5 | 190 | 2 (0)| 00:00:01 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL| EMP | 14 | 532 | 2 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 3 | 0 (0)| 00:00:01 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "DEPT" "DEPT" WHERE "DEPTNO"=:B1)) 3 - access("DEPTNO"=:B1)

可以从谓语部分看出来,两个的执行计划还是有很大的不同,第二个执行计划是未启用子查询解嵌套的形式。会在子查询中走索引,然后通过exists子句来和外部查询的deptno字段连接。 而第一个执行计划直接走了一个全表扫描,对于deptno的部分直接使用条件deptno is not null来过滤了。 select *from emp outer where outer.sal>(select avg(inner.sal) from emp inner where inner.deptno=outer.deptno) 6 rows selected.

Execution Plan ---------------------------------------------------------- Plan hash value: 1245077725 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 64 | 6 (34)| 00:00:01 | |* 1 | HASH JOIN | | 1 | 64 | 6 (34)| 00:00:01 | | 2 | VIEW | VW_SQ_1 | 3 | 78 | 3 (34)| 00:00:01 | | 3 | HASH GROUP BY | | 3 | 21 | 3 (34)| 00:00:01 | | 4 | TABLE ACCESS FULL| EMP | 14 | 98 | 2 (0)| 00:00:01 | | 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("ITEM_1"="OUTER"."DEPTNO") filter("OUTER"."SAL">"AVG(INNER.SAL)")

这个例子中子查询转换为了一个内嵌式图,然后与外部的查询合并连接,相关列成为了连接条件,子查询的剩余部分成为了内嵌视图。 sql的执行方式和下面的sql类似。 select *from emp outer, (select inner.deptno,avg(inner.sal) avg_sal from emp inner group by inner.deptno)inner where inner.deptno=outer.deptno and outer.sal>inner.avg_sal;

6 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 269884559 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 64 | 6 (34)| 00:00:01 | |* 1 | HASH JOIN | | 1 | 64 | 6 (34)| 00:00:01 | | 2 | VIEW | | 3 | 78 | 3 (34)| 00:00:01 | | 3 | HASH GROUP BY | | 3 | 21 | 3 (34)| 00:00:01 | | 4 | TABLE ACCESS FULL| EMP | 14 | 98 | 2 (0)| 00:00:01 | | 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("INNER"."DEPTNO"="OUTER"."DEPTNO") filter("OUTER"."SAL">"INNER"."AVG_SAL") 关于谓词推进和物化视图的部分,稍后补充。

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

原文发表时间:2014-10-26

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏黑泽君的专栏

day43_Oracle学习笔记_02

692
来自专栏乐沙弥的世界

当心外部连接中的ON子句

       在SQL tuning中,不良写法导致SQL执行效率比比皆是。最近的SQL tuning中一个外部连接写法不当导致过SQL执行时间超过15分钟左右...

1574
来自专栏乐沙弥的世界

SQL 基础--> 视图(CREATE VIEW)

视图来源于表,所有对视图数据的修改最终都会被反映到视图的基表中,这些修改必须服从基表的完整性约束,并同样会触发定义

723
来自专栏数据和云

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

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

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

生产环境sql语句调优实战第四篇(r2笔记41天)

生产中有一条sql语句消耗了大量的cpu资源,执行时间在18秒左右, Session:PRODBUSER (1560:61133)SQL ID:1hg2wcua...

2155
来自专栏乐沙弥的世界

Oracle 监控索引的使用率

    Oracle提供了索引监控特性来判断索引是否被使用。在Oracle 10g中,收集统计信息会使得索引被监控,在Oracle 11g中该现象不复存在。尽管...

1663
来自专栏开发与安全

Mysql数据库学习(三):表的crud操作、完整性约束、select各种查询

一、表的crud操作 指增加(Create)、查询(Retrieve)(重新得到数据)、更新(Update)和删除(Delete) // select 查询后面...

2360
来自专栏乐沙弥的世界

SQL 基础--> ROLLUP与CUBE运算符实现数据汇总

--=============================================

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

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

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

2794
来自专栏数据和云

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

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

3506

扫码关注云+社区

领取腾讯云代金券