Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1150772
  • 博文数量: 166
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 3760
  • 用 户 组: 普通用户
  • 注册时间: 2011-03-30 13:00
个人简介

About me:Oracle ACE pro,optimistic,passionate and harmonious. Focus on ORACLE,MySQL and other database programming,peformance tuning,db design, j2ee,Linux/AIX,Architecture tech,etc

文章分类

全部博文(166)

文章存档

2024年(18)

2023年(28)

2022年(43)

2020年(62)

2014年(3)

2013年(9)

分类: Oracle

2024-04-08 22:50:44

子查询的subquery unnest是数据库一种重要的查询转换技术,ORACLE作为有着强大优化器(CBO)的数据库,同样在子查询转换方面有很多技术,当然限制也较多,本文详细讲解子查询里有OR的查询转换技术,利用ORACLE CBO自身的两大查询转换技术以及手动改写方法,使用三大方法,助力OR子查询性能起飞。

先看建表语句:

点击(此处)折叠或打开

  1. DDL:
  2. drop table a;
  3. drop table b;
  4. drop table c;
  5. create table a as select * from dba_objects;
  6. create table b as select * from a;
  7. create table c as select * from a;
  8. create index idx_a on a(object_id);
  9. exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user,tabname=>'a',estimate_percent=>10,method_opt=>'for all columns size auto',no_invalidate=>false,cascade=>true,degree => 10);
  10. exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user,tabname=>'b',estimate_percent=>10,method_opt=>'for all columns size auto',no_invalidate=>false,cascade=>true,degree => 10);
  11. exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user,tabname=>'c',estimate_percent=>10,method_opt=>'for all columns size auto',no_invalidate=>false,cascade=>true,degree => 10);

子查询中有OR经常不能unnest subquery,从而走FILTER执行计划,效率低下,如何unnest,主要有如下三大方法:
1)or expansion subquery(12C支持)
2)disjunective subquery unnest(ORACLE 11g开始支持)
3) 手动改写成UNION ALL,主要利用LNNVL或rowid,以及集合运算规则not(a or b) => not a and not b,not a or not b => not (a and b)。集合改写主要是针对anti join子查询


ORACLE针对特定的or subquery,有or expansion subquery(12C开始支持带子查询的or expansion)、disjunctive subquery unnest两种。

1)12C开始对于带子查询的or expansion转换,主要针对exp or subq,对应的exp是主表查询条件,貌似exp条件要走索引,or expansion基于CBQT。

2)ORACLE CBO有_optimizer_unnest_disjunctive_subq功能(11g),符合条件的可以进行unnest subquery,
优化器将子查询里的或子查询外的OR条件对应的表(非主表)改写成UNION ALL,然后按照关联键剔重,做成VIEW,然后与主表关联,这样避免不能unnest走FILTER,
从而提高效率。

能够进行unnest disjunctive subquery的,要符合一定的条件,主要是针对subq1 or subq2或suq(condition1 or condition2)格式:

2.1)or子查询分支里的主表关联条件必须是同一个列(对exp or subq走or扩展的没有要求)
2.2)or子查询分支里的主表关联条件必须是在同一侧,子查询表对应的关联条件列必须类型一致(因为要改成UNION ALL视图,否则报错)。


什么叫disjunctive subquery?
disjunctive subquery指的是子查询里面or条件或子查询外面有or条件,且or条件不能直接从主表或子查询表里获取结果
(非简单过滤条件,简单过滤条件直接可以查了,也就不会造成子查询无法unnest)。
一般情况下,disjunctive subquery是不能unnest的,则执行计划由主表驱动子查询走FILTER。
符合oracle cbo的unnest disjunctive subquery才可以查询转换,符合条件的会自动将子查询改写为UNION ALL。
或者能够走or展开的走or展开。


对于or简单条件,只是过滤的,不影响subquery unnest:
如果or条件来源于同一张表,且可以直接获取结果(与其他条件是and连接或能转为and),这种相当于普通谓词,不是disjunctive subquery,不影响subquery unnest。
比如:

点击(此处)折叠或打开

  1. select * from a
  2. where exists(select 1 from b where a.object_name=b.object_name and
  3.             (b.object_id >100 or b.created>trunc(sysdate-10)))

