Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1150618
  • 博文数量: 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-22 17:25:21

接上一篇:ORACLE碰到OR子查询效率低,三大方法助力性能起飞_PART1

1.disjunctive subquery unnest

disjunctive subquery unnest条件,11g支持,由隐含参数_optimizer_unnest_disjunctive_subq控制:
前提必须是:
1)能unnest subquery必须or分支里的主表关联列是同一个列(如果不是关联列,or在子查询外面,且是主表条件,12c可以直接走or expansion subquery),
   如果子查询里有or分支且主表和子查询表关联,则主表条件在子查询里位置必须在同一侧(大前提,列做运算,函数都不行)且要同一个列
2)能够转为UNION ALL的子查询表对应的列必须类型一致,因为UNION ALL要求字段类型一致。


disjunctive subquery查询转换主要有如下形式:
1)or在子查询外面,比如where 【not】exists(suq1) or exp、where 【not】exists(suq1) or 【not】exists(subq2), exists和in一样,子查询分开,可能对应不同表,
2) or在子查询里面,比如where 【not】exists ( condition1 or condition2...),这种同样要满足disjunctive subquery条件才能转换。

针对or在子查询外面的情况:
1.1)一种格式是where exists(suq1) or exp,见or expansion subquery,12C支持,ORACLE碰到OR子查询效率低,三大方法助力性能起飞_PART1

1.2) subq1 or subq2形式,需要子查询对应主表列条件来源于同一个表,而且顺序一致,且子查询表类型一致,
因为这个要将子查询表做UNION ALL然后变为VIEW,然后再与主表关联,如下示例:

点击(此处)折叠或打开

  1. select * from a
  2. where exists (select 1 from b where a.object_id b.object_id)
  3. or exists (select 1 from c where a.object_id=c.object_id);
这个例子如何subq1 or subq2格式,且主表的列是同一个且在子查询里的条件都在左侧。所以可以
查询转换。
执行计划如下所示,可以看到C和B查询后做成UNION ALL视图,并且剔重(按照object_id剔重):

点击(此处)折叠或打开

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

  4. ----------------------------------------------------------------------------------------
  5. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  6. ----------------------------------------------------------------------------------------
  7. | 0 | SELECT STATEMENT | | 1 | 111 | 1349 (1)| 00:00:17 |
  8. | 1 | NESTED LOOPS | | | | | |
  9. | 2 | NESTED LOOPS | | 1 | 111 | 1349 (1)| 00:00:17 |
  10. | 3 | VIEW | VW_SQ_1 | 153K| 1949K| 612 (1)| 00:00:08 |
  11. | 4 | HASH UNIQUE | | 1 | 749K| 612 (51)| 00:00:08 |
  12. | 5 | UNION- ALL| | | | | |
  13. | 6 | TABLE ACCESS FULL | C | 76791 | 374K| 306 (1)| 00:00:04 |
  14. | 7 | TABLE ACCESS FULL | B | 76791 | 374K| 306 (1)| 00:00:04 |
  15. |* 8 | INDEX RANGE SCAN | IDX1_A | 1 | | 1 (0)| 00:00:01 |
  16. | 9 | TABLE ACCESS BY INDEX ROWID| A | 1 | 98 | 2 (0)| 00:00:01 |
  17. ----------------------------------------------------------------------------------------

  18. Predicate Information (identified by operation id):
  19. ---------------------------------------------------

  20.    8 - access("A"."OBJECT_ID"="VW_COL_1")

如果子查询将a.object_id移动到右侧,则不能
disjunctive subquery unnest,不符合主表关联条件在同一侧:

点击(此处)折叠或打开

  1. select * from a
  2. where exists(select 1 from b where b.object_id=a.object_id)
  3. or exists (select 1 from c where a.object_id=c.object_id)
执行计划有FILTER:

