Oracle表查询变换优化思路

ben@dw

已于 2022-08-13 11:22:31 修改

阅读量885

点赞数

分类专栏： Oracle SQL优化文章标签： oracle sql 数据库

于 2022-08-12 16:54:48 首次发布

本文链接：https://blog.csdn.net/xiaobinhxb/article/details/126307307

版权

Oracle SQL优化专栏收录该内容

4 篇文章 2 订阅

订阅专栏

一、子查询非嵌套
子查询非嵌套（Subquery Unnesting）：当where子查询中有in、not in、exists、not exists等，CBO会尝试将子查询展开（unnest），从而消除FILTER，这个过程叫作子查询非嵌套。子查询嵌套也就是子查询固化。
SQL及执行计划：

SELECT E.ENAME, E.DEPTNO
  FROM SCOTT.EMP E
 WHERE EXISTS (SELECT DEPTNO
          FROM SCOTT.DEPT D
         WHERE D.DNAME = 'CHICAGO'
           AND E.DEPTNO =D.DEPTNO
        UNION
        SELECT DEPTNO
          FROM SCOTT.DEPT D
         WHERE D.LOC = 'CHICAGO'
           AND D.DEPTNO = E.DEPTNO)

请添加图片描述

上述SQL改写（1）如下：

SELECT E.ENAME, E.DEPTNO
  FROM SCOTT.EMP E
 WHERE EXISTS (select 1
          from (SELECT DEPTNO
                  FROM SCOTT.DEPT D
                 WHERE D.DNAME = 'CHICAGO'
                UNION
                SELECT DEPTNO
                  FROM SCOTT.DEPT D
                 WHERE D.LOC = 'CHICAGO') a
         where a.DEPTNO = e.DEPTNO)

在这里插入图片描述

上述SQL改写（2）如下：

SELECT E.ENAME, E.DEPTNO
  FROM SCOTT.EMP E
 WHERE E.DEPTNO in (SELECT DEPTNO
                      FROM SCOTT.DEPT D
                     WHERE D.DNAME = 'CHICAGO'
                    UNION
                    SELECT DEPTNO
                      FROM SCOTT.DEPT D
                     WHERE D.LOC = 'CHICAGO')

在这里插入图片描述
对SQL进行等价改写后，消除了FILTER。为什么要消除FILTER呢？因为FILTER的驱动表是固定的，一旦驱动表被固定，那么执行计划也就被固定了。一旦固定的执行计划本身是错误的（低效的），就会引起性能问题。
如何才能产生FILTER？只需在子查询中添加/+no_unnest/。注意虽然可以在子查询中添加HINT（NO_UNNEST）来产生FILTER，但是执行计划中的FILTER很少能够通过HINT消除，一般需要通过SQL等价改写来消除。虽然一直强调要消除执行计划中的FILTER，本意是要保证执行计划的可控性，并不意味着执行计划产生了FILTER就一定性能差，相反有时候可以用FILTER来优化SQL。
1、那些SQL写法容易产生FILTER？
当查询语句包含有exists或者not exists时，子查询中有固化子查询关键词（union/union all/start with connect by/rownum/cube/rollup/order by ），那么执行计划中容易产生FILTER。
2、为什么EXISTS/NOT EXISTS容易产生FILTER，而IN很少会产生FILTER？
当子查询中有固化关键字（union/union all/start with connect by/rownum/cube/rollup），子查询会被固化为一个整体，采用exists或者not exists这种写法，这时子查询中有主表连接列，只能是主表通过连接列传值给指标，所以CBO只能选择FILTER。而如果将SQL改成in或者not in这种写法，子查询虽然被固化为整体，但是子查询中没有主表连接列字段，这时CBO就不会选择FILTER。
所以推荐在Oracle中使用IN而不是使用exists，因为exists子查询中有固化子查询关键字会自动走Filter，想要消除Filter只能改写SQL。in可以控制走Filter或者不走，in执行计划可控，而exists执行计划不可控。
3、如何避免产生FILTER？
当采用exists或者not exists这种写法，子查询不要出现固化关键词；或者采用in/not in 写法。
4、IN子查询什么时候走FILTER，什么时候不走FILTER？
对于IN子查询，一定要搞清楚IN子查询返回的数据量，究竟起到多大的过滤作用。如果IN子查询能过滤掉主表大量数据（主表连接列基数比较高），即主表返回数据量比较小，这些一定让IN子查询展开并且作为NL驱动表反向驱动主表，主表作为NL被驱动表走连接列索引。如果IN子查询不能过滤掉主表大量数据，即主表返回数据量比较大，这时要检查IN子查询返回的数据量，如果返回数据量很少，in子查询即使不展开，走FILTER也不大会影响SQL性能。如果IN子查询返回数据量很大，但是并不能过滤掉主表大量数据，即主表返回数据量比较大，这时一定让IN子查询展开并且与主表走HASH连接。

    SELECT /*+LEADING(T@A) USE_NL(T@A,T)*/
 GCODE, NAME, IDCODE
  FROM ZHXX_LGY.LY_T_CHREC T
 WHERE GCODE IN (SELECT /*+QB_NAME(A)*/
                  GCODE
                   FROM ZHXX_LGY.LY_T_CHREC t
                  WHERE NAME = '张三'
                    AND BDATE = '19941109')

