Chinaunix首页 | 论坛 | 博客

dingjun123的blog

首页　| 　博文目录　| 　关于我

dingjun123

  • 博客访问： 1256318
  • 博文数量： 166
  • 博客积分： 0
  • 博客等级： 民兵
  • 技术积分： 3765
  • 用 户 组： 普通用户
  • 注册时间： 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子查询效率低，三大方法助力性能起飞_PART3

分类： Oracle

2024-04-23 16:03:51

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

1.ANTI JOIN OR SUBQUERY改写

对于ANTI JOIN OR SUBQUERY主要有两种形式：
1）ANTJOIN SUBQ1 OR ANTIJOIN SUQ2：这种可以用union改写，如果子查询来源于同一个表，也可以用集合转换改成anti join( cond1 and cond2)
2）ANTIJOIN(cond1 or cond2)：这种用集合转换改成antijoin(cond1) and antijoin(cond2),不能用union改写，要用intersect.

利用集合操作，将OR转为AND，则可以unnest subquery，主要用于anti join转换
对于anti join改写比semi join稍微复杂点特别是子查询里有or的，不能简单改成union all,subq1 or subq2的这种可以改成union all，anti join(cond1 or cond2)的不能
改成union all。
另外对于anti join的，使用LNNVL或等价改写方法，也不能subquery unnest（semi join可以）。


OR能改为AND则能查询转换:
利用集合转换：
NOT (A OR B) 改为NOT A AND NOT B ==主要是这种，这种是anti join子查询里面有OR条件
NOT A OR NOT B改为 NOT (A AND B) ==这种如果A,B是子查询，也不能subquery unnest,如果是子查询里的条件，则可以

SQL1：subq1 or subq2形式,含义是满足条件OR其中任何一个分支的则返回，因此改写就相当于
两个分支UNION

这里主要研究anti join.

点击(此处)折叠或打开

  1. select * from a
  2. where not exists
  3. (select 1 from b where a.object_name = b.object_name)
  4. or
  5. not exists
  6. (select 1 from c where a.object_id = c.object_id)
  7. ;
1037 rows selected.
非常慢，都是FILTER，可以对FILTER子节点建索引优化，这里可以对b.object_name和c.object_id
建立索引优化

点击(此处)折叠或打开

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

  5. ---------------------------------------------------------------------------
  6. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  7. ---------------------------------------------------------------------------
  8. | 0 | SELECT STATEMENT | | 77378 | 7405K| 12M (1)| 41:20:01 |
  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. |* 4 | TABLE ACCESS FULL| B | 1 | 5 | 306 (1)| 00:00:04 |
  13. ---------------------------------------------------------------------------

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

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

下面开头思路改写。

方法1）一般改写，改写为UNION，这里是not exists,也不能用union all+LNNVL改写,改写了也不能
subquery unnest：
可以改成union,增加rowid防止有重复的

点击(此处)折叠或打开

  1. select owner,object_name,subobject_name,object_id
  2. from (
  3. select a.rowid,a.* from a where not exists
  4. (select 1 from b where a.object_name = b.object_name)
  5. union all
  6. select a.rowid,a.* from a where not exists
  7. (select 1 from c where a.object_id = c.object_id
  8. )
  9. );
  10. 1037 rows selected.

  12. Elapsed: 00:00:00.09

点击(此处)折叠或打开

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

  5. ---------------------------------------------------------------------------------------
  6. | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
  7. ---------------------------------------------------------------------------------------
  8. | 0 | SELECT STATEMENT | | 3760 | 414K| | 1547 (1)| 00:00:19 |
  9. | 1 | VIEW | | 3760 | 414K| | 1547 (1)| 00:00:19 |
  10. | 2 | UNION-ALL | | | | | | |
  11. |* 3 | HASH JOIN RIGHT ANTI| | 2986 | 183K| 2800K| 933 (1)| 00:00:12 |
  12. | 4 | TABLE ACCESS FULL | B | 77370 | 1888K| | 306 (1)| 00:00:04 |
  13. | 5 | TABLE ACCESS FULL | A | 77380 | 2871K| | 306 (1)| 00:00:04 |
  14. |* 6 | HASH JOIN RIGHT ANTI| | 774 | 33282 | | 614 (1)| 00:00:08 |
  15. | 7 | TABLE ACCESS FULL | C | 76640 | 374K| | 306 (1)| 00:00:04 |
  16. | 8 | TABLE ACCESS FULL | A | 77380 | 2871K| | 306 (1)| 00:00:04 |
  17. ---------------------------------------------------------------------------------------

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

  22.    3 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")
  23.    6 - access("A"."OBJECT_ID"="C"."OBJECT_ID")


  26. Statistics
  27. ----------------------------------------------------------
  28.          44 recursive calls
  29.           0 db block gets
  30.        4475 consistent gets
  31.        2188 physical reads
  32.           0 redo size
  33.       40676 bytes sent via SQL*Net to client
  34.        1279 bytes received via SQL*Net from client
  35.          71 SQL*Net roundtrips to/from client
  36.           4 sorts (memory)
  37.           0 sorts (disk)
  38.        1037 rows processed
