*** 9i example where each block has 50% or less used space ...

*** First create a table and populate it with 1000000 rows ...

SQL> CREATE TABLE bowie_stuff (id NUMBER, name VARCHAR2(20));

Table created.

SQL> INSERT INTO bowie_stuff SELECT rownum, 'DAVID BOWIE' FROM dual CONNECT BY LEVEL <= 1000000;

1000000 rows created.

SQL> commit;

Commit complete.


*** Create an index on the ID column


SQL> CREATE INDEX bowie_stuff_i ON bowie_stuff(id) PCTFREE 10;

Index created.



*** Delete 50% of all rows, deleting every alternative row in the table ..

SQL> DELETE bowie_stuff WHERE mod(id,2) = 1;

500000 rows deleted.

SQL> commit;

Commit complete.



*** Check size of index


SQL> ANALYZE INDEX bowie_stuff_i VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT height, blocks, lf_blks, br_blks, btree_space FROM index_stats;

    HEIGHT     BLOCKS    LF_BLKS    BR_BLKS BTREE_SPACE
---------- ---------- ---------- ---------- -----------
         3       2240       2226          5    17848160


*** Let's coalese the index


SQL> ALTER INDEX bowie_stuff_i COALESCE;

Index altered.


*** Check size of index again


SQL> ANALYZE INDEX bowie_stuff_i VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT height, blocks, lf_blks, br_blks, btree_space FROM index_stats;

    HEIGHT     BLOCKS    LF_BLKS    BR_BLKS BTREE_SPACE 
---------- ---------- ---------- ---------- ----------- 
         3       2240       1148          5     9224160    


*** We notice that indeed the index has successfully been coalesced and lf_blks have been reduced from 2226 to 1448 lf_blks



*** However, if we do the same thing in 9i but this time delete less than 50% of the rows ...

SQL> drop table bowie_stuff;

Table dropped.


SQL> CREATE TABLE bowie_stuff (id NUMBER, name VARCHAR2(20));

Table created.

SQL> INSERT INTO bowie_stuff SELECT rownum, 'DAVID BOWIE' FROM dual CONNECT BY LEVEL <= 1000000;

1000000 rows created.

SQL> commit;

Commit complete.


SQL> CREATE INDEX bowie_stuff_i ON bowie_stuff(id) PCTFREE 10;

Index created.


*** Delete just 25% of rows

SQL> DELETE bowie_stuff WHERE mod(id,4) = 1;

250000 rows deleted.

SQL> commit;

Commit complete.


SQL> ANALYZE INDEX bowie_stuff_i VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT height, blocks, lf_blks, br_blks, btree_space FROM index_stats;

    HEIGHT     BLOCKS    LF_BLKS    BR_BLKS BTREE_SPACE   
---------- ---------- ---------- ---------- ----------- 
         3       2240       2226          5    17848160   


*** Note 2226 leaf blocks ...
      

*** Let's coalese the index
                                                   

SQL> ALTER INDEX bowie_stuff_i COALESCE;

Index altered.


SQL> ANALYZE INDEX bowie_stuff_i VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT height, blocks, lf_blks, br_blks, btree_space FROM index_stats;

    HEIGHT     BLOCKS    LF_BLKS    BR_BLKS BTREE_SPACE   
---------- ---------- ---------- ---------- ----------- 
         3       2240       2226          5    17848160    


*** And we note that the index has the same number of leaf blocks ...

*** Coalesce did not reduce leaf blocks because no two consecutive blocks could be merged as the sum of used space for all consecutive blocks always exceeded 100%



*** However, in 10g, this behaviour has changed ...

*** Let's run the example again where we only delete 25% or so of rows ...


SQL> CREATE TABLE bowie_stuff (id NUMBER, name VARCHAR2(20));

Table created.

SQL> INSERT INTO bowie_stuff SELECT rownum, 'DAVID BOWIE' FROM dual CONNECT BY LEVEL <= 1000000;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> CREATE INDEX bowie_stuff_i ON bowie_stuff(id);

Index created.


*** Again, only delete 25% of rows ...

SQL> DELETE bowie_stuff WHERE mod(id,4) = 1;

250000 rows deleted.

SQL> commit;

Commit complete.

SQL> ANALYZE INDEX bowie_stuff_i VALIDATE STRUCTURE;

Index analyzed.


*** Check index space

SQL> SELECT height, blocks, lf_blks, br_blks, btree_space FROM index_stats;

    HEIGHT     BLOCKS    LF_BLKS    BR_BLKS BTREE_SPACE    
---------- ---------- ---------- ---------- -----------  
         3       2304       2226          5    17848160       


SQL> ALTER INDEX bowie_stuff_i COALESCE;

Index altered.


SQL> ANALYZE INDEX bowie_stuff_i VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT height, blocks, lf_blks, br_blks, btree_space FROM index_stats;

    HEIGHT     BLOCKS    LF_BLKS    BR_BLKS BTREE_SPACE   
---------- ---------- ---------- ---------- -----------  
         3       2304       1671          5    13408160    


*** And we notice that indeed, the number of leaf blocks has reduced, even though all blocks had more than 50% used space 

*** Coalesce can be used in far more situations in 10g than it could in 9i ...