5、IN和EXISTS全部改成WITH AS
如果这个TEST100W，TEST2 1千条。这时候就用IN 里面的去反向驱动外面的大表。如果这时你改
成EXISTS。而且EXISTS里面还有关键字被固化了，怎么办？改成IN ，或者WITH AS
select object_id,owner from test where object_id in (select object_id from test2);
TEST 100W,TEST 2 1000条，这时应该TEST2驱动TEST表对吧。
select object_id,owner from test where object_id in (很复杂的很恶心的 sql但返回数据不大) 这种怎么办？
本来正常情况下应该是IN里面的结果去驱动外面的，但IN里面太复杂了。咋搞？
这时候是不是很有可能subquery unnesting展开了。展开后就是IN里面一部分的表和外面的TEST关联。这时候就完全搞乱了。FROM 。。。。后8个表，是不是原本应该一个一个来关联。但子查询展开了，就全部乱了。所以这个时候写SQL应该写成WITH AS 把它固化了。以后遇到IN和EXISTS全部改成WITH AS ，
这样遇到性能问题加一个/+materialize/ 的HINT就解决了。

二、视图合并
视图合并：当SQL语句中有内联视图（in-line view，from后面的子查询），或者SQL语句中有用create view创建视图，CBO会尝试将内联视图/视图拆开，进行等价改写，这个过程叫视图合并。如果没有发生视图合并，在执行计划中，可以看到VIEW关键字，而且视图/子查询会作为一个整体。如果发生了视图合并，那么视图/子查询就会被拆开，而且执行计划中视图/子查询部分就没有VIEW关键字。
当视图/子查询中有多个表关联，发生视图合并之后一般会将视图/子查询内部表关联顺序打乱。可以添加HINT:no_merge禁止视图合并，也可以让子查询a与子查询b进行hash连接，当子查询a与子查询b进行HASH连接之后，就不会发生视图合并。
select /+no_merge(a)/ * from tab a,() b where a.id =b.id
select /+use_hash(a,b)*/ * from tab a,() b where a.id = b.id
与子查询非嵌套一样，当视图中有固化子查询关键字的时候，就不能发生视图合并。
固化子查询的关键字包括union、union all、start with connect by、rownum、cube、rollup。

1、视图合并后执行慢处理方法
如果看到这种SQL select … from ()a ()b 是不是in-line-view
首先就是看执行计划中有没有view关键字，如果没有VIEW表示这两个子查询都全部都展开了。
我们可以单独把a、b运行一下看慢不慢。如果我们单独跑a、b不慢。弄到一起就慢。这时就可以用
hint no_merge(a) no_merge(b) 让他们不展开。
如果无法写hint 怎么办？
这个时候我们可以在from 后面可以加一个where rownum > 0是不是它就固化了。

三、谓词推入
谓语推入（Pushing Predicate）:当SQL语句包含不能合并的视图，同时视图有谓词过滤（也就是where过滤条件），CBO会将谓词过滤条件推入视图中，这个过程叫谓词推入。谓词推入的主要目的就是让Oracle尽可能早地过滤掉无用的数据，从而提升查询性能。
在看执行计划的时候，如果VIEW前面有“*”号，这就说明有谓词没有推入到视图中。
谓词推入有两种：常量谓词推入和连接列谓词推入。

举例：
select … from a,v_b where a.id=v_b.id;a是一个表， v_b 是一个视图，里面有很多的表关联 a nl b
for a 返回数据总条数循环
select … from v_b where v_b.id=a传入的值
end;
这种是不是推入了N次？a返回了多少条数据，你就要推入多少次。
这样的话发生谓词推入没有？是不是有谓词推入。大家觉得这种谓词推入是有害还是无害的？
答：当然是有害的，而且大大的有害。
v_b是不是一个视图，它里面是不是有很多的SQL来关联，然后它又是当的NL的被驱动表。也就是V_B要被执行很多次。也就是说里面关联的SQL也要执行很多次。一般来说这种谓词推入就是有害的。
提问：什么样的谓词推入是有害的？
视图作为嵌套循环的被驱动表是有害的。

四、让半连接和反连接作驱动表

从 sql的角度
with … as (子查询)
2、从hint角度
（1）in 里面只有 1个表，in 的子表驱动外面的大表只要写个leading(子表) 就行
（2）select … from 大表 where id in (select … fro a,b ,c where …关联后返回10条)
in …里面的去驱动大表，只能改with as ，因为有三个表
with as (select /*+ materialize */…)