ANBOB™

认为需要重建索引的最常见理由有：
– 索引碎片在不断增加
– 索引不断增加，删除的空间没有重复使用 ,If ‘HEIGHT’ is greater than 4.
– 索引 clustering factor （群集因子）不同步

事实上，大多数索引都能保持平衡和完整，因为空闲的叶条目可以重复使用。插入/更新和删除操作确实会导致索引块周围的可用空间形成碎片，但是一般来说这些碎片都会被正确的重用。
Clustering factor 群集因子可以反映给定的索引键值所对应的表中的数据排序情况。重建索引不会对群集因子产生影响，要改变集群因子只能通过重组表的数据。

1. 大多数脚本都依赖 index_stats 动态表。此表使用以下命令填充：

analyze index … validate structure;

If the percentage of deleted rows exceeds 30% of the total. So if del_lf_rows / lf_rows> 0.3 in the sys.index_stats table.

尽管这是一种有效的索引检查方法，但是它在分析索引时会获取独占表锁。特别对于大型索引，它的影响会是巨大的，因为在此期间不允许对表执行 DML 操作。虽然该方法可以在不锁表的情况下在线运行，但是可能要消耗额外的时间。

note: validate structure online只是验证索引结构如分析索引坏块，不会填充任何统计信息.

2. 重建索引的直接结果是 REDO 活动可能会增加，总体的系统性能可能会受到影响。

重建索引后，它将连接的更为紧凑；但是，随着对表不断执行 DML 操作，必须再次分割索引，直到索引达到平衡为止。

3. 通常是优先考虑index coalesce（索引合并），而不是重建索引。索引合并有如下优点：

– 不需要占用近磁盘存储空间 2 倍的空间

– 可以在线操作

– 无需重建索引结构，而是尽快地合并索引叶块，这样可避免系统开销过大，请见第 2 点中的解释。

由于上面列出的原因，强烈建议不要定期重建索引，而是使用适当的诊断。不过也可以基于表段和索引段的大小对比，如果索引段接近表段或大于表段时，很有必要重建索引（当然不是索引key和表列一样多）。

注意：例如，如要将索引转移到其他表空间，则需要重建索引。

基于统计信息分析索引的方法

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;
/

Reference Index Rebuild, the Need vs the Implications [989093.1]

打赏

微信扫一扫，打赏作者吧～