凯发app官方网站-凯发k8官网下载客户端中心 | | 凯发app官方网站-凯发k8官网下载客户端中心

dingjun123的blog

凯发app官方网站首页　| 　博文目录　| 　关于我

dingjun123

  • 博客访问： 1156276
  • 博文数量： 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年（21）

2023年（28）

2022年（43）

2020年（62）

2014年（3）

2013年（9）

我的朋友
最近访客
推荐博文
相关博文
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
oracle碰到or子查询效率低，三大方法助力性能起飞_part2

分类： oracle

2024-04-22 17:25:21

接上一篇：oracle碰到or子查询效率低，三大方法助力性能起飞_part1

oracle碰到or子查询效率低，三大方法助力性能起飞-凯发app官方网站

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

  5. ----------------------------------------------------------------------------------------
  6. | id | operation | name | rows | bytes | cost (%cpu)| time |
  7. ----------------------------------------------------------------------------------------
  8. | 0 | select statement | | 1 | 111 | 1349 (1)| 00:00:17 |
  9. | 1 | nested loops | | | | | |
  10. | 2 | nested loops | | 1 | 111 | 1349 (1)| 00:00:17 |
  11. | 3 | view | vw_sq_1 | 153k| 1949k| 612 (1)| 00:00:08 |
  12. | 4 | hash unique | | 1 | 749k| 612 (51)| 00:00:08 |
  13. | 5 | union- all| | | | | |
  14. | 6 | table access full | c | 76791 | 374k| 306 (1)| 00:00:04 |
  15. | 7 | table access full | b | 76791 | 374k| 306 (1)| 00:00:04 |
  16. |* 8 | index range scan | idx1_a | 1 | | 1 (0)| 00:00:01 |
  17. | 9 | table access by index rowid| a | 1 | 98 | 2 (0)| 00:00:01 |
  18. ----------------------------------------------------------------------------------------

  20. predicate information (identified by operation id):
  21. ---------------------------------------------------

  23.    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

  5. ---------------------------------------------------------------------------
  6. | id | operation | name | rows | bytes | cost (%cpu)| time |
  7. ---------------------------------------------------------------------------
  8. | 0 | select statement | | 1 | 98 | 20m (1)| 67:15:33 |
  9. |* 1 | filter| | | | | |
  10. | 2 | table access full| a | 76791 | 7349k| 307 (1)| 00:00:04 |
  11. |* 3 | table access full| b | 1 | 5 | 306 (1)| 00:00:04 |
  12. |* 4 | table access full| c | 1 | 5 | 306 (1)| 00:00:04 |
  13. ---------------------------------------------------------------------------

  15. predicate information (identified by operation id):
  16. ---------------------------------------------------

  18.    1 - filter( exists (select 0 from "b" "b" where "b"."object_id"=:b1)
  19.               or exists (select 0 from "c" "c" where "c"."object_id"=:b2))
  20.    3 - filter("b"."object_id"=:b1)
  21.    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);

  5. execution plan
  6. ----------------------------------------------------------
  7. plan hash value: 3747852665

  9. ---------------------------------------------------------------------------
  10. | id | operation | name | rows | bytes | cost (%cpu)| time |
  11. ---------------------------------------------------------------------------
  12. | 0 | select statement | | 1 | 98 | 20m (1)| 67:25:10 |
  13. |* 1 | filter | | | | | |
  14. | 2 | table access full| a | 76791 | 7349k| 307 (1)| 00:00:04 |
  15. |* 3 | table access full| b | 1 | 5 | 307 (1)| 00:00:04 |
  16. |* 4 | table access full| c | 1 | 5 | 306 (1)| 00:00:04 |
  17. ---------------------------------------------------------------------------

  19. predicate information (identified by operation id):
  20. ---------------------------------------------------

  22.    1 - filter( exists (select 0 from "b" "b" where
  23.               to_number(to_char("b"."object_id"))=:b1) or exists (select 0 from "c"
  24.               "c" where "c"."object_id"=:b2))
  25.    3 - filter(to_number(to_char("b"."object_id"))=:b1)
  26.    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);

  5. execution plan
  6. ----------------------------------------------------------
  7. plan hash value: 609371133

  9. ----------------------------------------------------------------------------------------
  10. | id | operation | name | rows | bytes | cost (%cpu)| time |
  11. ----------------------------------------------------------------------------------------
  12. | 0 | select statement | | 1 | 111 | 1349 (1)| 00:00:17 |
  13. | 1 | nested loops | | | | | |
  14. | 2 | nested loops | | 1 | 111 | 1349 (1)| 00:00:17 |
  15. | 3 | view | vw_sq_1 | 153k| 1949k| 612 (1)| 00:00:08 |
  16. | 4 | hash unique | | 1 | 524k| 612 (51)| 00:00:08 |
  17. | 5 | union-all | | | | | |
  18. | 6 | table access full | b | 76791 | 374k| 306 (1)| 00:00:04 |
  19. | 7 | table access full | b | 76791 | 149k| 306 (1)| 00:00:04 |
  20. |* 8 | index range scan | idx1_a | 1 | | 1 (0)| 00:00:01 |
  21. | 9 | table access by index rowid| a | 1 | 98 | 2 (0)| 00:00:01 |
  22. ----------------------------------------------------------------------------------------

  24. predicate information (identified by operation id):
  25. ---------------------------------------------------

  27.    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);

  5. execution plan
  6. ----------------------------------------------------------
  7. plan hash value: 1049914119

  9. ---------------------------------------------------------------------------
  10. | id | operation | name | rows | bytes | cost (%cpu)| time |
  11. ---------------------------------------------------------------------------
  12. | 0 | select statement | | 1 | 98 | 20m (1)| 67:18:55 |
  13. |* 1 | filter | | | | | |
  14. | 2 | table access full| a | 76791 | 7349k| 307 (1)| 00:00:04 |
  15. |* 3 | table access full| b | 2 | 14 | 307 (1)| 00:00:04 |
  16. ---------------------------------------------------------------------------

  18. predicate information (identified by operation id):
  19. ---------------------------------------------------

  21.    1 - filter( exists (select 0 from "b" "b" where
  22.               "b"."data_object_id"=:b1 or "b"."object_id"=:b2))
  23.    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


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

  5. ---------------------------------------------------------------------------
  6. | id | operation | name | rows | bytes | cost (%cpu)| time |
  7. ---------------------------------------------------------------------------
  8. | 0 | select statement | | 3870 | 370k| 307 (1)| 00:00:04 |
  9. |* 1 | filter | | | | | |
  10. | 2 | table access full| a | 77380 | 7405k| 307 (1)| 00:00:04 |
  11. |* 3 | table access full| b | 1 | 25 | 166 (1)| 00:00:02 |
  12. ---------------------------------------------------------------------------

  14. predicate information (identified by operation id):
  15. ---------------------------------------------------

  17.    1 - filter("a"."object_id"=100 or exists (select /* unnest */ 0
  18.               from "b" "b" where "b"."object_name"=:b1))
  19.    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

  5. ----------------------------------------------------------------------------------------------
  6. | id | operation | name | rows | bytes |tempspc| cost (%cpu)| time |
  7. ----------------------------------------------------------------------------------------------
  8. | 0 | select statement | | 74395 | 8936k| | 1156 (100)| 00:00:14 |
  9. | 1 | union-all | | | | | | |
  10. | 2 | table access by index rowid| a | 1 | 98 | | 2 (0)| 00:00:01 |
  11. |* 3 | index range scan | idx_a | 1 | | | 1 (0)| 00:00:01 |
  12. |* 4 | hash join right semi | | 74394 | 8935k| 2800k| 1154 (1)| 00:00:14 |
  13. | 5 | table access full | b | 77370 | 1888k| | 306 (1)| 00:00:04 |
  14. |* 6 | table access full | a | 77379 | 7405k| | 307 (1)| 00:00:04 |
  15. ----------------------------------------------------------------------------------------------

  17. predicate information (identified by operation id):
  18. ---------------------------------------------------

  20.    3 - access("a"."object_id"=100)
  21.    4 - access("a"."object_name"="b"."object_name")
  22.    6 - filter(lnnvl("a"."object_id"=100))

其他改写技巧，请见下一篇： oracle碰到or子查询效率低，三大方法助力性能起飞_part3
阅读(515) | 评论(0) | 转发(0) |
0

上一篇：oracle碰到or子查询效率低，三大方法助力性能起飞_part1

下一篇：oracle碰到or子查询效率低，三大方法助力性能起飞_part3

给主人留下些什么吧！~~
| | | | |

凯发app官方网站 copyright 2001-2010 chinaunix.net all rights reserved 北京皓辰网域网络信息技术有限公司. 凯发k8官网下载客户端中心的版权所有

感谢所有关心和支持过chinaunix的朋友们
")); function link(t){ var href= $(t).attr('href'); href ="?url=" encodeuricomponent(location.href); $(t).attr('href',href); //setcookie("returnouturl", location.href, 60, "/"); }
网站地图