alter index coalesce和alter index rebuild的区别
来源:互联网 发布:gis中的空间数据的插值 编辑:程序博客网 时间:2024/05/21 08:32
alter index coalesce和alter index rebuild的区别:
alter index coalesce是合并同一branch的leaf block。
而alter index rebuild是重新建立一个indexes,一般需要2倍的index大小的空间。而且需要排序。
补充:
它讲到”coalesce index”比”rebuild index”消耗更少的资源。在我的测试中,coalesce index比rebuild index产生更多的redo size;如果对一些GB级别的索引进行coalesce,过多的redo会严重影响系统性能,且会很快把归档目录填满。还是在产品库上少用的为好。
以下是证明alter index coalesce的过程:
×××××××× ×××××××××××××××××alter index coalesce××××××××××××××××××××××
SQL>
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for Solaris: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production
SQL> drop table t2;
Table dropped.
SQL> create table t2(col1 number);
Table created.
SQL> create unique index t2_inx on t2(col1);
Index created.
SQL> alter table t2 add constraint t2_pk primary key(col1);
Table altered.
SQL> begin
2 for i in 500001..601000 loop
3 insert into t2 values(i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> begin
2 sys.dbms_stats.gather_table_stats('SYSTEM', 'T2', ESTIMATE_PERCENT => 20, c
ascade=> true);
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> col index_name for a10;
SQL> select index_name, blevel, leaf_blockS, distinct_keys
2 clustering_factor, num_rows
3 from user_indexes
4 where table_name LIKE 'T2%';
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS
---------- ---------- ----------- ----------------- ----------
T2_INX 1 190 101000 101000
SQL> declare
2 cursor mm is select col1 from t2;
3 begin
4 for cur_1 in mm loop
5 if mod(cur_1.col1, 2) = 0 then
6 delete from t2 where col1=cur_1.col1;
7 end if;
8 end loop;
9 commit;
10 end;
11 /
PL/SQL procedure successfully completed.
SQL> begin
2 sys.dbms_stats.gather_table_stats('SYSTEM', 'T2', ESTIMATE_PERCENT => 20, c
ascade=> true);
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select index_name, blevel, leaf_blockS, distinct_keys
2 clustering_factor, num_rows
3 from user_indexes
4 where table_name LIKE 'T2%';
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS
---------- ---------- ----------- ----------------- ----------
T2_INX 1 190 50500 50500
SQL> alter index t2_inx coalesce;
Index altered.
SQL> begin
2 sys.dbms_stats.gather_table_stats('SYSTEM', 'T2', ESTIMATE_PERCENT => 20, c
ascade=> true);
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select index_name, blevel, leaf_blockS, distinct_keys
2 clustering_factor, num_rows
3 from user_indexes
4 where table_name LIKE 'T2%';
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS
---------- ---------- ----------- ----------------- ----------
T2_INX 1 189 50500 50500
SQL> declare
2 cursor mm is select col1 from t2;
3 begin
4 for cur_1 in mm loop
5 if mod(cur_1.col1, 3) = 0 then
6 delete from t2 where col1=cur_1.col1;
7 end if;
8 end loop;
9 commit;
10 end;
11 /
PL/SQL procedure successfully completed.
SQL> alter index t2_inx coalesce;
Index altered.
SQL> begin
2 sys.dbms_stats.gather_table_stats('SYSTEM', 'T2', ESTIMATE_PERCENT => 20, c
ascade=> true);
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select index_name, blevel, leaf_blockS, distinct_keys
2 clustering_factor, num_rows
3 from user_indexes
4 where table_name LIKE 'T2%';
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS
---------- ---------- ----------- ----------------- ----------
T2_INX 1 95 33666 33666
SQL>
×××××××× ×××××××××××××××××alter index coalesce××××××××××××××××××××××
redo size 检验
它讲到”coalesce index”比”rebuild index”消耗更少的资源。在我的测试中,coalesce index比rebuild index产生更多的redo size;如果对一些GB级别的索引进行coalesce,过多的redo会严重影响系统性能,且会很快把归档目录填满。还是在产品库上少用的为好。
先在一个7M的表上进行测试
PHP code:
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 rebuildfora 7M table.
...............................
再在一个200M的表上进行测试。
PHP code:
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.
- alter index coalesce和alter index rebuild的区别
- alter index coalesce和alter index rebuild的区别
- alter index coalesce和alter index rebuild的区别
- alter index rebuild和alter index rebuild online的区别
- alter index rebuild与alter index rebuild online的区别
- alter index rebuild与alter index rebuild online的区别
- alter index rebuild与alter index rebuild online的区别
- oracle alter index rebuild online和alter index rebuild的区别
- alter index rebuild & alter index rebuild online
- alter index rebuild & alter index rebuild online
- alter index rebuild online引发的血案
- alter index rebuild online引发的血案
- alter index rebuild online引发的血案
- OCP-043 alter index..coalesce
- 重建索引:ALTER INDEX..REBUILD ONLINE vs ALTER INDEX..REBUILD
- Oracle alter index rebuild 说明
- alter index rebuild 效率分析
- alter index rebuild 与 rebuild online
- 浅谈HTTP中Get与Post的区别
- mdb 不能用 where
- VC|MFC学习笔记十一:GetWindowRect()和GetClientRect();OnPaint()和OnDraw()等
- scanf 与 gets()的区别
- C#数据结构和算法学习系列一----打造自己的Collection类
- alter index coalesce和alter index rebuild的区别
- android模拟器无法联网
- 如何在单片机上使用printf函数(printf)(avr)(stm)(lpc)(单片机)
- ZOJ1333 POJ1545 Galactic Import,Floyd算法
- Step By Step Instructions on Installing Oracle 11g Real Application Cluster (RAC) (11.1.0.6) 32-bit
- joj1349
- 111
- Android SDK和最新ADT下载地址
- 在win7上安装VS2010时VC9.0Runtime组件总是安装失败。