【恩墨學院】遠程數據庫的表超過20個索引的影響

【恩墨學院】遠程數據庫的表超過20個索引的影響

何劍敏

昨天同事參加了一個研討會,有提到一個案例。一個通過dblink查詢遠端數據庫，原來查詢很快，但是遠端數據庫增加了一個索引之后，查詢一下子變慢了。

經過分析，發現那個通過dblink的查詢語句，查詢遠端數據庫的時候，是走索引的，但是遠端數據庫添加索引之后，如果索引的個數超過20個，就會忽略第一個建立的索引，如果查詢語句恰好用到了第一個建立的索引，被忽略之后，只能走Full Table Scan了。

聽了這個案例，我查了一下，在oracle數據庫官方文檔中，關于Managing a Distributed Database有一段話：

Several performance restrictions relate to access of remote objects:

Remote views do not have statistical data.
Queries on partitioned tables may not be optimized.
No more than 20 indexes are considered for a remote table.
No more than 20 columns are used for a composite index.

說到，如果遠程數據庫使用超過20個索引，這些索引將不被考慮。這段話，在oracle 9i起的文檔中就已經存在，一直到12.2還有。

那么，超過20個索引，是新的索引被忽略了？還是老索引被忽略了？如何讓被忽略的索引讓oracle數據庫意識到?我們來測試一下。

可以看到，遠程表有27個字段，目前還只是在前20個字段建立了索引，且第一個字段是主鍵。本地表，有6個字段，6個字段都建索引。

測試場景1：

在遠程表20索引的情況下，本地表和遠程表關聯，用本地表的第一個字段關聯遠程表的第一個字段：

我們可以看到，對于遠程表的執行計劃，這是走主鍵的。

測試場景2：

在遠程表20索引的情況下，本地表和遠程表關聯，用本地表的第一個字段關聯遠程表的第20個字段：

我們可以看到，對于遠程表的執行計劃，這是走索引范圍掃描的。

測試場景3：

在遠程表20索引的情況下，本地表和遠程表關聯，用本地表的第2個字段關聯遠程表的第2個字段：

我們可以看到，對于遠程表的執行計劃，這是走索引范圍掃描的。

測試場景4：

在遠程表20索引的情況下，本地表和遠程表關聯，用本地表的第2個字段關聯遠程表的第20個字段：

我們可以看到，對于遠程表的執行計劃，這是走索引范圍掃描的。

測試場景1：

我們可以看到，對于遠程表的執行計劃，如果關聯條件是遠程表的第一個字段，第一個字段上的索引是被忽略的，執行計劃是選擇全表掃描的。

測試場景2：

我們可以看到，對于遠程表的執行計劃，如果關聯條件是遠程表的第20個字段，這第20個字段上的索引是沒有被忽略的，執行計劃是走索引。

測試場景3：

我們可以看到，對于遠程表的執行計劃，如果關聯條件是遠程表的第2個字段，這第2個字段上的索引是沒有被忽略的，執行計劃是走索引。

測試場景4：

我們可以看到，對于遠程表的執行計劃，如果關聯條件是遠程表的第20個字段，這第20個字段上的索引是沒有被忽略的，執行計劃是走索引。

我們目前可以總結到，當遠程表第21個索引建立的時候，通過dblink關聯本地表和遠程表，如果關聯條件是遠程表的第1個建立的索引的字段，那么這個索引將被忽略，從而走全表掃描。如果關聯條件是遠程表的第2個建立索引的字段，則不受影響。
似乎是有效索引的窗口是20個，當新建第21個，那么第1個就被無視了。

測試場景1：

測試場景2：

測試場景3：

測試場景4：

上述的測試，其實是可以驗證我們的猜測的。oracle數據庫對于通過dblink關聯訪問遠程表，只是會意識到最近創建的20個索引的字段。這個意識到索引的窗口是20個，一旦建立了一個新索引，那么最舊的一個索引會被無視。

rebuild第2個索引

所以我們看到，索引rebuild，是不能起到重新“喚醒”索引的作用。

我們可以看到，通過drop之后再重建，是可以“喚醒”第二個索引的。這也證明了我們20個索引識別的移動窗口，是按照索引的創建時間來移動的。

綜上：

1. 對于通過dblink關聯本地表和遠程表，如果遠程表的索引個數少于20個，那么不受影響。

2. 對于通過dblink關聯本地表和遠程表，如果遠程表的索引個數增加到21個或以上，那么oracle數據庫在執行遠程操作的時候，將忽略最早創建的那個索引，但是會以20個為窗口移動，最新建立的索引會被意識到。此時如果查詢的關聯條件中，使用到最早創建的那個索引的字段，由于忽略了索引，會走全表掃描。

3. 要“喚醒”對原來索引的意識，rebuild索引無效，需要drop & create索引。

4. 在本地表數據量比較少，遠程表的數據量很大，而索引數量超過20個，且關聯條件的字段時最早索引的情況下，可以考慮使用DRIVING_SITE的hint，將本地表的數據全量到遠程中，此時遠程的關聯查詢可以意識到那個索引。可見文末的例子。是否使用hint，需要評估本地表數據全量推送到遠程的成本，和遠程表使用全表掃的成本。

附：在22個索引的情況下，嘗試采用DRIVING_SITE的hint：

恩墨學院隸屬于云和恩墨（北京）信息技術有限公司，致力于提供專業高水準的oracle數據庫與大數據培訓服務，挖掘培養大數據與數據庫人才。恩墨學院提供包括個人實戰技能培訓、個人認證培訓、企業內訓在內的全方位大數據和數據庫技術培訓。ACE級別超強師資，配備專業實驗室，沉浸式學習與訓練，專業實驗室、配備專業助教指導訓練。能迅速融入專家圈子，業內資源豐富，迅速積累職場人脈。oracle數據庫課程包括：Oracle DBA實戰班、Oracle OCM考試、Oracle OCP考試等。