Oracle索引合并coalesce操作

索引rebuild和rebuild online是运维环境中经常遇到的问题。但是无论哪种，大数据索引对象的rebuild都是消耗资源的大规模操作，都需要进行时间窗规划，避免对在线系统运行有影响。

本篇主要介绍对索引的另一种精简操作方法：coalesce合并。从之前的讨论我们已经知道，索引结构一般是一个不断“退化”的平衡结构，如果有一个新值加入，就可能会伴随叶子节点拓展，甚至包括分支节点创建。而一个值被删除修改，叶子节点只是被标注为已删除，不会进行节点合并和回收。这样，正常环境下的索引应该是叶子“支离破碎”、“缓慢膨胀”的段结构。

回收空间、让叶子节点更加紧密是管理员考虑rebuild的基本出发动机。紧密的新索引的确空间占用比较小，检索速度也较快。但是之后插入、更新、删除的过程后，依然伴随着空间分配过程的损耗。所以，笔者个人认为：也许健康的索引结构就应该是“支离破碎”、“缓慢膨胀”。Coalesce操作提供的一种逻辑重组索引的方式，仅对索引树进行重组，不进行数据回收。

1、环境介绍

笔者选择11gR2进行实验。

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE11.2.0.3.0Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

创建数据表T，构建索引。

SQL> create table t as select * from dba_objects ;

Table created

SQL> create index idx_t_id on t(object_id);

Index created

为了模拟效果，删除大部分数据构成死节点。

SQL> select max(object_id) from t;

MAX(OBJECT_ID)

--------------

164092

SQL> delete t where object_id<164092;

77405 rows deleted

SQL> commit;

Commit complete

重新收集统计量。

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

SQL> select count(*) from t;

COUNT(*)

----------

1

2、coalesce操作

Delete操作既不会回收数据段，也不会回收索引段。当前一行数据表T对应的段信息如下：

SQL> select EXTENT_ID,FILE_ID,BLOCK_ID,BYTES,BLOCKS from dba_extents where owner='SYS' and segment_name='T';

EXTENT_IDFILE_IDBLOCK_IDBYTESBLOCKS

---------- ---------- ---------- ---------- ----------

0186984655368

1186992655368

2187000655368

3187008655368

4187016655368

5187024655368

6187032655368

7188960655368

8188968655368

9188976655368

10188984655368

11188992655368

12189000655368

13189008655368

14190360655368

15191008655368

161890881048576128

171892161048576128

181893441048576128

191894721048576128

201896001048576128

211897281048576128

221898561048576128

231899841048576128

24 rows selected

索引段如下：

0191704655368

1191712655368

2191720655368

3191728655368

4191736655368

5191744655368

6191752655368

7191760655368

8191768655368

9192544655368

10192552655368

11192560655368

12192568655368

13192576655368

14192584655368

15192592655368

161917761048576128

17 rows selected

多extent结构，表示结构没有回收。下面使用analyze语句分析一下索引的情况：

SQL>analyze index idx_t_id validate structure;

Index analyzed

SQL> select height,blocks,lf_rows,lf_blks,lf_rows_len,lf_blk_len,br_rows,br_blks,del_lf_rows from index_stats;

HEIGHTBLOCKSLF_ROWSLF_BLKS LF_ROWS_LEN LF_BLK_LENBR_ROWSBR_BLKS DEL_LF_ROWS

---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- -----------

22567740617212277927996171177405

索引树两层结构，包括了256个数据库，叶子节点包括77406个，被删除节点77405个。

开启10046事件跟踪coalesce过程操作。

SQL> select value from v$diag_info where name='Default Trace File';

VALUE

/home/oracle/app/diag/rdbms/awpdb/awpdb/trace/awpdb_ora_14931.trc

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

SQL>alter index idx_t_id coalesce;

Index altered.

SQL> alter session set events '10046 trace name context off';

操作之后检查一下结构效果。

2191720655368

6191752655368

索引段存储分配没有发生变化，还是17个extent。但是索引逻辑结构已经变化：

SQL> analyze index idx_t_id validate structure;

225611167996010

索引高度和分配块数量没有变化，但是叶子节点进行了重组。被删除数据节点被整理合并。

3、10046文件分析

从10046事件文件分析的情况看，如下：

=====================

PARSING IN CURSOR#139851695602760len=29 dep=0 uid=0 oct=11 lid=0 tim=1427182487640740 hv=4054144165 ad='aa2f2710' sqlid='a88sghvsuap55'

alter index idx_t_id coalesce

END OF STMT

PARSE #139851695602760:c=17997,e=56662,p=9,cr=117,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1427182487640739

根据游标编号，可以定位到检索读取数据过程。

WAIT #139851695602760:nam='db file sequential read'ela= 8file#=1 block#=91705 blocks=1 obj#=164093tim=1427182487878712

WAIT #139851695602760: nam='db file sequential read' ela= 6 file#=1 block#=91706 blocks=1 obj#=164093 tim=1427182487878751

WAIT #139851695602760: nam='db file sequential read' ela= 8 file#=1 block#=91707 blocks=1 obj#=164093 tim=1427182487878989

WAIT #139851695602760: nam='db file sequential read' ela= 9 file#=1 block#=91708 blocks=1 obj#=164093 tim=1427182487879576

WAIT #139851695602760: nam='db file sequential read' ela= 9 file#=1 block#=91709 blocks=1 obj#=164093 tim=1427182487879914

（篇幅原因，有省略……）

WAIT #139851695602760: nam='db file sequential read' ela= 7 file#=1 block#=91821 blocks=1 obj#=164093 tim=1427182487929761

大量单块读动作，每次集中在164093编号的对象上。

SQL> select object_name,owner from dba_objects where object_id=164093;

OBJECT_NAM OWNER

---------- ------------------------------

IDX_T_IDSYS

说明：合并操作是针对原有索引数据进行读取，之后合并索引。

4、结论

相对于rebuild，coalesce操作讨论的比较少，伴随着结构的变化，并没有发生存储结构的调整回收。相对于rebuild，coalesce有几个优势：

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

ü可以在线操作

ü无需重建索引结构，而是尽快地合并索引叶块，这样可避免系统开销过大

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!