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 ProductionPL/SQL Release 11.2.0.3.0 - ProductionCORE 11.2.0.3.0 ProductionTNS for Linux: Version 11.2.0.3.0 - ProductionNLSRTL Version 11.2.0.3.0 - Production创建数据表T，构建索引。SQL> create table t as select * from dba_objects ;Table createdSQL> 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> commit;Commit complete SQL> select count（*） from t; COUNT（*）---------- 12、coalesce操作Delete操作既不会回收数据段，也不会回收索引段。当前一行数据表T对应的段信息如下：SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where owner="SYS" and segment_name="T"; EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS---------- ---------- ---------- ---------- ---------- 0 1 86984 65536 8 1 1 86992 65536 8 2 1 87000 65536 8 3 1 87008 65536 8 4 1 87016 65536 8 5 1 87024 65536 8 6 1 87032 65536 8 7 1 88960 65536 8 8 1 88968 65536 8 9 1 88976 65536 8 10 1 88984 65536 8 11 1 88992 65536 8 12 1 89000 65536 8 13 1 89008 65536 8 14 1 90360 65536 8 15 1 91008 65536 8 16 1 89088 1048576 128 17 1 89216 1048576 128 18 1 89344 1048576 128 19 1 89472 1048576 128 EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS---------- ---------- ---------- ---------- ---------- 20 1 89600 1048576 128 21 1 89728 1048576 128 22 1 89856 1048576 128 23 1 89984 1048576 128 24 rows selected索引段如下：SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where owner="SYS" and segment_name="IDX_T_ID"; EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS---------- ---------- ---------- ---------- ---------- 0 1 91704 65536 8 1 1 91712 65536 8 2 1 91720 65536 8 3 1 91728 65536 8 4 1 91736 65536 8 5 1 91744 65536 8 6 1 91752 65536 8 7 1 91760 65536 8 8 1 91768 65536 8 9 1 92544 65536 8 10 1 92552 65536 8 11 1 92560 65536 8 12 1 92568 65536 8 13 1 92576 65536 8 14 1 92584 65536 8 15 1 92592 65536 8 16 1 91776 1048576 12817 rows selected多extent结构，表示结构没有回收。下面使用analyze语句分析一下索引的情况：SQL> analyze index idx_t_id validate structure;Index analyzedSQL> select height, blocks, lf_rows, lf_blks, lf_rows_len, lf_blk_len, br_rows, br_blks, del_lf_rows from index_stats; HEIGHT BLOCKS LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS DEL_LF_ROWS---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- 2 256 77406 172 1227792 7996 171 1 77405索引树两层结构，包括了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";Session altered.操作之后检查一下结构效果。SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where owner="SYS" and segment_name="IDX_T_ID"; EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS---------- ---------- ---------- ---------- ---------- 0 1 91704 65536 8 1 1 91712 65536 8 2 1 91720 65536 8 3 1 91728 65536 8 4 1 91736 65536 8 5 1 91744 65536 8 6 1 91752 65536 8 7 1 91760 65536 8 8 1 91768 65536 8 9 1 92544 65536 8 10 1 92552 65536 8 11 1 92560 65536 8 12 1 92568 65536 8 13 1 92576 65536 8 14 1 92584 65536 8 15 1 92592 65536 8 16 1 91776 1048576 12817 rows selected索引段存储分配没有发生变化，还是17个extent。但是索引逻辑结构已经变化：SQL> analyze index idx_t_id validate structure;Index analyzedSQL> select height, blocks, lf_rows, lf_blks, lf_rows_len, lf_blk_len, br_rows, br_blks, del_lf_rows from index_stats; HEIGHT BLOCKS LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS DEL_LF_ROWS---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- 2 256 1 1 16 7996 0 1 0索引高度和分配块数量没有变化，但是叶子节点进行了重组。被删除数据节点被整理合并。3、10046文件分析从10046事件文件分析的情况看，如下：=====================PARSING IN CURSOR #139851695602760 len=29 dep=0 uid=0 oct=11 lid=0 tim=1427182487640740 hv=4054144165 ad="aa2f2710" sqlid="a88sghvsuap55"alter index idx_t_id coalesceEND OF STMTPARSE #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= 8 file#=1 block#=91705 blocks=1 obj#=164093 tim=1427182487878712WAIT #139851695602760: nam="db file sequential read" ela= 6 file#=1 block#=91706 blocks=1 obj#=164093 tim=1427182487878751WAIT #139851695602760: nam="db file sequential read" ela= 8 file#=1 block#=91707 blocks=1 obj#=164093 tim=1427182487878989WAIT #139851695602760: nam="db file sequential read" ela= 9 file#=1 block#=91708 blocks=1 obj#=164093 tim=1427182487879576WAIT #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_ID SYS说明：合并操作是针对原有索引数据进行读取，之后合并索引。4、结论相对于rebuild，coalesce操作讨论的比较少，伴随着结构的变化，并没有发生存储结构的调整回收。相对于rebuild，coalesce有几个优势：

不需要占用近磁盘存储空间 2 倍的空间
可以在线操作
无需重建索引结构，而是尽快地合并索引叶块，这样可避免系统开销过大

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址