Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1150785
  • 博文数量: 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-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

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

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

  15.    1 - filter( NOT EXISTS (SELECT 0 FROM "B" "B" WHERE
  16.               "B"."OBJECT_NAME"=:B1) OR NOT EXISTS (SELECT 0 FROM "B" "B" WHERE
  17.               "B"."OBJECT_ID"=:B2))
  18.    3 - filter("B"."OBJECT_NAME"=:B1)
  19.    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.

  11. Elapsed: 00:00:00.09

点击(此处)折叠或打开

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

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

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

  19.    3 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")
  20.    6 - access("A"."OBJECT_ID"="C"."OBJECT_ID")


  21. Statistics
  22. ----------------------------------------------------------
  23.          44 recursive calls
  24.           0 db block gets
  25.        4475 consistent gets
  26.        2188 physical reads
  27.           0 redo size
  28.       40676 bytes sent via SQL*Net to client
  29.        1279 bytes received via SQL*Net from client
  30.          71 SQL*Net roundtrips to/from client
  31.           4 sorts (memory)
  32.           0 sorts (disk)
  33.        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

  4. ----------------------------------------------------------------------------
  5. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  6. ----------------------------------------------------------------------------
  7. | 0 | SELECT STATEMENT | | 77378 | 7405K| 224K (1)| 00:45:00 |
  8. |* 1 | FILTER | | | | | |
  9. | 2 | TABLE ACCESS FULL| A | 77380 | 7405K| 307 (1)| 00:00:04 |
  10. |* 3 | INDEX RANGE SCAN | IDX_B | 1 | 25 | 3 (0)| 00:00:01 |
  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( NOT EXISTS (SELECT 0 FROM "B" "B" WHERE
  16.               "B"."OBJECT_NAME"=:B1) OR NOT EXISTS (SELECT 0 FROM "C" "C" WHERE
  17.               "C"."OBJECT_ID"=:B2))
  18.    3 - access("B"."OBJECT_NAME"=:B1)
  19.    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

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

  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 | INDEX RANGE SCAN | IDX1_B | 1 | 5 | 1 (0)| 00:00:01 |
  13. -----------------------------------------------------------------------------

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

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


  21. Statistics
  22. ----------------------------------------------------------
  23.           0 recursive calls
  24.           0 db block gets
  25.      231314 consistent gets
  26.        1094 physical reads
  27.           0 redo size
  28.       52714 bytes sent via SQL*Net to client
  29.        1246 bytes received via SQL*Net from client
  30.          68 SQL*Net roundtrips to/from client
  31.           0 sorts (memory)
  32.           0 sorts (disk)
  33.         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. ;

  5. 999 rows selected.

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

点击(此处)折叠或打开

  1. Elapsed: 00:00:00.07

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

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

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

  15.    1 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME" AND
  16.               "A"."OBJECT_ID"="B"."OBJECT_ID")


  17. Statistics
  18. ----------------------------------------------------------
  19.           0 recursive calls
  20.           0 db block gets
  21.        2258 consistent gets
  22.        2188 physical reads
  23.           0 redo size
  24.       52714 bytes sent via SQL*Net to client
  25.        1246 bytes received via SQL*Net from client
  26.          68 SQL*Net roundtrips to/from client
  27.           0 sorts (memory)
  28.           0 sorts (disk)
  29.         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

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

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

  14.    1 - filter( NOT EXISTS (SELECT 0 FROM "B" "B" WHERE
  15.               "B"."OBJECT_NAME"=:B1 OR "B"."OBJECT_ID"=:B2))
  16.    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

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

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

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

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


  24. Statistics
  25. ----------------------------------------------------------
  26.           1 recursive calls
  27.           0 db block gets
  28.      140193 consistent gets
  29.        1648 physical reads
  30.           0 redo size
  31.       51946 bytes sent via SQL*Net to client
  32.        1235 bytes received via SQL*Net from client
  33.          67 SQL*Net roundtrips to/from client
  34.           0 sorts (memory)
  35.           0 sorts (disk)
  36.         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.

  2. Elapsed: 00:00:00.06

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

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

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

  18.    1 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")
  19.    2 - access("A"."OBJECT_ID"="B"."OBJECT_ID")


  20. Statistics
  21. ----------------------------------------------------------
  22.           1 recursive calls
  23.           0 db block gets
  24.        3292 consistent gets
  25.        1094 physical reads
  26.           0 redo size
  27.       81836 bytes sent via SQL*Net to client
  28.        1235 bytes received via SQL*Net from client
  29.          67 SQL*Net roundtrips to/from client
  30.           0 sorts (memory)
  31.           0 sorts (disk)
  32.         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.

  11. Elapsed: 00:00:00.08
执行计划如下:

点击(此处)折叠或打开

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

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

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

  21.    4 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")
  22.    8 - access("A"."OBJECT_ID"="B"."OBJECT_ID")


  23. Statistics
  24. ----------------------------------------------------------
  25.           1 recursive calls
  26.           0 db block gets
  27.        4388 consistent gets
  28.        2188 physical reads
  29.           0 redo size
  30.       38197 bytes sent via SQL*Net to client
  31.        1235 bytes received via SQL*Net from client
  32.          67 SQL*Net roundtrips to/from client
  33.           2 sorts (memory)
  34.           0 sorts (disk)
  35.         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);

  4. 76834 rows selected.

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