方法2）集合改写，这个推荐使用（只有子查询表是同一个才可以）
       
这种是not subq1 or not subq2 ===> not (subq1 and subq2)改写后并不能subquery unnest,因为
两个子查询是不同表，不能直接改到一个子查询里，如下所示：
这种不同表的改写后不能查询转换：

点击(此处)折叠或打开

  1. select * from a
  2. where not
  3. (exists
  4. (select 1 from b where a.object_name = b.object_name)
  5. and exists
  6. (select 1 from c where a.object_id = c.object_id)
  7. )
  8. ;
执行计划还是FILTER不能subquery unnest,因为改成了not (subq1 and subq2)形式，不能转换，
能转换的只有not subq1 and not subq2
或not sub1(cond1 and cond2)等格式：

点击(此处)折叠或打开

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

  5. ----------------------------------------------------------------------------
  6. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  7. ----------------------------------------------------------------------------
  8. | 0 | SELECT STATEMENT | | 77378 | 7405K| 224K (1)| 00:45:00 |
  9. |* 1 | FILTER | | | | | |
  10. | 2 | TABLE ACCESS FULL| A | 77380 | 7405K| 307 (1)| 00:00:04 |
  11. |* 3 | INDEX RANGE SCAN | IDX_B | 1 | 25 | 3 (0)| 00:00:01 |
  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( NOT EXISTS (SELECT 0 FROM "B" "B" WHERE
  19.               "B"."OBJECT_NAME"=:B1) OR NOT EXISTS (SELECT 0 FROM "C" "C" WHERE
  20.               "C"."OBJECT_ID"=:B2))
  21.    3 - access("B"."OBJECT_NAME"=:B1)
  22.    4 - filter("C"."OBJECT_ID"=:B1)
如果两个子查询的表是同一个，比如下面的两个子查询里都是b表，关联列不同：

点击(此处)折叠或打开

  1. select * from a
  2. where not exists
  3. (select 1 from b where a.object_name = b.object_name )
  4. or
  5. not exists
  6. (select 1 from b where a.object_id = b.object_id)
  7. ;
  8. 999 rows selected.

点击(此处)折叠或打开

  1. Elapsed: 00:00:00.25

  3. Execution Plan
  4. ----------------------------------------------------------
  5. Plan hash value: 302000962

  7. -----------------------------------------------------------------------------
  8. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  9. -----------------------------------------------------------------------------
  10. | 0 | SELECT STATEMENT | | 77378 | 7405K| 224K (1)| 00:45:00 |
  11. |* 1 | FILTER | | | | | |
  12. | 2 | TABLE ACCESS FULL| A | 77380 | 7405K| 307 (1)| 00:00:04 |
  13. |* 3 | INDEX RANGE SCAN | IDX_B | 1 | 25 | 3 (0)| 00:00:01 |
  14. |* 4 | INDEX RANGE SCAN | IDX1_B | 1 | 5 | 1 (0)| 00:00:01 |
  15. -----------------------------------------------------------------------------

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

  20.    1 - filter( NOT EXISTS (SELECT 0 FROM "B" "B" WHERE
  21.               "B"."OBJECT_NAME"=:B1) OR NOT EXISTS (SELECT 0 FROM "B" "B" WHERE
  22.               "B"."OBJECT_ID"=:B2))
  23.    3 - access("B"."OBJECT_NAME"=:B1)
  24.    4 - access("B"."OBJECT_ID"=:B1)


  27. Statistics
  28. ----------------------------------------------------------
  29.           0 recursive calls
  30.           0 db block gets
  31.      231314 consistent gets
  32.        1094 physical reads
  33.           0 redo size
  34.       52714 bytes sent via SQL*Net to client
  35.        1246 bytes received via SQL*Net from client
  36.          68 SQL*Net roundtrips to/from client
  37.           0 sorts (memory)
  38.           0 sorts (disk)
  39.         999 rows processed
这种可以利用集合转换，not cond1 or cond2 => not (cond1 and cond2),比如下面的，改写后走
HASH JOIN：

点击(此处)折叠或打开

  1. select * from a
  2. where not exists
  3. (select 1 from b where a.object_name = b.object_name and a.object_id = b.object_id)
  4. ;

  6. 999 rows selected.

执行计划显示可以查询转换，无FILTER,走HASH JOIN：

