即使在添加索引後也進行全表掃描

我在 Oracle 11g 伺服器中有兩個表，一個是分區的，另一個是 3GB 的數據。我已經根據 where 子句在兩個表上添加了一個複合索引，按照它出現的順序。收集統計資訊後解釋計劃的輸出顯示該 sql 正在以全掃描模式訪問兩個表。範例查詢如下。

select /*+ PARALLEL(a 8) */ b.col1, a.* from tablea a, tableb b partition(part_00)
       where
       b.col1=a.col1
       a.col2 is null
       b.col3 <= a.col3
       b.col4>a.col4
       b.col4=a.col4

複合的索引。我在下面列出了它們。下面給出了在創建索引語法中指定的順序。

create index idx_composite_tableb(col4, col3, col1) local;
create index idx_composite_tablea(col4, col2);

提前致謝。

我假設您希望 Oracle 優化器選擇 tableb 作為驅動程序表，並且對於 tableb 中的每一行，讓它對 tablea 執行索引掃描（因為 tablea 有 3GB 的數據）。

您的索引以 col4 開頭，所以我猜您希望其中一個將被優化器使用。

需要指出的一件事是，在您的 WHERE 子句中，您同時擁有：

b.col4>a.col4
b.col4=a.col4

最後一個條件 (b.col4=a.col4) 應該給優化器一個提示以對 tablea 使用索引掃描。然而，最後一個 (b.col4>a.col4) 可能會否定該選項。此時的優化器可能會意識到可能存在大量索引掃描乘以 tableb 中的行數。正如其他人指出的那樣，索引掃描（針對 tablea）然後是表行檢索（針對 tablea）可能很快變得比對 tablea 的直接表掃描更昂貴。

最後一點：我還假設您的 WHERE 子句都是 ANDed。如果是這樣的話

b.col4>a.col4
AND
b.col4=a.col4

永遠不應該是 TRUE。基本上你正在尋找 A>B AND A=B，這應該總是錯誤的。

這可能與您的謂詞的選擇性有關。如果查詢使用索引，它會承受從磁碟讀取構成索引本身的頁面的成本。相反，如果它執行表掃描，則會有檢索數據的成本，它將忽略。這兩個選項的相對成本將取決於索引的選擇性、數據的聚集方式以及索引是否覆蓋。優化器將選擇實際工作最少的選項。擁有最新的統計數據有助於它很好地做到這一點。

可能是您未顯示的查詢的其他部分（例如 aorder by或group by例如）在使用索引時會比使用表掃描時產生更多成本。例如，如果查詢有一個order by與表的分群順序相同的查詢，則可以通過按順序處理它們從磁碟中取出的行來消除代價高昂的排序。

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