一个NOT EXISTS含有OR条件子查询的优化

SQL> SELECT COUNT(*) FROM TC2_SEG S WHERE S.PART_ID = 11 AND S.CLASS_ID IN (310,510)
  2              AND NOT EXISTS (SELECT NODEID FROM TEMP_PORTNODE WHERE NODEID=S.HEAD_NODE OR NODEID=S.TAIL_NODE);

执行计划
----------------------------------------------------------
Plan hash value: 3774119106

-------------------------------------------------------------------------------------
| Id  | Operation           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               |     1 |    23 |   102K  (1)| 00:20:30 |
|   1 |  SORT AGGREGATE     |               |     1 |    23 |            |          |
|*  2 |   FILTER            |               |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| TC2_SEG       | 54783 |  1230K|   581   (1)| 00:00:07 |
|*  4 |    TABLE ACCESS FULL| TEMP_PORTNODE |     73598 |   1850K|     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter( NOT EXISTS (SELECT 0 FROM "TEMP_PORTNODE" "TEMP_PORTNODE"
              WHERE "NODEID"=:B1 OR "NODEID"=:B2))
   3 - filter(("S"."CLASS_ID"=310 OR "S"."CLASS_ID"=510) AND
              "S"."PART_ID"=11)
   4 - filter("NODEID"=:B1 OR "NODEID"=:B2)

这个SQL跑了16分钟，从执行计划看到走FILTER，效率极低，，原因是子查询WHERE中包含了OR。

尝试将SQL改为：

SQL> SELECT COUNT(*) FROM TC2_SEG S WHERE S.PART_ID = 11 AND S.CLASS_ID IN (310,510)
  2              AND NOT EXISTS (SELECT NODEID FROM TEMP_PORTNODE WHERE NODEID=S.HEAD_NODE )
  3              AND NOT EXISTS (SELECT NODEID FROM TEMP_PORTNODE WHERE NODEID=S.TAIL_NODE );

执行计划
----------------------------------------------------------
Plan hash value: 3386542280

----------------------------------------------------------------------------------------
| Id  | Operation              | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |               |     1 |    49 |   587   (1)| 00:00:08 |
|   1 |  SORT AGGREGATE        |               |     1 |    49 |            |          |
|*  2 |   HASH JOIN RIGHT ANTI |               | 54781 |  2621K|   587   (1)| 00:00:08 |
|   3 |    TABLE ACCESS FULL   | TEMP_PORTNODE |    73598 |    1850K |     2   (0)| 00:00:01 |
|*  4 |    HASH JOIN RIGHT ANTI|               | 54782 |  1925K|   584   (1)| 00:00:08 |
|   5 |     TABLE ACCESS FULL  | TEMP_PORTNODE |     73598 |    1850K |     2   (0)| 00:00:01 |
|*  6 |     TABLE ACCESS FULL  | TC2_SEG       | 54783 |  1230K|   581   (1)| 00:00:07 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("NODEID"="S"."HEAD_NODE")
   4 - access("NODEID"="S"."TAIL_NODE")
   6 - filter(("S"."CLASS_ID"=310 OR "S"."CLASS_ID"=510) AND "S"."PART_ID"=11)

1秒跑完，从执行计划看，子查询UNNEST了，选择了HASH JOIN RIGHT ANTI，原SQL由于子查询WHERE中含又OR导致无法UNNEST。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22990797/viewspace-1294341/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22990797/viewspace-1294341/