点击(此处)折叠或打开

  1. Elapsed: 00:00:00.07

  3. Execution Plan
  4. ----------------------------------------------------------
  5. Plan hash value: 2798188842

  7. -------------------------------------------------------------------------------------
  8. | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
  9. -------------------------------------------------------------------------------------
  10. | 0 | SELECT STATEMENT | | 774 | 99072 | | 1172 (1)| 00:00:15 |
  11. |* 1 | HASH JOIN RIGHT ANTI| | 774 | 99072 | 3176K| 1172 (1)| 00:00:15 |
  12. | 2 | TABLE ACCESS FULL | B | 77370 | 2266K| | 306 (1)| 00:00:04 |
  13. | 3 | TABLE ACCESS FULL | A | 77380 | 7405K| | 307 (1)| 00:00:04 |
  14. -------------------------------------------------------------------------------------

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

  19.    1 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME" AND
  20.               "A"."OBJECT_ID"="B"."OBJECT_ID")


  23. Statistics
  24. ----------------------------------------------------------
  25.           0 recursive calls
  26.           0 db block gets
  27.        2258 consistent gets
  28.        2188 physical reads
  29.           0 redo size
  30.       52714 bytes sent via SQL*Net to client
  31.        1246 bytes received via SQL*Net from client
  32.          68 SQL*Net roundtrips to/from client
  33.           0 sorts (memory)
  34.           0 sorts (disk)
  35.         999 rows processed


SQL2：这种not exists(xx1 or xx2),意思是只有都不满足xx1或xx2条件的才返回（因为是anti
join)，和前面的not subq1 or not subq2不一样(前面的可以改成union )
这种如果是anti join,不能简单改成union 了，不等价，看起来应该改成intersect），改写
使用集合运算，改写为not exists subq1 and not exists subq2
如下例：

点击(此处)折叠或打开

  1. select * from a
  2. where not exists
  3. (select 1 from b where a.object_name = b.object_name or a.object_id=b.object_id);
执行计划显示无法unnest,有FILTER：

点击(此处)折叠或打开

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

  5. ---------------------------------------------------------------------------
  6. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  7. ---------------------------------------------------------------------------
  8. | 0 | SELECT STATEMENT | | 77380 | 7405K| 8169K (1)| 27:13:49 |
  9. |* 1 | FILTER | | | | | |
  10. | 2 | TABLE ACCESS FULL| A | 77380 | 7405K| 307 (1)| 00:00:04 |
  11. |* 3 | TABLE ACCESS FULL| B | 2 | 60 | 109 (1)| 00:00:02 |
  12. ---------------------------------------------------------------------------

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

  17.    1 - filter( NOT EXISTS (SELECT 0 FROM "B" "B" WHERE
  18.               "B"."OBJECT_NAME"=:B1 OR "B"."OBJECT_ID"=:B2))
  19.    3 - filter("B"."OBJECT_NAME"=:B1 OR "B"."OBJECT_ID"=:B2)
非常慢，可以对b表object_name,object_id分别建立索引，这样走or扩展，走索引好点。
create index idx_b on b(object_name);
create index idx1_b on b(object_id);  
执行计划走FILTER,子查询因为是不同列，走INDEX OR转换，转为BITMAP INDEX，执行计划如
下：

点击(此处)折叠或打开

  1. Elapsed: 00:00:00.43

  3. Execution Plan
  4. ----------------------------------------------------------
  5. Plan hash value: 877580492

  7. -------------------------------------------------------------------------------------------
  8. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  9. -------------------------------------------------------------------------------------------
  10. | 0 | SELECT STATEMENT | | 77380 | 7405K| 337K (1)| 01:07:32 |
  11. |* 1 | FILTER | | | | | |
  12. | 2 | TABLE ACCESS FULL | A | 77380 | 7405K| 307 (1)| 00:00:04 |
  13. | 3 | BITMAP CONVERSION TO ROWIDS | | | | | |
  14. | 4 | BITMAP OR | | | | | |
  15. | 5 | BITMAP CONVERSION FROM ROWIDS| | | | | |
  16. |* 6 | INDEX RANGE SCAN | IDX_B | | | 3 (0)| 00:00:01 |
  17. | 7 | BITMAP CONVERSION FROM ROWIDS| | | | | |
  18. |* 8 | INDEX RANGE SCAN | IDX1_B | | | 1 (0)| 00:00:01 |
  19. -------------------------------------------------------------------------------------------

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

  24.    1 - filter( NOT EXISTS (SELECT 0 FROM "B" "B" WHERE "B"."OBJECT_NAME"=:B1 OR
  25.               "B"."OBJECT_ID"=:B2))
  26.    6 - access("B"."OBJECT_NAME"=:B1)
  27.    8 - access("B"."OBJECT_ID"=:B1)


  30. Statistics
  31. ----------------------------------------------------------
  32.           1 recursive calls
  33.           0 db block gets
  34.      140193 consistent gets
  35.        1648 physical reads
  36.           0 redo size
  37.       51946 bytes sent via SQL*Net to client
  38.        1235 bytes received via SQL*Net from client
  39.          67 SQL*Net roundtrips to/from client
  40.           0 sorts (memory)
  41.           0 sorts (disk)
  42.         988 rows processed