点击(此处)折叠或打开

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

  4. ---------------------------------------------------------------------------
  5. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  6. ---------------------------------------------------------------------------
  7. | 0 | SELECT STATEMENT | | 1 | 98 | 20M (1)| 67:15:33 |
  8. |* 1 | FILTER| | | | | |
  9. | 2 | TABLE ACCESS FULL| A | 76791 | 7349K| 307 (1)| 00:00:04 |
  10. |* 3 | TABLE ACCESS FULL| B | 1 | 5 | 306 (1)| 00:00:04 |
  11. |* 4 | TABLE ACCESS FULL| C | 1 | 5 | 306 (1)| 00:00:04 |
  12. ---------------------------------------------------------------------------

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

  15.    1 - filter( EXISTS (SELECT 0 FROM "B" "B" WHERE "B"."OBJECT_ID"=:B1)
  16.               OR EXISTS (SELECT 0 FROM "C" "C" WHERE "C"."OBJECT_ID"=:B2))
  17.    3 - filter("B"."OBJECT_ID"=:B1)
  18.    4 - filter("C"."OBJECT_ID"=:B1)
如果对应子查询表关联条件列类型不一致,也不行,因为不符合UNION ALL需要列类型一致条件,
比如下面的使用to_char:

点击(此处)折叠或打开

  1. select * from a
  2. where exists(select 1 from b where a.object_id = to_char(b.object_id))
  3. or exists (select 1 from c where a.object_id = c.object_id);

  4. Execution Plan
  5. ----------------------------------------------------------
  6. Plan hash value: 3747852665

  7. ---------------------------------------------------------------------------
  8. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  9. ---------------------------------------------------------------------------
  10. | 0 | SELECT STATEMENT | | 1 | 98 | 20M (1)| 67:25:10 |
  11. |* 1 | FILTER | | | | | |
  12. | 2 | TABLE ACCESS FULL| A | 76791 | 7349K| 307 (1)| 00:00:04 |
  13. |* 3 | TABLE ACCESS FULL| B | 1 | 5 | 307 (1)| 00:00:04 |
  14. |* 4 | TABLE ACCESS FULL| C | 1 | 5 | 306 (1)| 00:00:04 |
  15. ---------------------------------------------------------------------------

  16. Predicate Information (identified by operation id):
  17. ---------------------------------------------------

  18.    1 - filter( EXISTS (SELECT 0 FROM "B" "B" WHERE
  19.               TO_NUMBER(TO_CHAR("B"."OBJECT_ID"))=:B1) OR EXISTS (SELECT 0 FROM "C"
  20.               "C" WHERE "C"."OBJECT_ID"=:B2))
  21.    3 - filter(TO_NUMBER(TO_CHAR("B"."OBJECT_ID"))=:B1)
  22.    4 - filter("C"."OBJECT_ID"=:B1)

2)or在子查询里,比如 exists (select 1 from b where a.object_id = b.object_id
or a.object_id = b.data_object_id)
这种子查询是一个表,两个条件,可以直接对b按照条件union all,同样也是将子查询OR条件合并
成VIEW,同样要求主表是同一个列,且顺序一致,子查询表OR条件类型一致,如果类型不一致,
直接报错,这个和前面的OR在外面不一样,前面的subq1 or subq2是不能查询转换,这个是报错,
类似启发式查询转换了。
下面的可以unnest:

点击(此处)折叠或打开

  1. select * from a
  2. where exists
  3. (select 1 from b where a.object_id = b.object_id or a.object_id = b.data_object_id);

  4. Execution Plan
  5. ----------------------------------------------------------
  6. Plan hash value: 609371133

  7. ----------------------------------------------------------------------------------------
  8. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  9. ----------------------------------------------------------------------------------------
  10. | 0 | SELECT STATEMENT | | 1 | 111 | 1349 (1)| 00:00:17 |
  11. | 1 | NESTED LOOPS | | | | | |
  12. | 2 | NESTED LOOPS | | 1 | 111 | 1349 (1)| 00:00:17 |
  13. | 3 | VIEW | VW_SQ_1 | 153K| 1949K| 612 (1)| 00:00:08 |
  14. | 4 | HASH UNIQUE | | 1 | 524K| 612 (51)| 00:00:08 |
  15. | 5 | UNION-ALL | | | | | |
  16. | 6 | TABLE ACCESS FULL | B | 76791 | 374K| 306 (1)| 00:00:04 |
  17. | 7 | TABLE ACCESS FULL | B | 76791 | 149K| 306 (1)| 00:00:04 |
  18. |* 8 | INDEX RANGE SCAN | IDX1_A | 1 | | 1 (0)| 00:00:01 |
  19. | 9 | TABLE ACCESS BY INDEX ROWID| A | 1 | 98 | 2 (0)| 00:00:01 |
  20. ----------------------------------------------------------------------------------------

  21. Predicate Information (identified by operation id):
  22. ---------------------------------------------------

  23.    8 - access("A"."OBJECT_ID"="VW_COL_1")
