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

Oracle使用WITH AS和HINT MATERIALIZE最佳化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

--------------------------------------------------------------------------------

Linux-6-64下安裝Oracle 12C筆記

在CentOS 6.4下安裝Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虛擬機器中安裝步驟

Debian 下 安裝 Oracle 11g XE R2

--------------------------------------------------------------------------------

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

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

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

更多詳情見請繼續閱讀下一頁的精彩內容：

1 2 下一頁