这里的子查询里or条件是(b.object_id >100 or b.created>trunc(sysdate-10)而且与前面的条件是and连接,所以对于子查询表b可以直接按or条件过滤后
与主表关联,可以直接subquery unnest。
执行计划如下:
可以看到这种来源于同一个表的or条件,而且与其他条件是and连接,可以unnest subquery,执行计划走HASH JOIN RIGHT SEMI

点击(此处)折叠或打开

  1. Execution Plan
  2. ----------------------------------------------------------
  3. Plan hash value: 2016728674

  4. -------------------------------------------------------------------------------------
  5. | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
  6. -------------------------------------------------------------------------------------
  7. | 0 | SELECT STATEMENT | | 76791 | 9M| | 1198 (1)| 00:00:15 |
  8. |* 1 | HASH JOIN RIGHT SEMI| | 76791 | 9M| 3752K| 1198 (1)| 00:00:15 |
  9. |* 2 | TABLE ACCESS FULL | B | 76702 | 2846K| | 307 (1)| 00:00:04 |
  10. | 3 | TABLE ACCESS FULL | A | 76791 | 7349K| | 307 (1)| 00:00:04 |
  11. -------------------------------------------------------------------------------------

  12. Predicate Information (identified by operation id):
  13. ---------------------------------------------------

  14.    1 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")
  15.    2 - filter("B"."OBJECT_ID">100 OR "B"."CREATED">TRUNC(SYSDATE@!-10))

同样的,下面的or条件全部来源于主表,可以直接查询,也不影响unnest:

点击(此处)折叠或打开

  1. select * from a where (a.object_id >100 or a.created>trunc(sysdate-10))
  2.    and exists(select 1 from b where a.object_name=b.object_name)



执行计划如下:

点击(此处)折叠或打开

  1. Execution Plan
  2. ----------------------------------------------------------
  3. Plan hash value: 3112244577
  4. ---------------------------------------------------------------------------------------
  5. | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
  6. ---------------------------------------------------------------------------------------
  7. | 0 | SELECT STATEMENT | | 76702 | 9213K| | 948 (1)| 00:00:12 |
  8. |* 1 | HASH JOIN RIGHT SEMI | | 76702 | 9213K| 2776K| 948 (1)| 00:00:12 |
  9. | 2 | INDEX FAST FULL SCAN| IDX_B | 76791 | 1874K| | 105 (1)| 00:00:02 |
  10. |* 3 | TABLE ACCESS FULL | A | 76702 | 7340K| | 307 (1)| 00:00:04 |
  11. ---------------------------------------------------------------------------------------
  12. Predicate Information (identified by operation id):
  13. ---------------------------------------------------
  14. 1 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")
  15. 3 - filter("A"."OBJECT_ID">100 OR "A"."CREATED">TRUNC(SYSDATE@!-10))

下面针对or的subquery查询转换,分别研究下。
1.or expansion subquery
or expansion subquery转换条件:
主要是where exists(suq1) or exp结构,其中exp是主表条件,这种从12c开始可以直接走or expansion,由隐含参数_optimizer_cbqt_or_expansion控制,基于CBQT的查询转换。
貌似主表的exp条件走索引才能完美查询转换,否则另一个or分支还会有FILTER


下面是19c演示的or expansion子查询转换:

点击(此处)折叠或打开

  1. select/*+or_expand*/ * from a
  2. where a.object_name in (select/*+unnest*/ b.object_name from b)
  3. or a.object_id=100 ;
可以直接OR展开,然后LNNVL(a.object_id=100)去掉重复行,LNNVL相当于a.object_id<>100 or a.object_id is null。
从执行计划可以看到,or的两个分支分别用UNION ALL连接,对于OR子查询的分支也能unnest subquery,走HASH JOIN RIGHT SEMI。

点击(此处)折叠或打开

  1. Execution Plan
  2. ----------------------------------------------------------
  3. Plan hash value: 2365798918
  4. -----------------------------------------------------------------------------------------------------------------
  5. | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
  6. -----------------------------------------------------------------------------------------------------------------
  7. | 0 | SELECT STATEMENT | | 72150 | 33M| | 1537 (1)| 00:00:01 |
  8. | 1 | VIEW | VW_ORE_377C5901 | 72150 | 33M| | 1537 (1)| 00:00:01 |
  9. | 2 | UNION-ALL | | | | | | |
  10. | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| A | 1 | 132 | | 2 (0)| 00:00:01 |
  11. |* 4 | INDEX RANGE SCAN | IDX_A | 1 | | | 1 (0)| 00:00:01 |
  12. |* 5 | HASH JOIN RIGHT SEMI | | 72149 | 13M| 5504K| 1535 (1)| 00:00:01 |
  13. | 6 | VIEW | VW_NSO_1 | 72160 | 4650K| | 386 (1)| 00:00:01 |
  14. | 7 | TABLE ACCESS FULL | B | 72160 | 2466K| | 386 (1)| 00:00:01 |
  15. |* 8 | TABLE ACCESS FULL | A | 72149 | 9300K| | 389 (2)| 00:00:01 |
  16. -----------------------------------------------------------------------------------------------------------------
  17. Predicate Information (identified by operation id):
  18. ---------------------------------------------------
  19. 4 - access("A"."OBJECT_ID"=100)
  20. 5 - access("A"."OBJECT_NAME"="OBJECT_NAME")
  21. 8 - filter(LNNVL("A"."OBJECT_ID"=100))

但是如果去掉a表object_id列索引:虽然也能OR展开,但是子查询不能unnest,使用unnest hints也无效,所以针对or expansion的主表单独的条件要走索引,
这些是基于CBQT的,可能主表单独的分支走全表扫描,COST过大,子查询就不展开了。

点击(此处)折叠或打开

  1. drop index idx_a

执行计划如下,可以通过执行计划看到是子查询分支走FILTER

点击(此处)折叠或打开

  1. Execution Plan
  2. ----------------------------------------------------------
  3. Plan hash value: 2886310809
  4. ----------------------------------------------------------------------------------------
  5. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  6. ----------------------------------------------------------------------------------------
  7. | 0 | SELECT STATEMENT | | 72150 | 33M| 1160 (1)| 00:00:01 |
  8. | 1 | VIEW | VW_ORE_377C5901 | 72150 | 33M| 1160 (1)| 00:00:01 |
  9. | 2 | UNION-ALL | | | | | |
  10. |* 3 | TABLE ACCESS FULL | A | 1 | 132 | 386 (1)| 00:00:01 |
  11. |* 4 | FILTER | | | | | |
  12. |* 5 | TABLE ACCESS FULL| A | 72149 | 9300K| 388 (2)| 00:00:01 |
  13. |* 6 | TABLE ACCESS FULL| B | 1 | 35 | 386 (1)| 00:00:01 |
  14. ----------------------------------------------------------------------------------------
  15. Predicate Information (identified by operation id):
  16. ---------------------------------------------------
  17. 3 - filter("A"."OBJECT_ID"=100)
  18. 4 - filter( EXISTS (SELECT /*+ UNNEST */ 0 FROM "B" "B" WHERE
  19. "B"."OBJECT_NAME"=:B1))
  20. 5 - filter(LNNVL("A"."OBJECT_ID"=100))
  21. 6 - filter("B"."OBJECT_NAME"=:B1)
在11g里无法查询转换,走FILTER:

点击(此处)折叠或打开

  1. Execution Plan
  2. ----------------------------------------------------------
  3. Plan hash value: 1049914119
  4. ---------------------------------------------------------------------------
  5. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  6. ---------------------------------------------------------------------------
  7. | 0 | SELECT STATEMENT | | 3870 | 370K| 307 (1)| 00:00:04 |
  8. |* 1 | FILTER | | | | | |
  9. | 2 | TABLE ACCESS FULL| A | 77380 | 7405K| 307 (1)| 00:00:04 |
  10. |* 3 | TABLE ACCESS FULL| B | 1 | 25 | 166 (1)| 00:00:02 |
  11. ---------------------------------------------------------------------------
  12. Predicate Information (identified by operation id):
  13. ---------------------------------------------------
  14. 1 - filter("A"."OBJECT_ID"=100 OR EXISTS (SELECT /*+ UNNEST */ 0
  15. FROM "B" "B" WHERE "B"."OBJECT_NAME"=:B1))
  16. 3 - filter("B"."OBJECT_NAME"=:B1)
对于版本是12c之前的或者主表的or分支条件不能走索引的,只能手动改写为UNION ALL,这个后续会写。
下一篇:ORACLE碰到OR子查询效率低,三大方法助力性能起飞_PART2

阅读(1723) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~