一個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/，如需轉載，請註明出處，否則將追究法律責任。