Myth:Index Coalesce is less resource intensive

Myth:Index Coalesce is less resource intensive[@more@]在Google中搜尋”Index Internal”我們可以看到下面這個很好的文章。.

Microsoft PowerPoint - Oracle Index Internals.ppt
檔案格式: PDF/Adobe Acrobat - HTML 版
Classic Oracle Index Myths. Oracle B-tree indexes can become “unbalanced” over time. and need to be rebuilt … Index Coalesce.. More efficient, less resource intensive, less. locking issues than rebuild option …

它講到”coalesce index”比”rebuild index”消耗更少的資源。在我的測試中，coalesce index比rebuild index產生更多的redo size；如果對一些GB級別的索引進行coalesce，過多的redo會嚴重影響系統效能，且會很快把歸檔目錄填滿。還是在產品庫上少用的為好。



先在一個7M的表上進行測試


create table binzhang(id number not null,creation date not null,last_modified date not null);
insert into binzhang select binzhang_seq.nextval id,created,created from dba_objects
insert into binzhang select binzhang_seq.nextval id,created,created from dba_objects;
Commit
SQL> select bytes from user_segments where segment_name=’BINZHANG’;
BYTES
———-
7340032
create index binzhangidx1 on binzhang(creation) ;
create index binzhangidx2 on binzhang(last_modified) ;
update binzhang set creation=creation+124,last_modified=last_modified+124 where mod(id,12) in (1,3,5,7,9,11);
commit;
analyze index binzhangidx1 validate structure;
SQL> select name,value from v$statname s,v$mystat v where s.STATISTIC#=v.STATISTIC# and name=’redo size’;
NAME VALUE
—————————–
redo size 1288055828

SQL> alter index binzhangidx1 coalesce;
Index altered.

SQL> select name,value from v$statname s,v$mystat v where s.STATISTIC#=v.STATISTIC# and name=’redo size’;
NAME VALUE
———————— ———-
redo size 1294815880

SQL> alter index binzhangidx2 rebuild tablespace cr_data;
Index altered.

SQL> select name,value from v$statname s,v$mystat v where s.STATISTIC#=v.STATISTIC# and name=’redo size’;
NAME VALUE
———————— ———-
redo size 1299289400

Redo size used by coalesce 1294815880-1288055828=6760052
Redo size used by rebuild 1299289400-1294815880=4473520

OK. We can see that coalesce index generate more redo than rebuild for a 7M table.
...............................
再在一個200M的表上進行測試。


SQL> select bytes from user_segments where segment_name=’BINZHANG’;
BYTES
———-
201326592
SQL> update binzhang set creation=creation+124,last_modified=last_modified+124 where mod(id,12) in (1,3,5,7,9,11);
3083428 rows updated.
SQL> commit;
SQL> select name,value from v$statname s,v$mystat v where s.STATISTIC#=v.STATISTIC# and name=’redo size’;
NAME VALUE
——————————- ———-
redo size 568

SQL> alter index binzhangidx2 rebuild;
Index altered.

SQL> select name,value from v$statname s,v$mystat v where s.STATISTIC#=v.STATISTIC# and name=’redo size’;
NAME VALUE
————————— ———-
redo size 134919152

SQL> alter index binzhangidx1 coalesce;
Index altered.

SQL> select name,value from v$statname s,v$mystat v where s.STATISTIC#=v.STATISTIC# and name=’redo size’;

NAME VALUE
———————— ———-
redo size 496401172

............................................
非常明顯，coalesce產生過多的redo size.

結論：coalesce index is more resource intensive than rebuild index.