ORACLE使用WITH AS和HINT MATERIALIZE最佳化SQL解決FILTER效率低下

標籤：rom   計劃   ast   div   語句   內聯   like   lib   with   



在做項目的過程中，一個頁面使用類似例如以下的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裡面，結果還是一樣。後來嘗試把HINTMATERIALIZE和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

watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvbGlhbmd3ZWl3ZWkxMzA=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast" >

總結：

在FILTER中。NOT EXISTS後的SQL語句多次運行，本來資料量就非常大，每次都要運行一遍，結果可想而知。可是使用HINTMATERIALIZE和WITH AS結合使用，把內聯視圖實體化，運行過程中會建立基於視圖的暫時表。

這樣就不會每次NOT EXISTS都去運行一遍大資料表的掃描，僅僅須要掃描一次就可以。

可是是不是能夠在WITHAS中的每一個語句都實體化那？假設WITH AS中的語句僅僅被調用一次的話，不妨不要使用HINTMATERIALIZE。由於使用HINTMATERIALIZE第一次查詢會建立基於視圖結果的暫時表，這也耗費一些時間。

多次使用的話能夠使用HINTMATERIALIZE。

ORACLE使用WITH AS和HINT MATERIALIZE最佳化SQL解決FILTER效率低下