oracle执行计划结果分析_Oracle实验--从五个SQL探讨with as如何使用

概述

SQL语句是关系型数据库最直接也是最普遍的数据访问、操作方式。应该说,RDBMS的成功因素之中,SQL语言标准的推广有不可磨灭的功劳。Oracle、SQL Server和DB2等主流数据库都是在标准SQL的基础上,加以个性化拓展,实现高效的数据库应用。

with as是Oracle SQL语系里面经常使用到的一种语句结构。特别是在一些数据仓库挖掘系统中,SQL语句结构和嵌套结构是很复杂的。with as可以帮助我们简化语句结构,提高语句可读性,另外还可以提升语句执行计划控制性。

下面我们通过五个SQL语句,分析其执行计划看with as特性。


01

环境介绍

这里我选择Oracle 11gR2进行实验,实验schema为scott。

SQL> select * from v$version; SQL> select table_name from user_tables;
26c107d0556302716d850180e0776956.png

从语法上看,with as就是将SQL语句中单独的子查询语句提取出来,作为一个单独的命名进行组织。在原来的语句中,就可以使用新的别名来替代。

SQL> select count(*) from (select emp.empno empno, dept.dname from emp, dept where emp.deptno=dept.deptno);SQL> with a as (select emp.empno empno, dept.dname from emp, dept where emp.deptno=dept.deptno) select count(*) from a;
5a3bd2a04595156e40c7908c9c7c1fbf.png

02

语句分析

在很多文档中,都介绍了with as的用途中有一条是数据临时表化。当我们在一个SQL中包括子查询的时候,Oracle可能不会按照“子查询=》主查询”的顺序执行语句的。SQL Transformation过程中有一个子查询展开(subquery unnested)。一般的子查询都会被展开,作为一系列的join动作出现在执行计划中。with as所谓的数据临时表化,就是支持将subquery先进行执行,形成内存中的临时表,再进行后续操作。

下面是我们第一个SQL语句,是一个子查询与一个数据表的连接。

SQL> explain plan for with a as (select emp.empno, emp.ename, dept.loc, emp.job from emp, dept where emp.deptno=dept.deptno) select * from a m, dept n where m.loc=n.loc;SQL> select * from table(dbms_xplan.display);
5a43da631ae0fc4cbf4833487490c9c6.png

上面SQL中,将子查询整理在with as中,以a的别名出现在系统中。在主查询里面,a与dept进行连接。从执行计划看,虽然有用with as组织,Oracle优化器CBO还是将子查询展开了,作为一系列的join操作。由此:在单次使用with as的情况下,临时表预执行动作是不会进行的。

下面语句,我们同样抽取a,主查询中两次使用a查询结果。

SQL> explain plan for with a as (select emp.empno, emp.ename, dept.loc, emp.job from emp, dept where emp.deptno=dept.deptno) select * from a m, a n where m.loc=n.loc;SQL> select * from table(dbms_xplan.display);
0207378ed490cb97dcb02f7bff26e9bf.png

执行计划出现了变化。最典型的是在之前,使用Temp Table Transformation,将子查询进行预加载,形成内存中的SYS_TEMP_0FD9D6621_4FA3D74临时表。之后,对这个临时表进行join连接操作。这也就是我们经常看到的对于with as预加载的现象。

对比之前的实验,我们可以知道:只有在主查询中超过一次使用with as中的子查询结构,Oracle才会“自动”的进行临时表预加载。

这样的处理也是有道理的。子查询和查询嵌套主要是便于可读性提升上,绝大多数的子查询和嵌套,都可以通过一系列的连接来完成。而数据库系统是擅长连接操作的。所以,从CBO角度看,大多数子查询被打开,构成系列的连接动作。

此外,子查询单独执行还存在临时空间占用的问题。如果进行子查询单独执行,临时结果的缓存要占用额外的空间,成本消耗相对较高。

但是如果在主查询中多次使用with as确定的别名,情况就不一样了。缓存一部分数据,要比重新执行一遍或者多次连接子查询数据表要强得多。所以,当多次进行with as子循环检索的时候,Oracle会自动的进行临时表化。

下面我们看第三个SQL语句。

SQL> explain plan for with a as (select /*+MATERIALIZE*/emp.empno, emp.ename, dept.loc, emp.job from emp, dept where emp.deptno=dept.deptno) select * from a m, dept n where m.loc=n.loc;SQL> select * from table(dbms_xplan.display);
06f124cfe60b1e70c95222da88a5f5b0.png

这个SQL中,我们使用了with as,在主查询中只引用了一次a。但是,在执行计划中还是出现了临时表加载计算动作。区别就在于在子查询中使用了materialize的hint。

with as和materialize是经常匹配出现的。如果with as不满足临时表加载动作条件(引用两次),但是使用了materialize标记的话,Oracle还是会生成对应的临时表加载执行计划的。

如果不使用with as子句,而是直接使用materialize hint的话,会不会也出现临时表加载动作呢?

SQL> explain plan for select * from (select /*+MATERIALIZE*/emp.empno, emp.ename, dept.loc, emp.job from emp, dept where emp.deptno=dept.deptno) m, (select /*+MATERIALIZE*/emp.empno, emp.ename, dept.loc, emp.job from emp, dept where emp.deptno=dept.deptno) n where m.loc=n.loc;SQL> select * from table(dbms_xplan.display);
eeb10e329882065c245c2ea5afdb5f22.png

单独使用materialize hint,是不会起到临时表作用的。with as可以支持多个别名块创建动作,看第五个SQL。

SQL> explain plan for with a as (select /*+MATERIALIZE*/emp.empno, emp.ename, dept.loc, emp.job from emp, dept where emp.deptno=dept.deptno), b as (select /*+MATERIALIZE*/emp.empno, emp.ename, dept.loc, emp.job from emp, dept where emp.deptno=dept.deptno) select * from a m, b n where m.loc=n.loc; SQL> select * from table(dbms_xplan.display);
5d01eac84695050202f33c04e3596c21.png

总结

综合上面的实验测试,我们可以得到with as应用的条件和好处。总体上看,with as可以带来两方面的好处。第一是语句清晰度上,将原有的复杂子查询内容拆解为若干语句块,提高了可读性。第二是对于自动临时表,可以将子查询数据先检索出来,在内存中进行组织连接。

最后我们想讨论一下对于subquery unnested特性。大多数情况下,我们认为Oracle这个特性是可以生成更好地执行计划的。但是,在实践中我们也的确发现过很多这样的场景:子查询速度很快、数据量也不大,但是连接之后速度变慢,结果是发现执行计划中子查询被打开。

默认情况下,Oracle在估算多条件语句结果集数量的时候,是有“计算偏小”的特性的。也就是估算的结果集数量要小于实际数量,这个是由于默认统计量没有考虑列相关性的原因。所以,大部分查询展开都是被通过的。如果出现这样的情况,with as + materialize是一种强制进行子查询先执行的好办法。

后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注一下~

1b1fa2d21a786ab3c7ef0a7c9e4aa078.gif
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值