oracle 查询结果内容转换

发布网友发布时间：2022-04-09 16:57

共2个回答

懂视网时间：2022-04-09 21:18

连接谓词推入(Join Predicate Pushdown)是优化器处理带视图的目标SQL的一种优化手段，它是指虽然优化器会把该SQL中视图的定义SQL语句当作一个独立单元来单独执行，但此时优化器会把原本处于该视图外部查询中和该视图之间的连接条件推入到该视图的定义SQL语句内部，这样是为了能使用上该视图内部相关基表上的索引，进而能走出基于索引的嵌套循环连接。

连接谓词推入所带来的基于索引的嵌套循环连接并不一定能走出更高效的执行计划，因为当做了连接谓词推入后，原目标SQL中的视图就和外部查询产生了关联，同时Oracle又必须将该视图的定义SQL语句当作一个独立的处理单元单独执行，这也就意味着对于外部查询所在结果集中的每一条记录，上述视图的定义SQL语句都得单独执行一次，这样一旦外部查询所在的结果集的Cardinality比较大的话，即便在执行上述视图的定义语句时能用上索引，整个SQL的执行效率也不定比不做连接谓词推入时的哈希连接或排序合并连接高。所以Oracle在做连接谓词推入时会考虑成本，只有当经过连接谓词推入后走嵌套循环连接的等价改写SQL的成本值小于原SQL的成本值时，Oracle才会对目标SQL做连接谓词推入。

Oracle是否能做连接谓词推入与目标视图的类型、该视图与外部查询之间的连接类型以及连接方法有关。到目前为止，Oracle仅仅支持对如下类型的视图做连接谓词推入。

视图定义SQL语句中包含UNION ALL/UNION的视图

视图定义SQL语句中包含DISTINCT的视图

视图定义SQL语句中包含GROUP BY的视图

和外部查询之间的连接类型是外连接的视图

和外部查询之间的连接类型是反连接的视图

和外部查询之间的连接类型是半连接的视图

看一个连接谓词推入的实例，创建测试表、相关索引和一个普通视图和一个带有UNION ALL的视图

scott@TEST>create table emp1 as select * from emp;

Table created.

scott@TEST>create table emp2 as select * from emp;

Table created.

scott@TEST>create index idx_emp1 on emp1(empno);

Index created.

scott@TEST>create index idx_emp2 on emp2(empno);

Index created.

scott@TEST>create or replace view emp_view as 
  2  select emp1.empno as empno1 from emp1;

View created.

scott@TEST>create or replace view emp_view_union as
  2  select emp1.empno as empno1 from emp1
  3  union all
  4  select emp2.empno as empno1 from emp2;

View created.

执行测试SQL

scott@TEST>select /*+ no_merge(emp_view) */ emp.empno
  2  from emp,emp_view
  3  where emp.empno=emp_view.empno1(+)
  4  and emp.ename=‘FORD‘;

     EMPNO
----------
      7902

在上面的SQL中，我们使用了no_merge hint是为了让Oracle不对视图EMP_VIEW做视图合并，这样就具备了做连接谓词推入的基本条件。这里外部查询和视图EMP_VIEW的连接条件为“emp.empno=emp_view.empno1(+)”，由于已经在视图EMP_VIEW的基表EMP1的列EMPNO上创建了索引IDX_EMP1，而且这里的连接类型又是外连接，根据前面的介绍，对于视图EMP_VIEW而言，所有能做连接谓词推入的条件都已具备，Oracle在执行上面的SQL时会考虑做连接谓词推入。如果做连接谓词推入，执行计划就会走嵌套循环外连接并且访问视图EMP_VIEW的基表EMP1时会使用列EMPNO上的索引IDX_EMP1。

技术分享

从执行计划上可以看出，Oracle在执行测试SQL时确实走的是嵌套循环外连接，并且访问视图EMP_VIEW的基表EMP1时用到了索引IDX_EMP1。而且Id=3的执行步骤上Name列的值是“EMP_VIEW”，Operation列的值是“VIEW PUSHED PREDICATE”。这说明Oracle确实没有对视图EMP_VIEW做视图合并，而是把它当作一个独立的执行单元来单独执行，并且把外部查询和视图EMP_VIEW之间的连接条件“emp.empno=emp_view.empno1(+)”推入到了视图的定义语句内部。

如果不做连接谓词推入，那Oracle在访问视图EMP_VIEW的基表EMP1时就只能做全表扫描了。在测试SQL中加入no_push_pred hint(让优化器不要对视图EMP_VIEW做连接谓词推入)再次执行

scott@TEST>select /*+ no_merge(emp_view) no_push_pred(emp_view) */ emp.empno
  2  from emp,emp_view
  3  where emp.empno=emp_view.empno1(+)
  4  and emp.ename=‘FORD‘;

     EMPNO
----------
      7902

技术分享执行计划已经变为了HASH JOIN OUTER，而且对EMP_VIEW的基表EMP1确实用的是全表扫描。

现在把测试SQL改一下，把EMP_VIEW用EMP_VIEW_UNION视图替换，并把连接类型改为内连接，再次执行

scott@TEST>select emp.empno
  2  from emp,emp_view_union
  3  where emp.empno=emp_view_union.empno1
  4  and emp.ename=‘FORD‘;

     EMPNO