点击(此处)折叠或打开


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

  4. ----------------------------------------------------------------------------
  5. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  6. ----------------------------------------------------------------------------
  7. | 0 | SELECT STATEMENT | | 2 | 196 | 224K (1)| 00:45:00 |
  8. |* 1 | FILTER | | | | | |
  9. | 2 | TABLE ACCESS FULL| A | 77380 | 7405K| 307 (1)| 00:00:04 |
  10. |* 3 | INDEX RANGE SCAN | IDX_B | 1 | 25 | 3 (0)| 00:00:01 |
  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
  16.               "B"."OBJECT_NAME"=:B1) OR EXISTS (SELECT 0 FROM "C" "C" WHERE
  17.               "C"."OBJECT_ID"=:B2))
  18.    3 - access("B"."OBJECT_NAME"=:B1)
  19.    4 - filter("C"."OBJECT_ID"=:B1)


  20. Statistics
  21. ----------------------------------------------------------
  22.           1 recursive calls
  23.           0 db block gets
  24.      169738 consistent gets
  25.        1094 physical reads
  26.           0 redo size
  27.     4013099 bytes sent via SQL*Net to client
  28.       56862 bytes received via SQL*Net from client
  29.        5124 SQL*Net roundtrips to/from client
  30.           0 sorts (memory)
  31.           0 sorts (disk)
  32.       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.

  11. Elapsed: 00:00:00.87
可以sunquery unnest,如下所示:

点击(此处)折叠或打开

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

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

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

  20.    4 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")
  21.    7 - access("A"."OBJECT_ID"="C"."OBJECT_ID")


  22. Statistics
  23. ----------------------------------------------------------
  24.           1 recursive calls
  25.           0 db block gets
  26.        3675 consistent gets
  27.        2188 physical reads
  28.           0 redo size
  29.     3255132 bytes sent via SQL*Net to client
  30.       56862 bytes received via SQL*Net from client
  31.        5124 SQL*Net roundtrips to/from client
  32.           1 sorts (memory)
  33.           0 sorts (disk)
  34.       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. );

  11. 76834 rows selected.

  12. Elapsed: 00:00:00.79
执行计划如下:

点击(此处)折叠或打开

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

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

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

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


  24. Statistics
  25. ----------------------------------------------------------
  26.           0 recursive calls
  27.           0 db block gets
  28.        9109 consistent gets
  29.        2188 physical reads
  30.           0 redo size
  31.     3255141 bytes sent via SQL*Net to client
  32.       56862 bytes received via SQL*Net from client
  33.        5124 SQL*Net roundtrips to/from client
  34.           0 sorts (memory)
  35.           0 sorts (disk)
  36.       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.

  5. Elapsed: 00:00:01.59

  6. Execution Plan
  7. ----------------------------------------------------------
  8. Plan hash value: 877580492

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

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

  24.    1 - filter( 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)


  28. Statistics
  29. ----------------------------------------------------------
  30.           1 recursive calls
  31.           0 db block gets
  32.      155671 consistent gets
  33.        1094 physical reads
  34.           0 redo size
  35.     3962659 bytes sent via SQL*Net to client
  36.       56136 bytes received via SQL*Net from client
  37.        5058 SQL*Net roundtrips to/from client
  38.           0 sorts (memory)
  39.           0 sorts (disk)
  40.       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. );


  9. 75848 rows selected.

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

点击(此处)折叠或打开

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

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

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

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


  25. Statistics
  26. ----------------------------------------------------------
  27.           1 recursive calls
  28.           0 db block gets
  29.      161911 consistent gets
  30.        2188 physical reads
  31.           0 redo size
  32.     3217600 bytes sent via SQL*Net to client
  33.       56136 bytes received via SQL*Net from client
  34.        5058 SQL*Net roundtrips to/from client
  35.           0 sorts (memory)
  36.           0 sorts (disk)
  37.       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. );

  10. Elapsed: 00:00:00.78
执行计划如下:

点击(此处)折叠或打开

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

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

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

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


  24. Statistics
  25. ----------------------------------------------------------
  26.           0 recursive calls
  27.           0 db block gets
  28.        8121 consistent gets
  29.        2188 physical reads
  30.           0 redo size
  31.     3217600 bytes sent via SQL*Net to client
  32.       56136 bytes received via SQL*Net from client
  33.        5058 SQL*Net roundtrips to/from client
  34.           0 sorts (memory)
  35.           0 sorts (disk)
  36.       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);

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

点击(此处)折叠或打开

  1. Elapsed: 00:00:01.17

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

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

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

  19.    2 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")
  20.    5 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
  21.        filter(CASE WHEN ("A"."OBJECT_NAME"<>"B"."OBJECT_NAME" OR
  22.               "A"."OBJECT_NAME" IS NULL OR "B"."OBJECT_NAME" IS NULL) THEN 0 END =0)


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

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




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