性能优化之查询转换 - 子查询类

作者简介

韩锋

精通包括Oracle、MySQL、informix等多种关系型数据库,有丰富的数据库架构设计开发经验。就职于宜信。

子查询,是SQL中常见的一种写法。对于优化器来说,子查询是较难优化的部分。Oracle提供了多种方式,对子查询进行查询转换。

1

子查询推进

子查询推进(又称子查询推入)是指优化器将子查询提前进行评估,使得优化器可以更早地介入优化以获得更优质的执行计划。这个技术可以通过提示PUSH_SUBQ/NO_PUSH_SUBQ控制。下面通过一个示例看看结果。

SQL> create table t_users as select * from dba_users; //表已创建 SQL> create index idx_user_created on t_users(created); //索引已创建 SQL> create table t_objects as select * from dba_objects; //表已创建

执行以下语句:

SQL> select /*+ no_push_subq(@inv)*/ /*hf1*/ * from t_objects u where created > (select /*+ qb_name(inv)*/ max(created) from t_users );

执行计划如下:

注:在这个语句中,我们通过提示强制不使用子查询推进技术。由执行计划可见,执行是按照T_OBJECTS和T_USRES进行的一个索引的嵌套循环。

使用子查询推进:

SQL> select /*hf2*/ * from t_objects u where created > ( select /*+ qb_name(inv)*/ max(created) from t_users );

执行计划如下:

在这个示例中,Oracle使用了子查询推入技术,且可以在OutLine中看到PUSH_SUBQ字样。从执行计划可见,没有出现两表关联,提前处理了子查询,生成MAX CREATED,然后全表扫描T_OBJECTS进行条件过滤,显然这种方式效率更高。

2

子查询解嵌套、展开

子查询解嵌套是指优化器将子查询展开,和外部的查询进行关联、合并,从而得到更优的执行计划。可以通过UNNEST/NO_UNNEST提示控制是否进行解嵌套。采用这种技术通常可以提高执行效率,原因是如果不解嵌套,子查询往往是最后执行的,作为FILTER条件来过滤外部查询;而一旦展开,优化器就可以选择表关联等更高效的执行方式,以提高效率。下面通过几个示例说明各种解嵌套的形式。

先看第一个示例:

SQL> create table t_tables as select * from dba_tables; Table created. SQL> select * from t_objects o where exists(select /*+ qb_name(inv)*/ 1 from t_tables t where t.owner=o.owner and t.table_name=o.object_name);

执行计划如下:

在这个示例中,对EXISTS的子查询进行了解嵌套,然后选择了半连接(SEMI JOIN)的关联方式。

再来看一个示例。

SQL> select * from t_objects o where not exists (select /*+ qb_name(inv)*/ 1 from t_tables t where t.owner=o.owner and t.table_name=o.object_name);

执行计划如下:

在这个示例中,对NOT EXISTS的子查询进行了解嵌套,然后选择了反连接(ANTI JOIN)的关联方式。

3

子查询分解

子查询分解是由WITH创建的复杂查询语句并存储在临时表中,可按照与一般表相同的方式使用该临时表的功能。这种方式可以把一个复杂的查询分成很多简单的部分,并让优化器去决定是产生中间数据集还是构建该查询复杂的扩展形式并对其进行优化。这种方式的优点在于,使用WITH子句的子查询在复杂查询语句中只需要执行一次,但结果可以在同一个查询语句中被多次使用。缺点在于,这种方式不允许语句变形,所以无效的情况较多。

下面看一个示例。

SQL> with user_obj as (select owner,count(*) cnt from t_objects group by owner) select u.user_id,u.username,o.cnt from t_users u,user_obj o where u.username=o.owner;

子查询定义为user_obj,在执行计划中以一个视图的形式(ID=2的步骤)出现,并与T_USRES进行了哈希关联。

上述过程并没有生成临时表,可通过一个提示materialize强制优化器创建临时表。

SQL> with user_obj as (select --+ materialize owner,count(*) cnt from t_objects group by owner ) select u.user_id,u.username,o.cnt from t_users u,user_obj o where u.username=o.owner;

执行计划如下:

引入了materialize提示后,由ID=2步骤可见,系统生成了一个临时表SYS_TEMP_XXX,并由这个表在后面与T_USERS进行了关联查询。

4

子查询合并

在语义等价的前提下,如果多个子查询产生的结果集相同,则优化器可以使用这种技术将多个子查询合并为一个子查询。这样的好处在于减少多次扫描产生的开销。可以通过NO_COALESCE_SQ/COALESCE_SQ提示来控制。下面看个示例:

select /*+ qb_name(mn)*/ t.* from t_tables t where exists (select /*+ qb_name(sub1)*/ 1 from t_tablespaces ts where t.tablespace_name=ts.tablespace_name and ts.block_size=8) and exists (select /*+ qb_name(sub2)*/ 1 from t_tablespaces ts where t.tablespace_name=ts.tablespace_name);

执行计划如下:

在这个查询中,外部对T_TABLES表的查询要同时满足SUB1和SUB2两个子查询,而SUB1在语义上又是SUB2的子集,因此优化器将两个子查询进行了合并(只进行一次对T_TABLESPACES表的扫描),然后与外部表T_TABLES进行半连接。

那么如果语义不等价又会怎么样呢?

select /*+ qb_name(mn)*/ t.* from t_tables t where exists (select /*+ qb_name(sub1)*/ 1 from t_tablespaces ts where t.tablespace_name=ts.tablespace_name and ts.block_size=8) and exists (select /*+ qb_name(sub2)*/ 1 from t_tablespaces ts where t.tablespace_name=ts.tablespace_name and ts.block_size=16);

执行计划如下:

在这个查询语句中,外部查询要满足两个子查询—SUB1和SUB2,但两者条件不同,不能简单合并。因此在执行计划中,分别对两者进行了扫描(直观感觉就是对T_TABLESPACES进行了两次扫描),然后再做关联查询。

5

子查询实体化

子查询实体化是指在上面WITH定义的查询中,将查询结果写入一张临时表中,后续的查询直接利用临时表中的数据。可以通过MATERIALIZE提示来控制。下面看个示例。

SQL> with v as (select /*+ MATERIALIZE */ * from t_users where username='SYS') select count(*) from v;

执行计划如下:

在ID=2的步骤中生成了一张临时表SYS_TEMP_xxx,并且这个临时表在后面会被直接使用。如果去掉提示会怎样呢?

SQL> with v as (select * from t_users where username='SYS') select count(*) from v;

执行计划如下:

此时不再生成临时表,直接解嵌套执行。

-----the end

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2016-12-27

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏C/C++基础

C++特性使用建议

使用引用替代指针且所有不变的引用参数必须加上const。在C 语言中,如果函数需要修改变量的值,参数必须为指针,如int foo(int *pval),在 C+...

853
来自专栏企鹅号快讯

给初学者:JavaScript 的常见注意点

作者: CarterLi 原文:https://segmentfault.com/a/1190000012730162 上篇说了一些 JS 中数组操作的常见误区...

1786
来自专栏程序员互动联盟

java到底和C++有啥区别?

作为一名C++程序员,我们早已掌握了面向对象程序设计的基本概念,而且Java的语法无疑是非常熟悉的。事实上,Java本来就是从C++衍生出来的。 然而,C++和...

3246
来自专栏程序你好

C# 发展历史及版本新功能介绍

622
来自专栏desperate633

Python爬虫之信息标记与提取(XML&JSON&YAML)信息标记信息标记的种类信息提取基于bs4的html信息提取的实例小结

<tag>(..) 等价于 <tag>.find_all(..) soup(..) 等价于 soup.find_all(..)

581
来自专栏喵了个咪的博客空间

zephir-(6)运算符

#zephir-运算符# ? ##前言## 先在这里感谢各位zephir开源技术提供者 了解的动态变量和静态变量之后我们今天来了解一下在编码工作中至关重要的运算...

3489
来自专栏PHP技术

PHP程序员容易忽略的几点精华

1、变量、数组的应用技巧   (1)很多人用得不多的数组函数。foreach、list、each。分别举几个例子,应该就能知道了。例:   $dat...

33310
来自专栏Android机动车

Java 基础(二)——集合源码解析 Iterator

针对一个特定的问题,如果事先不知道需要多少个对象,或者它们的持续时间有多长,那么也不知道如何保存那些对象。既然如此,怎样才能知道那些对象要求多少空间呢?事先上根...

713
来自专栏带你撸出一手好代码

浅谈 var 关键字

提起 var关键子,程序员的第一反应就是JavaScript, 事实上这个关键子在其他语言中也有被采用。 比如说C#, 比如说kotlin, 用法和JavaSc...

2618
来自专栏用户2442861的专栏

Python标准库11 时间与日期 (time, datetime包)

作者:Vamei 出处:http://www.cnblogs.com/vamei 欢迎转载,也请保留这段声明。谢谢!

502

扫描关注云+社区