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
CORE 11.2.0.3.0 Production
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> commit;
Commit complete
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_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 128
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;
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 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
---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- -----------
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 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= 8 file#=1 block#=91705 blocks=1 obj#=164093 tim=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_ID SYS
說明：合併操作是針對原有索引資料進行讀取，之後合併索引。
4、結論
相對於rebuild，coalesce操作討論的比較少，伴隨著結構的變化，並沒有發生儲存結構的調整回收。相對於rebuild，coalesce有幾個優勢：
ü 不需要佔用近磁碟儲存空間 2 倍的空間
ü 可以線上操作
ü 無需重建索引結構，而是儘快地合併索引葉塊，這樣可避免系統開銷過大
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17203031/viewspace-1478492/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 合併分割槽（coalesce partition)
- mysql 索引合併MySql索引
- RxJava 合併操作RxJava
- index merge合併索引Index索引
- oracle索引操作Oracle索引
- 12.索引合併-indexMerge索引Index
- Vim常用操作-合併行。
- Oracle 合併行/多行合併成一行Oracle
- MySQL 配置索引頁的合併閾值MySql索引
- Oracle 合併 merger intoOracle
- TortoiseSVN 合併操作簡明教程
- mysql索引合併:一條sql可以使用多個索引MySql索引
- 如何合併視訊？是否適合新手操作？
- ORACLE 組合索引 使用分析Oracle索引
- Oracle表連線操作——Merge Sort Join（合併排序連線）Oracle排序
- oracle複合索引介紹(多欄位索引)Oracle索引
- MySQL 優化之 index_merge （索引合併）MySql優化Index索引
- Oracle中coalesce函式的簡單理解Oracle函式
- Java Stream 流如何進行合併操作Java
- 使用TortoiseGit操作分支的建立與合併Git
- pandas -- DataFrame的級聯以及合併操作
- 淺談 OI 中各種合併操作
- PostgreSQL11preview-索引優化。filter智慧消除、分割槽索引智慧合併SQLView索引優化Filter
- Hbase-原理-region合併和hfile的合併（大合併、小合併）
- oracle 合併多個sys_refcursorOracle
- oracle聯合索引在CBO下的分析Oracle索引
- Oracle中組合索引的使用詳解Oracle索引
- 【oracle 效能優化】組合索引查詢。Oracle優化索引
- MySQL中為什麼要使用索引合併(Index Merge)？MySql索引Index
- 索引回表操作，ORACLE所作的優化索引Oracle優化
- python3.9中字典合併如何操作？Python
- 教你批次合併影片、文案和音訊的操作音訊
- 知識點：Python中的列表合併操作Python
- 【Oracle】-【建立索引】-建立索引的操作原理與一些體會Oracle索引
- Oracle複合索引的建立和注意事項Oracle索引
- oracle組合索引什麼情況下生效?Oracle索引
- PHP 圖片的合併，微信小程式碼合併，文字合併PHP微信小程式
- oracle全文索引之commit與DML操作Oracle索引MIT