分析与优化：
方法1)：子查询有or条件，对应主表的列不是同一个列，不能改写为UNION ALL，只能走
FILTER，需要手动改写。
对于这种可以用集合化运算进行转换，NOT (A OR B) ==> NOT A AND NOT B，则可以对
subquery进行unnest转换提高效率.
可以改写为：

点击(此处)折叠或打开

  1. select * from a
  2. where not exists
  3. (select 1 from b where a.object_name = b.object_name)
  4. and not exists
  5. (
  6.  select 1 from b where a.object_id=b.object_id
  7. );
执行计划没有FILTER了，走HASH JOIN，变为0.06s,如下所示：

点击(此处)折叠或打开

  1. 988 rows selected.

  3. Elapsed: 00:00:00.06

  5. Execution Plan
  6. ----------------------------------------------------------
  7. Plan hash value: 3793835392

  9. ------------------------------------------------------------------------------
  10. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  11. ------------------------------------------------------------------------------
  12. | 0 | SELECT STATEMENT | | 8 | 1024 | 921 (1)| 00:00:12 |
  13. |* 1 | HASH JOIN ANTI | | 8 | 1024 | 921 (1)| 00:00:12 |
  14. |* 2 | HASH JOIN RIGHT ANTI| | 774 | 79722 | 615 (1)| 00:00:08 |
  15. | 3 | TABLE ACCESS FULL | B | 77370 | 377K| 306 (1)| 00:00:04 |
  16. | 4 | TABLE ACCESS FULL | A | 77380 | 7405K| 307 (1)| 00:00:04 |
  17. | 5 | TABLE ACCESS FULL | B | 77370 | 1888K| 306 (1)| 00:00:04 |
  18. ------------------------------------------------------------------------------

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

  23.    1 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")
  24.    2 - access("A"."OBJECT_ID"="B"."OBJECT_ID")


  27. Statistics
  28. ----------------------------------------------------------
  29.           1 recursive calls
  30.           0 db block gets
  31.        3292 consistent gets
  32.        1094 physical reads
  33.           0 redo size
  34.       81836 bytes sent via SQL*Net to client
  35.        1235 bytes received via SQL*Net from client
  36.          67 SQL*Net roundtrips to/from client
  37.           0 sorts (memory)
  38.           0 sorts (disk)
  39.         988 rows processed
方法2)：not exists(xx1 or xx2)这种不能简单改成union all,不等价，因为是anti join,实际上返回的
行必须or条件都找不到，所以可以改成intersect:

点击(此处)折叠或打开

  1. select owner,object_name,subobject_name,object_id
  2. from (
  3. select a.rowid,a.* from a where not exists
  4. (select 1 from b where a.object_name = b.object_name)
  5. intersect
  6. select a.rowid,a.* from a where not exists
  7. (select 1 from b where a.object_id = b.object_id
  8. )
  9. );
  10. 988 rows selected.

  12. Elapsed: 00:00:00.08
执行计划如下：

点击(此处)折叠或打开

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

  5. ----------------------------------------------------------------------------------------
  6. | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
  7. ----------------------------------------------------------------------------------------
  8. | 0 | SELECT STATEMENT | | 774 | 87462 | | 1771 (1)| 00:00:22 |
  9. | 1 | VIEW | | 774 | 87462 | | 1771 (1)| 00:00:22 |
  10. | 2 | INTERSECTION | | | | | | |
  11. | 3 | SORT UNIQUE | | 2986 | 358K| | 1155 (1)| 00:00:14 |
  12. |* 4 | HASH JOIN RIGHT ANTI| | 2986 | 358K| 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 | 77380 | 7405K| | 307 (1)| 00:00:04 |
  15. | 7 | SORT UNIQUE | | 774 | 79722 | | 616 (1)| 00:00:08 |
  16. |* 8 | HASH JOIN RIGHT ANTI| | 774 | 79722 | | 615 (1)| 00:00:08 |
  17. | 9 | TABLE ACCESS FULL | B | 77370 | 377K| | 306 (1)| 00:00:04 |
  18. | 10 | TABLE ACCESS FULL | A | 77380 | 7405K| | 307 (1)| 00:00:04 |
  19. ----------------------------------------------------------------------------------------

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

  24.    4 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")
  25.    8 - access("A"."OBJECT_ID"="B"."OBJECT_ID")


  28. Statistics
  29. ----------------------------------------------------------
  30.           1 recursive calls
  31.           0 db block gets
  32.        4388 consistent gets
  33.        2188 physical reads
  34.           0 redo size
  35.       38197 bytes sent via SQL*Net to client
  36.        1235 bytes received via SQL*Net from client
  37.          67 SQL*Net roundtrips to/from client
  38.           2 sorts (memory)
  39.           0 sorts (disk)
  40.         988 rows processed

