­

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