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

DB宝 2019-05-27
3685
♣题目部分在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动态表，此表使用以下命令填充：
1ANALYZE 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表中。这对避免做定时索引重建很有帮助。用户也可以自定义这个历史记录表。
该脚本的内容如下所示：
  1CREATE TABLE index_log (
  2 owner          VARCHAR2(30),
  3 index_name     VARCHAR2(30),
  4 last_inspected DATE,
  5 leaf_blocks    NUMBER,    
  6 target_size    NUMBER,
  7 idx_layout     CLOB);
  8
  9ALTER TABLE index_log ADD CONSTRAINT pk_index_log PRIMARY KEY (owner,index_name);
 10
 11CREATE TABLE index_hist (
 12 owner          VARCHAR2(30),
 13 index_name     VARCHAR2(30),
 14 inspected_date DATE,
 15 leaf_blocks    NUMBER,    
 16 target_size    NUMBER,
 17 idx_layout     VARCHAR2(4000));
 18
 19ALTER TABLE index_hist ADD CONSTRAINT pk_index_hist PRIMARY KEY  (owner,index_name,inspected_date);
 20
 21--
 22-- Variables:
 23--  vMinBlks: Specifies the minimum number of leaf blocks for scanning the index
 24--            Indexes below this number will not be scanned/reported on
 25--  vScaleFactor: The scaling factor, defines the threshold of the estimated leaf block count 
 26--                to be smaller than the supplied fraction of the current size. 
 27--  vTargetUse : Supplied percentage utilisation. For example 90% equates to the default pctfree 10 
 28--  vHistRet : Defines the number of records to keep in the INDEX_HIST table for each index entry
 29--
 30
 31CREATE OR REPLACE PACKAGE index_util AUTHID CURRENT_USER IS
 32vMinBlks     CONSTANT POSITIVE := 1000;
 33vScaleFactor CONSTANT NUMBER := 0.6;
 34vTargetUse   CONSTANT POSITIVE := 90;  -- equates to pctfree 10  
 35vHistRet     CONSTANT POSITIVE := 10;  -- (#) records to keep in index_hist
 36 procedure inspect_schema (aSchemaName IN VARCHAR2);
 37 procedure inspect_index (aIndexOwner IN VARCHAR2, aIndexName IN VARCHAR2, aTableOwner IN VARCHAR2, aTableName IN VARCHAR2, aLeafBlocks IN NUMBER);
 38END index_util; 
 39/
 40
 41CREATE OR REPLACE PACKAGE BODY index_util IS
 42procedure inspect_schema (aSchemaName IN VARCHAR2) IS
 43 begin
 44 FOR r IN (select table_owner, table_name, owner index_owner, index_name, leaf_blocks 
 45           from dba_indexes  
 46           where owner = upper(aSchemaname)
 47             and index_type in ('NORMAL','NORMAL/REV','FUNCTION-BASED NORMAL')
 48             and partitioned = 'NO'  
 49             and temporary = 'N'  
 50             and dropped = 'NO'  
 51             and status = 'VALID'  
 52             and last_analyzed is not null  
 53           order by owner, table_name, index_name) LOOP
 54
 55   IF r.leaf_blocks > vMinBlks THEN
 56   inspect_index (r.index_owner, r.index_name, r.table_owner, r.table_name, r.leaf_blocks);
 57   END IF;
 58  END LOOP;
 59 commit;
 60end inspect_schema;
 61procedure inspect_index (aIndexOwner IN VARCHAR2, aIndexName IN VARCHAR2, aTableOwner IN VARCHAR2, aTableName IN VARCHAR2, aLeafBlocks IN NUMBER) IS
 62 vLeafEstimate number;  
 63 vBlockSize    number;
 64 vOverhead     number := 192; -- leaf block "lost" space in index_stats 
 65 vIdxObjID     number;
 66 vSqlStr       VARCHAR2(4000);
 67 vIndxLyt      CLOB;
 68 vCnt          number := 0;
 69  TYPE IdxRec IS RECORD (rows_per_block number, cnt_blocks number);
 70  TYPE IdxTab IS TABLE OF IdxRec;
 71  l_data IdxTab;
 72begin  
 73 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;
 74 select round (100 / vTargetUse *       -- assumed packing efficiency
 75              (ind.num_rows * (tab.rowid_length + ind.uniq_ind + 4) + sum((tc.avg_col_len) * (tab.num_rows) )  -- column data bytes  
 76              ) / (vBlockSize - vOverhead)  
 77              ) index_leaf_estimate  
 78   into vLeafEstimate  
 79 from (select  /*+ no_merge */ table_name, num_rows, decode(partitioned,'YES',10,6) rowid_length  
 80       from dba_tables
 81       where table_name  = aTableName  
 82         and owner       = aTableOwner) tab,  
 83      (select  /*+ no_merge */ index_name, index_type, num_rows, decode(uniqueness,'UNIQUE',0,1) uniq_ind  
 84       from dba_indexes  
 85       where table_owner = aTableOwner  
 86         and table_name  = aTableName  
 87         and owner       = aIndexOwner  
 88         and index_name  = aIndexName) ind,  
 89      (select  /*+ no_merge */ column_name  
 90       from dba_ind_columns  
 91       where table_owner = aTableOwner  
 92         and table_name  = aTableName 
 93         and index_owner = aIndexOwner   
 94         and index_name  = aIndexName) ic,  
 95      (select  /*+ no_merge */ column_name, avg_col_len  
 96       from dba_tab_cols  
 97       where owner = aTableOwner  
 98         and table_name  = aTableName) tc  
 99 where tc.column_name = ic.column_name  
100 group by ind.num_rows, ind.uniq_ind, tab.rowid_length; 
101
102 IF vLeafEstimate < vScaleFactor * aLeafBlocks THEN
103  select object_id into vIdxObjID
104  from dba_objects  
105  where owner = aIndexOwner
106    and object_name = aIndexName;
107   vSqlStr := 'SELECT rows_per_block, count(*) blocks FROM (SELECT /*+ cursor_sharing_exact ' ||
108             'dynamic_sampling(0) no_monitoring no_expand index_ffs(' || aTableName || 
109             ',' || aIndexName || ') noparallel_index(' || aTableName || 
110             ',' || aIndexName || ') */ sys_op_lbid(' || vIdxObjID || 
111             ', ''L'', ' || aTableName || '.rowid) block_id, ' || 
112             'COUNT(*) rows_per_block FROM ' || aTableOwner || '.' || aTableName || ' GROUP BY sys_op_lbid(' || 
113             vIdxObjID || ', ''L'', ' || aTableName || '.rowid)) group by rows_per_block order by rows_per_block';
114   execute immediate vSqlStr BULK COLLECT INTO l_data;
115  vIndxLyt := '';
116
117   FOR i IN l_data.FIRST..l_data.LAST LOOP
118    vIndxLyt := vIndxLyt || l_data(i).rows_per_block || ' - ' || l_data(i).cnt_blocks || chr(10);
119   END LOOP;
120
121   select count(*) into vCnt from index_log where owner = aIndexOwner and index_name = aIndexName;
122
123   IF vCnt = 0   
124    THEN insert into index_log values (aIndexOwner, aIndexName, sysdate, aLeafBlocks, round(vLeafEstimate,2), vIndxLyt);
125    ELSE vCnt := 0;
126
127         select count(*) into vCnt from index_hist where owner = aIndexOwner and index_name = aIndexName;
128
129         IF vCnt >= vHistRet THEN
130           delete from index_hist
131           where owner = aIndexOwner 
132             and index_name = aIndexName 
133             and inspected_date = (select MIN(inspected_date) 
134                                   from index_hist
135                                   where owner = aIndexOwner 
136                                     and index_name = aIndexName);
137         END IF;
138
139          insert into index_hist select * from index_log where owner = aIndexOwner and index_name = aIndexName;
140
141         update index_log  
142         set last_inspected = sysdate,
143             leaf_blocks = aLeafBlocks, 
144             target_size = round(vLeafEstimate,2),
145             idx_layout = vIndxLyt
146        where owner = aIndexOwner and index_name = aIndexName;
147
148   END IF;
149  END IF;
150 END inspect_index;
151END index_util;
152/
153

复制
该脚本的使用过程如下所示：
1.创建一个用户，为这个用户赋予DBA权限，以及SELECT ON DBA_TABLESPACES权限。
2.执行脚本的代码
如果脚本以SYS外的其他用户执行，在创建包体时，将遭遇ORA-942错误。即使赋予了正确的角色，除非显式授予如下SELECT权限，CREATE PACKAGE BODY还是会失败
1grant select on dba_tablespaces to ;
2grant select on dba_indexes to ;
3grant select on dba_tables to ;
4grant select on dba_ind_columns to ;
5grant select on dba_tab_cols to ;
6grant select on dba_objects to ;
7grant select on v_$parameter to ;

复制
说明：
因为脚本依赖于最新的统计信息，请首先收集统计信息，使之能够看到schema中的变化。
1SQL> exec dbms_stats.gather_schema_stats('SCOTT');
2
3--之后运行下一个procedure：
4
5SQL> exec index_util.inspect_schema ('SCOTT');

复制
注意：此示例代码只为教育目的，Oracle Support不提供技术支持。它已经过内部测试，然而我们无法确保它在任何环境中都能成功使用。请您在使用之前先在测试环境中运行。
查询：
1SELECT OWNER, INDEX_NAME, LAST_INSPECTED, LEAF_BLOCKS, TARGET_SIZE FROM INDEX_LOG;--找到满足条件的索引
2select idx_layout from index_log  where owner='LHR' AND index_name='T_IDX';--可以用来检查索引的布局，第一列列出索引块中的行数，第二列列出具有这个索引条目数的块数
3SELECT TO_CHAR(INSPECTED_DATE, 'DD-MON-YYYY HH24:MI:SS') INSPECTED_DATE, LEAF_BLOCKS,  TARGET_SIZE
4  FROM INDEX_HIST WHERE INDEX_NAME = 'T_IDX';--找出一个索引的变化过程

复制

 
& 说明：
有关索引重建的必要性与影响的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2141341/
 


本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。