2.SEMI JOIN  OR SUBQUERY 改写

前面例子对于exp or subq改为UNION ALL，使用LNNVL，下面的要复杂点，不是简单表达式，是
子查询。
1) exists subq1 or exists subq2 含义是只要满足一个即可，可以改为union+rowid、union all+
一个subq取反、union all+lnnvl（子查询分支表是同一个）

点击(此处)折叠或打开

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

  5. 76834 rows selected.

  7. Elapsed: 00:00:01.32
执行计划出现FILTER：

点击(此处)折叠或打开


  2. Execution Plan
  3. ----------------------------------------------------------
  4. Plan hash value: 2447606385

  6. ----------------------------------------------------------------------------
  7. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  8. ----------------------------------------------------------------------------
  9. | 0 | SELECT STATEMENT | | 2 | 196 | 224K (1)| 00:45:00 |
  10. |* 1 | FILTER | | | | | |
  11. | 2 | TABLE ACCESS FULL| A | 77380 | 7405K| 307 (1)| 00:00:04 |
  12. |* 3 | INDEX RANGE SCAN | IDX_B | 1 | 25 | 3 (0)| 00:00:01 |
  13. |* 4 | TABLE ACCESS FULL| C | 1 | 5 | 306 (1)| 00:00:04 |
  14. ----------------------------------------------------------------------------

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

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


  26. Statistics
  27. ----------------------------------------------------------
  28.           1 recursive calls
  29.           0 db block gets
  30.      169738 consistent gets
  31.        1094 physical reads
  32.           0 redo size
  33.     4013099 bytes sent via SQL*Net to client
  34.       56862 bytes received via SQL*Net from client
  35.        5124 SQL*Net roundtrips to/from client
  36.           0 sorts (memory)
  37.           0 sorts (disk)
  38.       76834 rows processed
下面研究下semi join or subquery改写：
方法1：改为union ,需要剔除重复行，可以用rowid

点击(此处)折叠或打开

  1. select owner,object_name,subobject_name,object_id
  2. from(
  3. select a.rowid,a.* from a
  4. where exists(select 1 from b where a.object_name = b.object_name)
  5. union
  6. select a.rowid,a.* from a
  7. where exists (select 1 from c where a.object_id = c.object_id)
  8. );
  9.                   
  10. 76834 rows selected.

  12. Elapsed: 00:00:00.87
可以sunquery unnest，如下所示：

点击(此处)折叠或打开

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

  5. ------------------------------------------------------------------------------------------
  6. | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
  7. ------------------------------------------------------------------------------------------
  8. | 0 | SELECT STATEMENT | | 151K| 16M| | 5424 (1)| 00:01:06 |
  9. | 1 | VIEW | | 151K| 16M| | 5424 (1)| 00:01:06 |
  10. | 2 | SORT UNIQUE | | 151K| 16M| 17M| 5424 (45)| 00:01:06 |
  11. | 3 | UNION-ALL | | | | | | |
  12. |* 4 | HASH JOIN RIGHT SEMI | | 74394 | 8935K| 2800K| 952 (1)| 00:00:12 |
  13. | 5 | INDEX FAST FULL SCAN| IDX_B | 77370 | 1888K| | 104 (1)| 00:00:02 |
  14. | 6 | TABLE ACCESS FULL | A | 77380 | 7405K| | 307 (1)| 00:00:04 |
  15. |* 7 | HASH JOIN RIGHT SEMI | | 76640 | 7708K| | 615 (1)| 00:00:08 |
  16. | 8 | TABLE ACCESS FULL | C | 76640 | 374K| | 306 (1)| 00:00:04 |
  17. | 9 | TABLE ACCESS FULL | A | 77380 | 7405K| | 307 (1)| 00:00:04 |
  18. ------------------------------------------------------------------------------------------

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

  23.    4 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")
  24.    7 - access("A"."OBJECT_ID"="C"."OBJECT_ID")


  27. Statistics
  28. ----------------------------------------------------------
  29.           1 recursive calls
  30.           0 db block gets
  31.        3675 consistent gets
  32.        2188 physical reads
  33.           0 redo size
  34.     3255132 bytes sent via SQL*Net to client
  35.       56862 bytes received via SQL*Net from client
  36.        5124 SQL*Net roundtrips to/from client
  37.           1 sorts (memory)
  38.           0 sorts (disk)
  39.       76834 rows processed
