Friday, March 23, 2012

Alter Index ... Rebuild Online

Some people suggest that you should rebuild an index if its height is greater than 3. I am not totally convinced by this but I was asked to do so recently and this is what happened. I checked the index first as follows:
 
SQL> analyze index auabopen_1
  2  validate structure
  3  /
 
Index analyzed.
 
SQL> select name, height from index_stats
  2  /
 
NAME                               HEIGHT
------------------------------ ----------
AUABOPEN_1                              4
 
SQL>
 
I checked the size of the index, saw what tablespace it is in and made sure there was enough space there to do the rebuild:
 
SQL> l
  1  select bytes from dba_segments
  2* where segment_name = 'AUABOPEN_1'
SQL> /
 
     BYTES
----------
2306867200
 
SQL> select tablespace_name
  2  from dba_indexes
  3  where index_name = 'AUABOPEN_1'
  4  /
 
TABLESPACE_NAME
------------------------------
UABOPEN_INDEX_1
 
SQL> select max(bytes) from dba_free_space
  2  where tablespace_name = 'UABOPEN_INDEX_1'
  3  /
 
MAX(BYTES)
----------
7340032000
 
SQL>
 
The index is on an online system so I rebuilt it as follows:
 
SQL> alter index auabopen_1
  2  rebuild online
  3  tablespace uabopen_index_1
  4  /
 
Index altered.
 
SQL>
 
Then I rechecked its height but it had not changed. You can't win them all!
 
SQL> analyze index auabopen_1
 2  validate structure
  3  /
 
Index analyzed.
 
SQL> select name, height from index_stats
  2  /
 
NAME                               HEIGHT
------------------------------ ----------
AUABOPEN_1                              4
 
SQL>

No comments: