The difference between ALTER INDEX COALESCE and ALTER INDEX rebuild

The difference between ALTER INDEX COALESCE and ALTER INDEX rebuild:
Alter index COALESCE is the leaf block that merges the same branch.
and ALTER index rebuild is to re-establish a indexes, generally need twice times the size of the index space . and needs sorting.

Add:

It says that "coalesce index" consumes less resources than "rebuild index". In my tests,Coalesce index produced more redo size than rebuildindex, and if you coalesce some GB-level indexes, too much redo can seriously affect system performance and quickly fill up the archive directory. It is better to use less on the product library.

The following is a procedure to prove alter index COALESCE:
Xxxxxxxxxxxxxxxxxxxxxxxxxalter Index Coalescexxxxxxxxxxxxxxxxxxxxxx
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 =>, 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;
Ten end;
11/

Pl/sql procedure successfully completed.

Sql> begin
2 sys.dbms_stats.gather_table_stats (' SYSTEM ', ' T2 ', estimate_percent =>, 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 =>, 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;
Ten 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 =>, 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>

Xxxxxxxxxxxxxxxxxxxxxxxxxalter Index Coalescexxxxxxxxxxxxxxxxxxxxxx

Redo Size Test

It says that "coalesce index" consumes less resources than "rebuild index". In my tests, coalesce index produced more redo size than rebuild index, and if you coalesce some GB-level indexes, too much redo can seriously affect system performance and quickly fill up the archive directory. It is better to use less on the product library.



First Test the PHP code on a 7M table:

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 coalesce index generate more redo than rebuildfor a 7M table.

...............................

Then test on a 200M table. 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



............................................

Obviously, coalesce produces too much redo size.

Conclusion: Coalesce index is more resource intensive than rebuild index.