分區 Oracle 表上的部分本地索引

我們有一個大事務表，按事務日期（日）分區。每天，都會添加數百萬條記錄。我們需要在過去五天的時間內在此表上進行選擇，包括 accountid。

為了讓事情保持高性能，我建議在天級別（完成）對錶進行分區，並在 accountid 上創建一個本地索引（帶前綴或不帶前綴）。現在，其中一位團隊成員說這是不可能的，因為他們只需要在表的最後五個分區上創建索引，而這需要太多的努力。

AFAIK，索引的創建只需要進行一次，然後每天只需要創建新分區上的索引。除此之外，超過 5 天的分區上的索引將不再被使用，所以沒有什麼可做的。

您對此有何想法？考慮到性能，如何最好地解決這個問題？

您只能為某些分區創建索引 - 當然這僅適用於 LOCAL 索引。

問題是您必須設置DEFAULT ATTRIBUTES INDEXING ON才能在新分區上創建索引。超過 5 天的索引分區必須手動刪除（即ALTER TABLE ... MODIFY PARTITION ... UNUSABLE LOCAL INDEXES;或）。ALTER INDEX ... MODIFY PARTITION ... UNUSABLE;

當然你可以設置DEFAULT ATTRIBUTES INDEXING OFF但是你必須每天創建（即重建）新的索引分區——這應該是相同的努力。

在由數據庫中的調度程序作業每天執行的 PL/SQL 過程中執行這樣的任務（上述任何一個）都沒什麼大不了的。

初始任務

-- Turn off Indexing for existing partitions
BEGIN
   FOR aPart IN (SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'TABLE1') LOOP
       EXECUTE IMMEDIATE 'ALTER TABLE TABLE1 MODIFY PARTITION '||aPart.PARTITION_NAME||' INDEXING OFF';
   END LOOP;   
END;
/

-- Create the index
CREATE INDEX TABLE1 ON idx (column1, column2) LOCAL INDEXING PARTIAL;

-- Enable indexing, otherwise new partitions will not get indexed.
ALTER TABLE TABLE1 MODIFY DEFAULT ATTRIBUTES INDEXING ON; 

-- Rebuild index on most recent partitions
DECLARE
  ts TIMESTAMP;
BEGIN
   FOR aPart IN (SELECT PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'TABLE1') LOOP
       EXECUTE IMMEDIATE 'BEGIN :ret := '||aPart.HIGH_VALUE||'; END;' USING OUT ts;
       IF ts > SYSTIMESTAMP - INTERVAL '5' DAY THEN
          EXECUTE IMMEDIATE 'ALTER INDEX idx REBUILD PARTITION '||aPart .PARTITION_NAME;
       END IF;
   END LOOP;   
END;
/

日常任務

DECLARE
  ts TIMESTAMP;
BEGIN
   FOR aPart IN (SELECT PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'TABLE1') LOOP
       EXECUTE IMMEDIATE 'BEGIN :ret := '||aPart.HIGH_VALUE||'; END;' USING OUT ts;
       IF ts < SYSTIMESTAMP - INTERVAL '5' DAY THEN
          EXECUTE IMMEDIATE 'ALTER TABLE TABLE1 MODIFY PARTITION '||aPart .PARTITION_NAME||' UNUSABLE LOCAL INDEXES';
       END IF;
   END LOOP;   
END;
/

但是，如果您不查詢超過 5 天的數據，那麼這種方法對性能沒有任何影響，您只會節省一些磁碟空間。

我真的不明白這個說法

需要太多的努力

您應該使用間隔分區(11g+) 根據需要自動創建分區。

使用 12c，您可以擁有分區表的部分索引。

您仍然會為INDEX每個表創建一次本地，而不是每個分區一次。

與往常一樣，您需要執行基準測試以查看哪種索引方法效果最好。

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