----------
      7902
      7902

视图EMP_VIEW_UNION的定义SQL语句中包含UNION ALL，它本身就不能做视图合并，因而具备了做连接谓词推入的基本条件。这里外部查询和视图EMP_VIEW_UNION的连接条件为“emp.empno=emp_view_union.empno1”视图对基表上的EMPNO列都有索引，虽然这里的连接类型是内连接，但对于包含UNION ALL的视图EMP_VIEW_UNION而言，所有能作连接谓词推入的条件都已具备，意味着Oracle地执行上述SQL时做考虑做连接谓词推入。如果做连接谓词推入，那执行计划就会走嵌套循环连接，并且访问视图的基表会用上列EMPNO上的索引。

技术分享从执行计划中可以看出，Oracle走的执行计划与预想的一样。

在SQL中加入no_push_pred hint(让优化器不要对视图EMP_VIEW做连接谓词推入)再次执行

scott@TEST>select /*+ no_push_pred(emp_view_union) */emp.empno
  2  from emp,emp_view_union
  3  where emp.empno=emp_view_union.empno1
  4  and emp.ename=‘FORD‘;

     EMPNO
----------
      7902
      7902

技术分享从执行计划可以看出，不使用连接谓词推入，则对视图的基表做的是全表扫描。

之前提到过，Oracle在做连接谓词推入时会考虑成本，只有经过连接谓词推入后走嵌套循环连接的等价改写SQL的成本值小于原SQL的成本值时，Oracle才会对目标SQL做连接谓词推入。

现在来验证一下，在上面的SQL中加入cardinality hint，让CBO认为外围查询的结果集的Cardinality是1万，这样就会急剧增加做连接谓词推入后的嵌套循环连接的成本，如果Oracle在做连接谓词推入是确实会考虑成本，那么此时Oracle就一定不会再选择做连接谓词推入。

scott@TEST>select /*+ cardinality(emp 10000) */emp.empno
  2  from emp,emp_view_union
  3  where emp.empno=emp_view_union.empno1
  4  and emp.ename=‘FORD‘;

     EMPNO
----------
      7902
      7902

技术分享

scott@TEST>select /*+ cardinality(emp 10000) push_pred(emp_view_union) */emp.empno
  2  from emp,emp_view_union
  3  where emp.empno=emp_view_union.empno1
  4  and emp.ename=‘FORD‘;

     EMPNO
----------
      7902
      7902

技术分享从上面的测试可以看出使用cardinality hint后Oracle没有选择做连接谓词推入，此时的成本为10，使用push_pred强制做连接谓词推入，看到成本为20008。这也验证了之前说的Oracle在做连接谓词推入会考虑成本。

下面再看使用了内嵌视图且连接类型为外连接的示例：

scott@TEST>select /*+ no_merge(emp_view_inline) */ emp.empno
  2  from emp,(select emp1.empno as empno1 from emp1) emp_view_inline
  3  where emp.empno=emp_view_inline.empno1(+)
  4  and emp.ename=‘FORD‘;

     EMPNO
----------
      7902

技术分享对于上面的SQL，所有能做连接谓词推入的条件都已具备，从执行计划中也可以看出Oracle确实也做了连接谓词推入。

再回到一开始执行的SQL，把外连接改为内连接，并在其中加入push_pred hint(让优化器对视图EMP_VIEW做连接谓词推入)和USE_NL hint

scott@TEST>select /*+ no_merge(emp_view) use_nl(emp_view) push_pred(emp_view) */ emp.empno
  2  from emp,emp_view
  3  where emp.empno=emp_view.empno1
  4  and emp.ename=‘FORD‘;

     EMPNO
----------
      7902

技术分享

从执行计划来看，Oracle没有做连接谓词推入，因为它不属于开关提到的那几种能做连接谓词推入的情形，即使使用了Hint也不行。

虽然Oracle是否能做连接谓词推入与目标视图是否能做视图合并、是否是内嵌视图没有关系，但是与目标视图的类型、与外查询之间的连接类型及连接方法是有关系的。到目前为止，Oracle里能做连接谓词推入的情形公限于开头提到的那几种类型，如果不属于这些情形，即便是看起来很简单，Oracle也不会做。

参考《基于Oracle的SQL优化》

官方文档：http://docs.oracle.com/cd/E11882_01/server.112/e41573/optimops.htm#i55050

本文出自 “DBA Fighting!” 博客，请务必保留此出处http://hbxztc.blog.51cto.com/1587495/1905643

Oracle查询转换之连接谓词推入

标签：oracle 谓词推入

热心网友时间：2022-04-09 18:26

创建测试表：

create table test
(score varchar2(100));

insert into test values ('50,60,70');
insert into test values ('60,70,80');
commit;

执行：

select wm_concat(case
                   when t.score < 60 then
                    '不及格'
                   else
                    '及格'
                 end) score
  from (select a.rowid rd, REGEXP_SUBSTR(a.score, '[^,]+', 1, l) score
          from test a, (SELECT LEVEL l FROM DUAL CONNECT BY LEVEL <= 100) b
         WHERE l <= LENGTH(a.score) - LENGTH(REPLACE(score, ',')) + 1) t
group by t.rd

结果：