方法2：因为这里两个分支是不同表，所以改为union all+一个分支取反(not exists)

点击(此处)折叠或打开

  1. select owner,object_name,subobject_name,object_id
  2. from(
  3. select a.rowid,a.* from a
  4. where exists(select 1 from b where a.object_name = b.object_name)
  5. union all
  6. select a.rowid,a.* from a
  7. where exists (select 1 from c where a.object_id = c.object_id)
  8. and not exists
  9. (select 1 from b where a.object_name = b.object_name)
  10. );

  12. 76834 rows selected.

  14. Elapsed: 00:00:00.79
执行计划如下：

点击(此处)折叠或打开

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

  5. ------------------------------------------------------------------------------------------
  6. | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
  7. ------------------------------------------------------------------------------------------
  8. | 0 | SELECT STATEMENT | | 77379 | 8538K| | 1768 (1)| 00:00:22 |
  9. | 1 | VIEW | | 77379 | 8538K| | 1768 (1)| 00:00:22 |
  10. | 2 | UNION-ALL | | | | | | |
  11. |* 3 | HASH JOIN RIGHT SEMI | | 74394 | 4576K| 2800K| 731 (1)| 00:00:09 |
  12. | 4 | INDEX FAST FULL SCAN | IDX_B | 77370 | 1888K| | 104 (1)| 00:00:02 |
  13. | 5 | TABLE ACCESS FULL | A | 77380 | 2871K| | 306 (1)| 00:00:04 |
  14. |* 6 | HASH JOIN SEMI | | 2985 | 198K| | 1038 (1)| 00:00:13 |
  15. |* 7 | HASH JOIN RIGHT ANTI | | 2986 | 183K| 2800K| 731 (1)| 00:00:09 |
  16. | 8 | INDEX FAST FULL SCAN| IDX_B | 77370 | 1888K| | 104 (1)| 00:00:02 |
  17. | 9 | TABLE ACCESS FULL | A | 77380 | 2871K| | 306 (1)| 00:00:04 |
  18. | 10 | TABLE ACCESS FULL | C | 76640 | 374K| | 306 (1)| 00:00:04 |
  19. ------------------------------------------------------------------------------------------

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

  24.    3 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")
  25.    6 - access("A"."OBJECT_ID"="C"."OBJECT_ID")
  26.    7 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")


  29. Statistics
  30. ----------------------------------------------------------
  31.           0 recursive calls
  32.           0 db block gets
  33.        9109 consistent gets
  34.        2188 physical reads
  35.           0 redo size
  36.     3255141 bytes sent via SQL*Net to client
  37.       56862 bytes received via SQL*Net from client
  38.        5124 SQL*Net roundtrips to/from client
  39.           0 sorts (memory)
  40.           0 sorts (disk)
  41.       76834 rows processed
 方法3 如果or子查询来源于同一个表，semi join改成union all+lnnvl
如果两个分支是来源于同一个表，则可以使用union all+lnnvl,如下所示,下面子查询都是b表：

点击(此处)折叠或打开

  1. select * from a
  2. where exists(select 1 from b where a.object_name = b.object_name)
  3. or exists (select 1 from b where a.object_id = b.object_id);
  4. 75848 rows selected.

  6. Elapsed: 00:00:01.59

  8. Execution Plan
  9. ----------------------------------------------------------
  10. Plan hash value: 877580492

  12. -------------------------------------------------------------------------------------------
  13. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  14. -------------------------------------------------------------------------------------------
  15. | 0 | SELECT STATEMENT | | 1 | 98 | 337K (1)| 01:07:32 |
  16. |* 1 | FILTER | | | | | |
  17. | 2 | TABLE ACCESS FULL | A | 77380 | 7405K| 307 (1)| 00:00:04 |
  18. | 3 | BITMAP CONVERSION TO ROWIDS | | | | | |
  19. | 4 | BITMAP OR | | | | | |
  20. | 5 | BITMAP CONVERSION FROM ROWIDS| | | | | |
  21. |* 6 | INDEX RANGE SCAN | IDX_B | | | 3 (0)| 00:00:01 |
  22. | 7 | BITMAP CONVERSION FROM ROWIDS| | | | | |
  23. |* 8 | INDEX RANGE SCAN | IDX1_B | | | 1 (0)| 00:00:01 |
  24. -------------------------------------------------------------------------------------------

  26. Predicate Information (identified by operation id):
  27. ---------------------------------------------------

  29.    1 - filter( EXISTS (SELECT 0 FROM "B" "B" WHERE "B"."OBJECT_NAME"=:B1 OR
  30.               "B"."OBJECT_ID"=:B2))
  31.    6 - access("B"."OBJECT_NAME"=:B1)
  32.    8 - access("B"."OBJECT_ID"=:B1)


  35. Statistics
  36. ----------------------------------------------------------
  37.           1 recursive calls
  38.           0 db block gets
  39.      155671 consistent gets
  40.        1094 physical reads
  41.           0 redo size
  42.     3962659 bytes sent via SQL*Net to client
  43.       56136 bytes received via SQL*Net from client
  44.        5058 SQL*Net roundtrips to/from client
  45.           0 sorts (memory)
  46.           0 sorts (disk)
  47.       75848 rows processed
