ORACLE使用WITHAS和HINTMATERIALIZE優化SQL解決FILTER效率低下

ORACLE使用WITHAS和HINTMATERIALIZE優化SQL解決FILTER效率低下

在做項目的過程中，一個頁面使用類似如下的SQL查詢數據，為了保密和使用方便，我把項目中有關的表名和字段替換使用ORACLE數據庫中的系統表和字段。

在我所做的項目中，類似ALL_TABLES的表中大概有8W多條數據，下面這個查詢SQL很慢。

WITH PARAMS AS
 (SELECT '' USER_ID, '' SDATE, '%' || '' || '%' SNAME FROM DUAL)
SELECT AU.USERNAME, AU.USER_ID
  FROM ALL_USERS AU
 INNER JOIN PARAMS PA
    ON 1 = 1
 INNER JOIN DBA_USERS DU
    ON AU.USERNAME = DU.USERNAME
 WHERE ((PA.SDATE IS NULL AND PA.USER_ID IS NOT NULL AND
       AU.USER_ID = PA.USER_ID) OR
       
       (PA.SDATE IS NULL AND PA.USER_ID IS NULL AND
       AU.USERNAME NOT IN
       (SELECT AU.USERNAME
            FROM ALL_USERS AU
           INNER JOIN DBA_USERS DEV
              ON AU.USERNAME = DEV.USERNAME
           INNER JOIN (SELECT OWNER AS USERNAME
                        FROM ALL_TABLES T
                       WHERE T.LAST_ANALYZED = TRUNC(SYSDATE)) ATA
              ON AU.USERNAME = ATA.USERNAME)) OR
       (PA.SDATE IS NOT NULL AND
       AU.USERNAME IN
       (SELECT AU.USERNAME
            FROM ALL_USERS AU
           INNER JOIN DBA_USERS PA
              ON AU.USERNAME = PA.USERNAME
           INNER JOIN ALL_TABLES ATA
              ON PA.USERNAME = ATA.OWNER
           WHERE TO_CHAR(ATA.LAST_ANALYZED, 'YYYY-MM-DD') = PA.SDATE) AND
       AU.USER_ID = PA.USER_ID))
   AND DU.PROFILE LIKE 'D%'
   AND AU.USERNAME LIKE PA.SNAME

針對上面的SQL語句執行慢的問題，我做了如下的分析：

第一步，把語句的WHERE條件後的三個OR都分別和主查詢一塊執行，執行速度都很快，放到一塊就很慢。

第二步，對比上面SQL和三個OR拆分出來的三個SQL的執行計劃，如下圖所示。發現上面SQL的執行中有一個FILTER，過濾器謂詞中用到了NOT EXISTS，是導致這條SQL跑的慢的原因。

原因找到了，就得想辦法把執行計劃的FILTER去掉。開始想加HINT，但是實驗了很多HINT，都不起作用。最後的結果還一樣，後來想到WITH AS 能提高SQL的查詢速度，就把影響SQL執行的那段SQL放到WITH AS裡面，結果還是一樣。後來嘗試把HINT MATERIALIZE和WITH AS 結合使用，修改成如下的SQL，查詢速度立即提升了很多。如下圖所示，執行計劃中FILTER的NOT EXISTS不存在了。 

WITH PARAMS AS
 (SELECT '' USER_ID, '' SDATE, '%' || '' || '%' SNAME FROM DUAL),
USERNAMEDATA AS
 (SELECT /*+ materialize */
   AU.USERNAME
    FROM ALL_USERS AU
   INNER JOIN DBA_USERS DEV
      ON AU.USERNAME = DEV.USERNAME
   INNER JOIN (SELECT OWNER AS USERNAME
                FROM ALL_TABLES T
               WHERE T.LAST_ANALYZED = TRUNC(SYSDATE)) ATA
      ON AU.USERNAME = ATA.USERNAME)
SELECT AU.USERNAME, AU.USER_ID
  FROM ALL_USERS AU
 INNER JOIN PARAMS PA
    ON 1 = 1
 INNER JOIN DBA_USERS DU
    ON AU.USERNAME = DU.USERNAME
 WHERE ((PA.SDATE IS NULL AND PA.USER_ID IS NOT NULL AND
       AU.USER_ID = PA.USER_ID) OR
       
       (PA.SDATE IS NULL AND PA.USER_ID IS NULL AND
       AU.USERNAME NOT IN (SELECT USERNAME FROM USERNAMEDATA)) OR
       (PA.SDATE IS NOT NULL AND
       AU.USERNAME IN
       (SELECT AU.USERNAME
            FROM ALL_USERS AU
           INNER JOIN DBA_USERS PA
              ON AU.USERNAME = PA.USERNAME
           INNER JOIN ALL_TABLES ATA
              ON PA.USERNAME = ATA.OWNER
           WHERE TO_CHAR(ATA.LAST_ANALYZED, 'YYYY-MM-DD') = PA.SDATE) AND
       AU.USER_ID = PA.USER_ID))
   AND DU.PROFILE LIKE 'D%'
   AND AU.USERNAME LIKE PA.SNAME

總結：

在FILTER中，NOT EXISTS後的SQL語句多次執行，本來數據量就很大，每次都要執行一遍，結果可想而知。但是使用HINT MATERIALIZE和WITH AS 結合使用，把內聯視圖實體化，執行過程中會創建基於視圖的臨時表。這樣就不會每次NOT EXISTS都去執行一遍大數據表的掃描，只需要掃描一次即可。

但是是不是可以在WITHAS中的每個語句都實體化那？如果WITH AS中的語句只被調用一次的話，最好還是不要使用HINT MATERIALIZE，因為使用HINT MATERIALIZE第一次查詢會創建基於視圖結果的臨時表，這也耗費一些時間。多次使用的話可以使用HINT MATERIALIZE。