739
遠程數據庫的表超過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個索引識別的移動窗口，是按照索引的創建時間來移動的。
綜上：
- 對於通過dblink關聯本地表和遠程表，如果遠程表的索引個數少於20個，那麼不受影響。
- 對於通過dblink關聯本地表和遠程表，如果遠程表的索引個數增加到21個或以上，那麼oracle在執行遠程操作的時候，將忽略最早創建的那個索引，但是會以20個為窗口移動，最新建立的索引會被意識到。此時如果查詢的關聯條件中，使用到最早創建的那個索引的字段，由於忽略了索引，會走全表掃描。
- 要“喚醒”對原來索引的意識，rebuild索引無效，需要drop & create索引。
- 在本地表數據量比較少，遠程表的數據量很大，而索引數量超過20個，且關聯條件的字段時最早索引的情況下，可以考慮使用DRIVING_SITE的hint，將本地表的數據全量到遠程中，此時遠程的關聯查詢可以意識到那個索引。可見文末的例子。是否使用hint，需要評估本地表數據全量推送到遠程的成本，和遠程表使用全表掃的成本。
附：在22個索引的情況下，嚐試采用DRIVING_SITE的hint：
本文來自雲棲社區合作夥伴“數據和雲”，了解相關信息可以關注“數據和雲
最後更新：2017-10-26 11:04:31