改为UNION ALL+LNNVL，这种改写带LNNVL的不能subquery unnest：

点击(此处)折叠或打开

  1. select owner,object_name,subobject_name,object_id
  2. from(
  3. select a.rowid,a.* from a
  4. where exists(select 1 from b where a.object_name = b.object_name)
  5. union all
  6. select a.rowid,a.* from a
  7. where exists (select 1 from b where a.object_id = b.object_id and lnnvl(a.object_name = b.object_name))
  8. );


  11. 75848 rows selected.

  13. Elapsed: 00:00:00.92
执行计划貌似对于LNNVL的还是FILTER，这里的LNNVL是关联条件，前面的是简单表达式可以：

点击(此处)折叠或打开

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

  5. -------------------------------------------------------------------------------------------------
  6. | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
  7. -------------------------------------------------------------------------------------------------
  8. | 0 | SELECT STATEMENT | | 74395 | 8209K| | 151K (1)| 00:30:20 |
  9. | 1 | VIEW | | 74395 | 8209K| | 151K (1)| 00:30:20 |
  10. | 2 | UNION-ALL | | | | | | |
  11. |* 3 | HASH JOIN RIGHT SEMI | | 74394 | 4576K| 2800K| 731 (1)| 00:00:09 |
  12. | 4 | INDEX FAST FULL SCAN | IDX_B | 77370 | 1888K| | 104 (1)| 00:00:02 |
  13. | 5 | TABLE ACCESS FULL | A | 77380 | 2871K| | 306 (1)| 00:00:04 |
  14. |* 6 | FILTER | | | | | | |
  15. | 7 | TABLE ACCESS FULL | A | 77380 | 2871K| | 306 (1)| 00:00:04 |
  16. |* 8 | TABLE ACCESS BY INDEX ROWID| B | 1 | 30 | | 2 (0)| 00:00:01 |
  17. |* 9 | INDEX RANGE SCAN | IDX1_B | 1 | | | 1 (0)| 00:00:01 |
  18. -------------------------------------------------------------------------------------------------

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

  23.    3 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")
  24.    6 - filter( EXISTS (SELECT 0 FROM "B" "B" WHERE "B"."OBJECT_ID"=:B1 AND
  25.               LNNVL("B"."OBJECT_NAME"=:B2)))
  26.    8 - filter(LNNVL("B"."OBJECT_NAME"=:B1))
  27.    9 - access("B"."OBJECT_ID"=:B1)


  30. Statistics
  31. ----------------------------------------------------------
  32.           1 recursive calls
  33.           0 db block gets
  34.      161911 consistent gets
  35.        2188 physical reads
  36.           0 redo size
  37.     3217600 bytes sent via SQL*Net to client
  38.       56136 bytes received via SQL*Net from client
  39.        5058 SQL*Net roundtrips to/from client
  40.           0 sorts (memory)
  41.           0 sorts (disk)
  42.       75848 rows processed
当然，用前面的方法改为not exists是可以的：

点击(此处)折叠或打开

  1. select owner,object_name,subobject_name,object_id
  2. from(
  3. select a.rowid,a.* from a
  4. where exists(select 1 from b where a.object_name = b.object_name)
  5. union all
  6. select a.rowid,a.* from a
  7. where exists (select 1 from b where a.object_id = b.object_id)
  8. and not exists(select 1 from b where a.object_name = b.object_name)
  9. );

  11. Elapsed: 00:00:00.78
执行计划如下：