修改第二个分支a.object_id到右侧,则不能查询转换:


点击(此处)折叠或打开

  1. select * from a
  2. where exists
  3. (select 1 from b where a.object_id = b.object_id or b.data_object_id = a.object_id);

  4. Execution Plan
  5. ----------------------------------------------------------
  6. Plan hash value: 1049914119

  7. ---------------------------------------------------------------------------
  8. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  9. ---------------------------------------------------------------------------
  10. | 0 | SELECT STATEMENT | | 1 | 98 | 20M (1)| 67:18:55 |
  11. |* 1 | FILTER | | | | | |
  12. | 2 | TABLE ACCESS FULL| A | 76791 | 7349K| 307 (1)| 00:00:04 |
  13. |* 3 | TABLE ACCESS FULL| B | 2 | 14 | 307 (1)| 00:00:04 |
  14. ---------------------------------------------------------------------------

  15. Predicate Information (identified by operation id):
  16. ---------------------------------------------------

  17.    1 - filter( EXISTS (SELECT 0 FROM "B" "B" WHERE
  18.               "B"."DATA_OBJECT_ID"=:B1 OR "B"."OBJECT_ID"=:B2))
  19.    3 - filter("B"."DATA_OBJECT_ID"=:B1 OR "B"."OBJECT_ID"=:B2)

类型不一致,直接报错:

点击(此处)折叠或打开

  1. select * from a
  2. where exists
  3. (select 1 from b where a.object_id = b.object_id or a.object_id = to_char(b.data_object_id));
  4.                                                                   *
  5. ERROR at line 3:
  6. ORA-01790: expression must have same datatype as corresponding expression

2.针对12c之前的or expansion subquery改写


12c之前针对exp or subquery不能or展开,可以用union all改写,利用LNNVL:

点击(此处)折叠或打开

  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 ;
11g不能展开子查询:

点击(此处)折叠或打开

  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)
使用UNION ALL+LNNVL改写,
第二个分支加上lnnvl(a.object_id=100),相当于a.object<>100 or a.object_id is null,也就是
去掉
a.object_id=100的结果,又保留了第二个分支可能存在a.object is null的结果,实际上12c的
子查询or展开就是这样改写的,
如下所示:

点击(此处)折叠或打开

  1. select * from a where a.object_id=100
  2. union all
  3. select * from a
  4. where a.object_name in (select b.object_name from b)
  5. and lnnvl(a.object_id=100);
执行计划如下:

点击(此处)折叠或打开

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

  4. ----------------------------------------------------------------------------------------------
  5. | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
  6. ----------------------------------------------------------------------------------------------
  7. | 0 | SELECT STATEMENT | | 74395 | 8936K| | 1156 (100)| 00:00:14 |
  8. | 1 | UNION-ALL | | | | | | |
  9. | 2 | TABLE ACCESS BY INDEX ROWID| A | 1 | 98 | | 2 (0)| 00:00:01 |
  10. |* 3 | INDEX RANGE SCAN | IDX_A | 1 | | | 1 (0)| 00:00:01 |
  11. |* 4 | HASH JOIN RIGHT SEMI | | 74394 | 8935K| 2800K| 1154 (1)| 00:00:14 |
  12. | 5 | TABLE ACCESS FULL | B | 77370 | 1888K| | 306 (1)| 00:00:04 |
  13. |* 6 | TABLE ACCESS FULL | A | 77379 | 7405K| | 307 (1)| 00:00:04 |
  14. ----------------------------------------------------------------------------------------------

  15. Predicate Information (identified by operation id):
  16. ---------------------------------------------------

  17.    3 - access("A"."OBJECT_ID"=100)
  18.    4 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")
  19.    6 - filter(LNNVL("A"."OBJECT_ID"=100))

其他改写技巧,请见下一篇: ORACLE碰到OR子查询效率低,三大方法助力性能起飞_PART3
阅读(473) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~