【DB筆試面試568】在Oracle中，索引是否必須定期重建？索引重建有哪些影響？

  • 2019 年 10 月 10 日
  • 筆記

♣

題目部分

在Oracle中，索引是否必須定期重建？索引重建有哪些影響？

♣

答案部分

一般而言，極少需要重建B樹索引，基本原因是B樹索引很大程度上可以自我管理或自我平衡。認為需要重建索引的最常見理由有：

l B-Tree索引隨著時間的推移變得不平衡（錯誤的認識）；

l 索引碎片在不斷增加，但是這些碎片會被重用；

l 索引不斷增加，刪除的空間沒有重複使用（錯誤的認識）；

l 索引聚簇因子（Clustering Factor）不同步，可以通過重建修復（錯誤的認識）。

事實上，由於空閑的索引葉條目可以重複使用，所以大多數索引都能保持平衡和完整，插入、更新和刪除操作確實會導致索引塊周圍的可用空間形成碎片，但是一般來說這些碎片都會被正確的重用。聚簇因子可以反映給定的索引鍵值所對應的表中的數據排序情況。重建索引不會對聚簇因子產生影響，要改變聚簇因子只能通過重組表的數據。

若是重建索引，則建議對以下的索引進行重建：

①　在分析（ANALYZE）指定索引之後，查詢INDEX_STATS的HEIGHT欄位的值，如果HEIGHT>=4即索引深度超過3級，那麼最好重建（REBUILD）這個索引，但是如果這個值一直保持不變，那麼這個索引也就不需要重建。

②　在分析（ANALYZE）指定索引之後，查詢INDEX_STATS的DEL_LF_ROWS和LF_ROWS的值，如果(DEL_LF_ROWS/LF_ROWS)*100>=20即已刪除的索引條目至少佔有現有索引條目總數的20%，那麼表示這個索引也需要重建。

重建索引的影響非常明顯，主要有以下幾點：

（1）大多數腳本都依賴INDEX_STATS動態表，此表使用以下命令填充：

ANALYZE INDEX ... VALIDATE STRUCTURE;

儘管這是一種有效的索引檢查方法，但是它在分析索引時會獲取獨佔表鎖。特別對於大型索引，它的影響會是巨大的，因為在此期間不允許對錶執行DML操作。雖然該方法可以在不鎖表的情況下在線運行，但是可能要消耗額外的時間。

（2）重建索引的直接結果是Redo活動可能會增加，總體的系統性能可能會受到影響。

插入、更新、刪除操作會導致索引隨著索引的分割和增長不斷發展。重建索引後，它將連接的更為緊湊；但是，隨著對錶不斷執行DML操作，必須再次分割索引，直到索引達到平衡為止。結果，重做活動增加，且索引分割更有可能對性能產生直接影響，因為需要將更多的I/O、CPU等用於索引重建。經過一段時間後，索引可能會再次遇到「問題」，因此可能會再被標記為重建，從而陷入惡性循環。因此，通常最好是讓索引處於自然平衡和（或）至少要防止定期重建索引。

（3）通常是優先考慮索引合併（INDEX COALESCE），而不是重建索引。索引合併有如下優點：

l 不需要佔用過多的磁碟空間。

l 可以在線操作。

l 無需重建索引結構，而是儘快地合併索引葉塊，這樣可避免系統開銷過大。

如果將索引轉移到其它表空間，那麼需要重建索引。

綜上所述，Oracle強烈建議不要定期重建索引，而應使用合適的診斷工具。為此，Oracle在Mos中給出了相關分析的腳本：「研究 b-tree 索引結構的腳本 (文檔 ID 1577374.1)」。這個腳本將根據已存在的表和索引的統計資訊來核實B-Tree索引結構，並可以估計索引的理論大小和索引布局，而且該腳本會將收集的資訊以歷史記錄的形式保存在INDEX_HIST表中。這對避免做定時索引重建很有幫助。用戶也可以自定義這個歷史記錄表。

該腳本的內容如下所示：

CREATE TABLE index_log (   owner          VARCHAR2(30),   index_name     VARCHAR2(30),   last_inspected DATE,   leaf_blocks    NUMBER,   target_size    NUMBER,   idx_layout     CLOB);    ALTER TABLE index_log ADD CONSTRAINT pk_index_log PRIMARY KEY (owner,index_name);    CREATE TABLE index_hist (   owner          VARCHAR2(30),   index_name     VARCHAR2(30),   inspected_date DATE,   leaf_blocks    NUMBER,   target_size    NUMBER,   idx_layout     VARCHAR2(4000));    ALTER TABLE index_hist ADD CONSTRAINT pk_index_hist PRIMARY KEY  (owner,index_name,inspected_date);    --  -- Variables:  --  vMinBlks: Specifies the minimum number of leaf blocks for scanning the index  --            Indexes below this number will not be scanned/reported on  --  vScaleFactor: The scaling factor, defines the threshold of the estimated leaf block count  --                to be smaller than the supplied fraction of the current size.  --  vTargetUse : Supplied percentage utilisation. For example 90% equates to the default pctfree 10  --  vHistRet : Defines the number of records to keep in the INDEX_HIST table for each index entry  --    CREATE OR REPLACE PACKAGE index_util AUTHID CURRENT_USER IS  vMinBlks     CONSTANT POSITIVE := 1000;  vScaleFactor CONSTANT NUMBER := 0.6;  vTargetUse   CONSTANT POSITIVE := 90;  -- equates to pctfree 10  vHistRet     CONSTANT POSITIVE := 10;  -- (#) records to keep in index_hist   procedure inspect_schema (aSchemaName IN VARCHAR2);   procedure inspect_index (aIndexOwner IN VARCHAR2, aIndexName IN VARCHAR2, aTableOwner IN VARCHAR2, aTableName IN VARCHAR2, aLeafBlocks IN NUMBER);  END index_util;  /    CREATE OR REPLACE PACKAGE BODY index_util IS  procedure inspect_schema (aSchemaName IN VARCHAR2) IS   begin   FOR r IN (select table_owner, table_name, owner index_owner, index_name, leaf_blocks             from dba_indexes             where owner = upper(aSchemaname)               and index_type in ('NORMAL','NORMAL/REV','FUNCTION-BASED NORMAL')               and partitioned = 'NO'               and temporary = 'N'               and dropped = 'NO'               and status = 'VALID'               and last_analyzed is not null             order by owner, table_name, index_name) LOOP       IF r.leaf_blocks > vMinBlks THEN     inspect_index (r.index_owner, r.index_name, r.table_owner, r.table_name, r.leaf_blocks);     END IF;    END LOOP;   commit;  end inspect_schema;  procedure inspect_index (aIndexOwner IN VARCHAR2, aIndexName IN VARCHAR2, aTableOwner IN VARCHAR2, aTableName IN VARCHAR2, aLeafBlocks IN NUMBER) IS   vLeafEstimate number;   vBlockSize    number;   vOverhead     number := 192; -- leaf block "lost" space in index_stats   vIdxObjID     number;   vSqlStr       VARCHAR2(4000);   vIndxLyt      CLOB;   vCnt          number := 0;    TYPE IdxRec IS RECORD (rows_per_block number, cnt_blocks number);    TYPE IdxTab IS TABLE OF IdxRec;    l_data IdxTab;  begin   select a.block_size into vBlockSize from dba_tablespaces a,dba_indexes b where b.index_name=aIndexName and b.owner=aIndexOwner and a.tablespacE_name=b.tablespace_name;   select round (100 / vTargetUse *       -- assumed packing efficiency                (ind.num_rows * (tab.rowid_length + ind.uniq_ind + 4) + sum((tc.avg_col_len) * (tab.num_rows) )  -- column data bytes                ) / (vBlockSize - vOverhead)                ) index_leaf_estimate     into vLeafEstimate   from (select  /*+ no_merge */ table_name, num_rows, decode(partitioned,'YES',10,6) rowid_length         from dba_tables         where table_name  = aTableName           and owner       = aTableOwner) tab,        (select  /*+ no_merge */ index_name, index_type, num_rows, decode(uniqueness,'UNIQUE',0,1) uniq_ind         from dba_indexes         where table_owner = aTableOwner           and table_name  = aTableName           and owner       = aIndexOwner           and index_name  = aIndexName) ind,        (select  /*+ no_merge */ column_name         from dba_ind_columns         where table_owner = aTableOwner           and table_name  = aTableName           and index_owner = aIndexOwner           and index_name  = aIndexName) ic,        (select  /*+ no_merge */ column_name, avg_col_len         from dba_tab_cols         where owner = aTableOwner           and table_name  = aTableName) tc   where tc.column_name = ic.column_name   group by ind.num_rows, ind.uniq_ind, tab.rowid_length;     IF vLeafEstimate < vScaleFactor * aLeafBlocks THEN    select object_id into vIdxObjID    from dba_objects    where owner = aIndexOwner      and object_name = aIndexName;     vSqlStr := 'SELECT rows_per_block, count(*) blocks FROM (SELECT /*+ cursor_sharing_exact ' ||               'dynamic_sampling(0) no_monitoring no_expand index_ffs(' || aTableName ||               ',' || aIndexName || ') noparallel_index(' || aTableName ||               ',' || aIndexName || ') */ sys_op_lbid(' || vIdxObjID ||               ', ''L'', ' || aTableName || '.rowid) block_id, ' ||               'COUNT(*) rows_per_block FROM ' || aTableOwner || '.' || aTableName || ' GROUP BY sys_op_lbid(' ||               vIdxObjID || ', ''L'', ' || aTableName || '.rowid)) group by rows_per_block order by rows_per_block';     execute immediate vSqlStr BULK COLLECT INTO l_data;    vIndxLyt := '';       FOR i IN l_data.FIRST..l_data.LAST LOOP      vIndxLyt := vIndxLyt || l_data(i).rows_per_block || ' - ' || l_data(i).cnt_blocks || chr(10);     END LOOP;       select count(*) into vCnt from index_log where owner = aIndexOwner and index_name = aIndexName;       IF vCnt = 0      THEN insert into index_log values (aIndexOwner, aIndexName, sysdate, aLeafBlocks, round(vLeafEstimate,2), vIndxLyt);      ELSE vCnt := 0;             select count(*) into vCnt from index_hist where owner = aIndexOwner and index_name = aIndexName;             IF vCnt >= vHistRet THEN             delete from index_hist             where owner = aIndexOwner               and index_name = aIndexName               and inspected_date = (select MIN(inspected_date)                                     from index_hist                                     where owner = aIndexOwner                                       and index_name = aIndexName);           END IF;              insert into index_hist select * from index_log where owner = aIndexOwner and index_name = aIndexName;             update index_log           set last_inspected = sysdate,               leaf_blocks = aLeafBlocks,               target_size = round(vLeafEstimate,2),               idx_layout = vIndxLyt          where owner = aIndexOwner and index_name = aIndexName;       END IF;    END IF;   END inspect_index;  END index_util;  /

該腳本的使用過程如下所示：

1.創建一個用戶，為這個用戶賦予DBA許可權，以及SELECT ON DBA_TABLESPACES許可權。

2.執行腳本的程式碼

如果腳本以SYS外的其他用戶執行，在創建包體時，將遭遇ORA-942錯誤。即使賦予了正確的角色，除非顯式授予如下SELECT許可權，CREATE PACKAGE BODY還是會失敗

grant select on dba_tablespaces to ;  grant select on dba_indexes to ;  grant select on dba_tables to ;  grant select on dba_ind_columns to ;  grant select on dba_tab_cols to ;  grant select on dba_objects to ;  grant select on v_$parameter to ;

說明：

因為腳本依賴於最新的統計資訊，請首先收集統計資訊，使之能夠看到schema中的變化。

SQL> exec dbms_stats.gather_schema_stats('SCOTT');    --之後運行下一個procedure：    SQL> exec index_util.inspect_schema ('SCOTT');

注意：此示例程式碼只為教育目的，Oracle Support不提供技術支援。它已經過內部測試，然而我們無法確保它在任何環境中都能成功使用。請您在使用之前先在測試環境中運行。

查詢：

SELECT OWNER, INDEX_NAME, LAST_INSPECTED, LEAF_BLOCKS, TARGET_SIZE FROM INDEX_LOG;--找到滿足條件的索引  select idx_layout from index_log  where owner='LHR' AND index_name='T_IDX';--可以用來檢查索引的布局，第一列列出索引塊中的行數，第二列列出具有這個索引條目數的塊數  SELECT TO_CHAR(INSPECTED_DATE, 'DD-MON-YYYY HH24:MI:SS') INSPECTED_DATE, LEAF_BLOCKS,  TARGET_SIZE    FROM INDEX_HIST WHERE INDEX_NAME = 'T_IDX';--找出一個索引的變化過程

& 說明：

有關索引重建的必要性與影響的更多內容可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-2141341/

本文選自《Oracle程式設計師面試筆試寶典》，作者：李華榮。

Tags:

VirMach 便宜 VPS