Changing Storage Parameters for Indexes

Parameters that can be modified deal with block utilization. These parameters can be changed with the ALTER INDEX command and include the INITRANS, MAXTRANS, and MAXEXTENTS parameters. These parameters are often altered to allow a higher level of concurrency on any given index block.

Furthermore, much the same as you can manually add an extent to a table, you can manually add extents to indexes.

The following example alters the storage parameters associated with the address_last_name_idx index:

[View full width]
 ALTER INDEX addresses_last_name_idx ALLOCATE EXTENT (SIZE 250K DATAFILE 'd:\mydatabases \mydb1\addresses_idx.dbf') 

You can also deallocate space above the high water mark in an index. This means that if you have allocated space to an index, but that space has never been used (located above the highest point at which data had been stored in the index), you can deallocate that space. The following example shows the syntax that would be used for this:

 ALTER INDEX addresses_last_name_idx DEALLOCATE UNUSED; 

Manual allocation and deallocation of space for an index follows the same rules as it does for tables.

Index space is automatically deallocated when the table on which the index is built is truncated; table truncation results in index truncation.

Now that we have created indexes and changed their storage parameters, we need to discuss how to reorganize them should the need arise.