关于重建索引有用与否的讨论有很多。一般而言，极少需要重建 B 树索引，基本原因是 B 树索引很大程度上可以自我管理或自我平衡。
认为需要重建索引的最常见理由有：
– 索引碎片在不断增加
– 索引不断增加，删除的空间没有重复使用
– 索引 clustering factor （群集因子）不同步
事实上，大多数索引都能保持平衡和完整，因为空闲的叶条目可以重复使用。插入/更新和删除操作确实会导致索引块周围的可用空间形成碎片，但是一般来说这些碎片都会被正确的重用。
Clustering factor 群集因子可以反映给定的索引键值所对应的表中的数据排序情况。重建索引不会对群集因子产生影响，要改变集群因子只能通过重组表的数据。
另外，重建索引的影响非常明显，请仔细阅读以下说明：
1. 大多数脚本都依赖 index_stats 动态表。此表使用以下命令填充：
analyze index ... validate structure;
尽管这是一种有效的索引检查方法，但是它在分析索引时会获取独占表锁。特别对于大型索引，它的影响会是巨大的，因为在此期间不允许对表执行 DML 操作。虽然该方法可以在不锁表的情况下在线运行，但是可能要消耗额外的时间。
2. 重建索引的直接结果是 REDO 活动可能会增加，总体的系统性能可能会受到影响。
插入/更新/删除操作会导致索引随着索引的分割和增长不断发展。重建索引后，它将连接的更为紧凑；但是，随着对表不断执行 DML 操作，必须再次分割索引，直到索引达到平衡为止。结果，重做活动增加，且索引分割更有可能对性能产生直接影响，因为我们需要将更多的 I/O、CPU 等用于索引重建。经过一段时间后，索引可能会再次遇到“问题”，因此可能会再被标记为重建，从而陷入恶性循环。因此，通常最好是让索引处于自然平衡和（或）至少要防止定期重建索引。
3. 通常是优先考虑index coalesce（索引合并），而不是重建索引。索引合并有如下优点：
- 不需要占用近磁盘存储空间 2 倍的空间 - 可以在线操作 - 无需重建索引结构，而是尽快地合并索引叶块，这样可避免系统开销过大，请见第 2 点中的解释。 注意：例如，如要将索引转移到其他表空间，则需要重建索引。
综上所述，强烈建议不要定期重建索引，而应使用合适的诊断工具，下面分享一个Oracle推荐的分析索引结构的脚本以及使用建议：
1）. 创建一个将包含索引统计信息表的用户
2）. 为此用户分配“dba”权限。并授予选择dba_tablespaces;<user>
3）. 执行位于脚本部分中的代码。
如果脚本以 SYS 以外的用户身份运行，则在创建包正文时可能会遇到 ORA-942 错误。
使用定义者权限调用过程时，角色将丢失，因此除非显式授予以下 SELECT 权限，否则创建包正文将失败：
grant select on dba_tablespaces to <user>; grant select on dba_indexes to <user>; grant select on dba_tables to <user>; grant select on dba_ind_columns to <user>; grant select on dba_tab_cols to <user>; grant select on dba_objects to <user>; grant select on v_$parameter to <user>;
并且执行分析脚本前需要分析索引的统计信息，以下为例：
SQL> exec dbms_stats.gather_schema_stats(‘SCOTT’);
SQL> exec index_util.inspect_schema (‘SCOTT’);
脚本样例：
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;
/
脚本执行输出的样例：
SQL> select owner, index_name, last_inspected, leaf_blocks, target_size from index_logOWNER INDEX_NAME LAST_INSP LEAF_BLOCKS TARGET_SIZE ------------------------------ ------------------------------ --------- ----------- ----------- SYS I_ARGUMENT1 17-JUN-10 432 303 SYS I_ARGUMENT2 17-JUN-10 282 186 SYS I_COL1 17-JUN-10 288 182 SYS I_DEPENDENCY1 17-JUN-10 109 103 SYS I_DEPENDENCY2 17-JUN-10 136 95 SYS I_H_OBJ#_COL# 17-JUN-10 258 104 SYS WRH$_SQL_PLAN_PK 17-JUN-10 118 59 SYS WRI$_ADV_PARAMETERS_PK 17-JUN-10 210 121 SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST 17-JUN-10 2268 1313 SYS I_WRI$_OPTSTAT_H_ST 17-JUN-10 1025 963 SYS I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST 17-JUN-10 338 191
SQL> select idx_layout from index_log where owner='SCOTT' and index_name='T_IDX'; IDX_LAYOUT ------------ 104 - 1 204 - 1 213 - 1 219 - 1 221 - 2 222 - 1 223 - 2 224 - 1 225 - 1 230 - 1 231 - 3 235 - 3 236 - 1 238 - 3 239 - 2 241 - 1 242 - 2 243 - 1 245 - 3 247 - 1 249 - 1 250 - 1 252 - 3 255 - 1 257 - 2 263 - 2 264 - 1 267 - 1 268 - 1 276 - 1 283 - 1 296 - 1 345 - 1
SQL> 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'; INSPECTED_DATE LEAF_BLOCKS TARGET_SIZE -------------------- ----------- ----------- 10-MAR-2010 10:04:04 432 303 10-APR-2010 10:04:03 435 430 10-MAY-2010 10:04:02 431 301
索引重建的必要性与影响以及相关分析索引结构的脚本
