更改非相關子查詢的訪問方法

甲骨文 11g R2

不幸的是，我們的應用程序具有每行安全“特性”。我們有一個看起來像這樣的查詢：

壞的，慢的：

SELECT someRow, someOtherRow
FROM bigTableA a
WHERE EXISTS (
 SELECT 0 FROM bigTableA_securitymapping b 
 WHERE b.PrimaryKeyTableA = a.PrimaryKeyTableA AND
      b.accesscode in (SELECT accesscode 
                             FROM accesscodeView 
                             WHERE user = :someUserID)
)

上有一個唯一索引bigTableA_securitymapping(PrimaryKeyTableA,accesscode)。

可能會accesscodeView為給定使用者返回多個訪問碼，因此它必須是IN()而不是=。

問題是此查詢忽略了唯一索引bigTableA_securitymapping並選擇進行全表掃描。

如果我將其更改IN()為an，=那麼它會UNIQUE SCAN在唯一索引上執行a，bigTableA_securitymapping並且速度大約快50倍。

好，快但不可能：

SELECT someRow, someOtherRow
   FROM bigTableA a
   WHERE EXISTS (
     SELECT 0 FROM bigTableA_securitymapping b 
     WHERE b.PrimaryKeyTableA = a.PrimaryKeyTableA AND
          b.accesscode =(SELECT distinct accesscode 
                                 FROM accesscodeView 
                                 WHERE user = :someUserID)
   )

但是，我不能這樣做，因為accesscodeView可能返回不止一行。

（那裡有 a distinct，因為accesscodeView它給出了 的需要=，將DISTINCT放在原始查詢上沒有區別。）

如果我對訪問程式碼進行硬編碼，它還UNIQUE SCAN會對bigTableA_securitymapping.

好，快，但需要大量的應用程序更改：

SELECT someRow, someOtherRow
     FROM bigTableA a
     WHERE EXISTS (
       SELECT 0 FROM bigTableA_securitymapping b 
       WHERE b.PrimaryKeyTableA = a.PrimaryKeyTableA AND
            b.accesscode in (1,2,3,4)
     )

更改為內部連接也無濟於事。它仍然進行全表掃描。

壞的，慢的：

SELECT someRow, someOtherRow
FROM bigTableA a
WHERE EXISTS (
 SELECT 0 FROM accesscode ac INNER JOIN 
               bigTableA_securitymapping b ON
                 ac.accesscode = b.accesscode         
 WHERE b.PrimaryKeyTableA = a.PrimaryKeyTableA 
       AND user = :someUserID
)

那麼為什麼=和IN()in之間有區別呢？為什麼一個不相關的子查詢（accesscodeview子查詢）會導致這樣的計劃差異呢？有沒有辦法重寫它來做我想做的事？這裡“好計劃”成本與“壞計劃”成本的差異為 87 與 37,000，並且在實際執行時需要大量時間才能獲得相同的結果。

如果您的統計資訊過時（包括系統和表統計資訊）或者您的初始化參數未優化，數據庫可能會忽略您的索引。例如，對於大多數應用程序來說，預設的優化器成本調整可能是錯誤的。

這就是我會做的，雖然我在這裡沒有完全看到整個畫面，也許我錯過了一些東西。如果沒有用，一個例子將有助於完善我的答案。

SELECT someRow, someOtherRow
   FROM bigTableA a
   WHERE EXISTS (
     SELECT 0 FROM bigTableA_securitymapping b 
     WHERE b.PrimaryKeyTableA = a.PrimaryKeyTableA 
          AND EXISTS (SELECT 1 FROM accesscodeView 
                         WHERE user = :someUserID
                         AND accesscode = b.accesscode)
   )

它總是返回一行而不是掃描一秒鐘（或更多，如果有更多）

引用自：https://dba.stackexchange.com/questions/60686