【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程序员面试笔试宝典》,作者:李华荣。