2 256 77406 172 1227792 7996 171 1 77405
索引树两层结构，包括了256个数据库，叶子节点包括77406个，被删除节点77405个。
开启10046事件跟踪coalesce过程操作。
SQL> select value from v$diag_info where;
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 128
17 rows selected
索引段存储分配没有发生变化，还是17个extent。但是索引逻辑结构已经变化：
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;
HEIGHT BLOCKS LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS DEL_LF_ROWS
---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- -----------