玉面飞龙的BLOG

在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.

结论：coalesce index is more resource intensive than rebuild index.

See more in http://www.itpub.net/showthread.php?threadid=719131&pagenumber=