点击(此处)折叠或打开

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

  5. -------------------------------------------------------------------------------------------
  6. | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
  7. -------------------------------------------------------------------------------------------
  8. | 0 | SELECT STATEMENT | | 77379 | 8538K| | 1510 (1)| 00:00:19 |
  9. | 1 | VIEW | | 77379 | 8538K| | 1510 (1)| 00:00:19 |
  10. | 2 | UNION-ALL | | | | | | |
  11. |* 3 | HASH JOIN RIGHT SEMI | | 74394 | 4576K| 2800K| 731 (1)| 00:00:09 |
  12. | 4 | INDEX FAST FULL SCAN | IDX_B | 77370 | 1888K| | 104 (1)| 00:00:02 |
  13. | 5 | TABLE ACCESS FULL | A | 77380 | 2871K| | 306 (1)| 00:00:04 |
  14. |* 6 | HASH JOIN SEMI | | 2985 | 198K| | 779 (1)| 00:00:10 |
  15. |* 7 | HASH JOIN RIGHT ANTI | | 2986 | 183K| 2800K| 731 (1)| 00:00:09 |
  16. | 8 | INDEX FAST FULL SCAN| IDX_B | 77370 | 1888K| | 104 (1)| 00:00:02 |
  17. | 9 | TABLE ACCESS FULL | A | 77380 | 2871K| | 306 (1)| 00:00:04 |
  18. | 10 | INDEX FAST FULL SCAN | IDX1_B | 77370 | 377K| | 47 (0)| 00:00:01 |
  19. -------------------------------------------------------------------------------------------

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

  24.    3 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")
  25.    6 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
  26.    7 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")


  29. Statistics
  30. ----------------------------------------------------------
  31.           0 recursive calls
  32.           0 db block gets
  33.        8121 consistent gets
  34.        2188 physical reads
  35.           0 redo size
  36.     3217600 bytes sent via SQL*Net to client
  37.       56136 bytes received via SQL*Net from client
  38.        5058 SQL*Net roundtrips to/from client
  39.           0 sorts (memory)
  40.           0 sorts (disk)
  41.       75848 rows processed
lnnvl(a.object_name = b.object_name)等价于
a.object_name<>b.object_name or a.object_name is null or b.object_name is null
在老虎刘那学了一招，可以通过case when改写：
case when a.object_name<>b.object_name or a.object_name is null or b.object_name is null
then 0 end = 0

点击(此处)折叠或打开

  1. select * from a
  2. where exists
  3. (select 1 from b where a.object_name = b.object_name)
  4. union all
  5. select * from a
  6. where exists
  7. (select 1 from b where a.object_id = b.object_id
  8. and case when a.object_name<>b.object_name or a.object_name is null or b.object_name is null then 0 end = 0);

  10. 75848 rows selected.
效率上看，还没有改成not exists的好(和具体数据分布有关），因为建了索引，直接lnnvl走FILTER
的也不错。

点击(此处)折叠或打开

  1. Elapsed: 00:00:01.17

  3. Execution Plan
  4. ----------------------------------------------------------
  5. Plan hash value: 884194011

  7. ----------------------------------------------------------------------------------------
  8. | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
  9. ----------------------------------------------------------------------------------------
  10. | 0 | SELECT STATEMENT | | 75168 | 9032K| | 2124 (56)| 00:00:26 |
  11. | 1 | UNION-ALL | | | | | | |
  12. |* 2 | HASH JOIN RIGHT SEMI | | 74394 | 8935K| 2800K| 952 (1)| 00:00:12 |
  13. | 3 | INDEX FAST FULL SCAN| IDX_B | 77370 | 1888K| | 104 (1)| 00:00:02 |
  14. | 4 | TABLE ACCESS FULL | A | 77380 | 7405K| | 307 (1)| 00:00:04 |
  15. |* 5 | HASH JOIN RIGHT SEMI | | 774 | 99072 | 3176K| 1172 (1)| 00:00:15 |
  16. | 6 | TABLE ACCESS FULL | B | 77370 | 2266K| | 306 (1)| 00:00:04 |
  17. | 7 | TABLE ACCESS FULL | A | 77380 | 7405K| | 307 (1)| 00:00:04 |
  18. ----------------------------------------------------------------------------------------

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

  23.    2 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")
  24.    5 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
  25.        filter(CASE WHEN ("A"."OBJECT_NAME"<>"B"."OBJECT_NAME" OR
  26.               "A"."OBJECT_NAME" IS NULL OR "B"."OBJECT_NAME" IS NULL) THEN 0 END =0)


  29. Statistics
  30. ----------------------------------------------------------
  31.           0 recursive calls
  32.           0 db block gets
  33.        8659 consistent gets
  34.        2188 physical reads
  35.           0 redo size
  36.     3962659 bytes sent via SQL*Net to client
  37.       56136 bytes received via SQL*Net from client
  38.        5058 SQL*Net roundtrips to/from client
  39.           0 sorts (memory)
  40.           0 sorts (disk)
  41.       75848 rows processed
对于not exists用上面的case when改貌似不行，exists可以，anti join不可以。

子查询转换挺复杂的，特别是带OR的子查询，经常性无法查询转换走FILTER,造成性能低下，了解
一些OR子查询转换和改写规则，有助于提升子查询性能，理解优化器的查询转换。
此外，子查询转换还有很多限制，比如子查询里有rownum等复杂的条件，也可能阻止unnest。




阅读(1171) | 评论(0) | 转发(0) |
0

上一篇：ORACLE碰到OR子查询效率低，三大方法助力性能起飞_PART2

下一篇：12C批量加载自动收集统计信息特性和相关BUG、限制

给主人留下些什么吧！~~
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6