Understanding database indexing

Most database administrators are familiar with the need to rebuild the indexes on database columns. As indexes grow larger, they may become inefficient due to fragmentation. Rebuilding these fragmented indexes results in less space being used and improved performance.

About rebuilding indexes

• The algorithm for determining what indexes to rebuild is based on index size and when it was last rebuilt. The largest indexes with the oldest rebuild dates are rebuilt first.

• If using the Standard Edition of Oracle, do not rebuild indexes while the database is open and being used, as this can prevent the IMPAX database from being accessible.

• If using the Enterprise Edition of Oracle, rebuilding indexes can be done online while the database is open and being used. However, the rebuilding of indexes consumes a lot of CPU and I/O resources, so we recommend that you find a time when the Database Server hosting Oracle is not heavily used.

• We also recommend that after rebuilding the indexes, you perform a database backup.

• Since the index rebuild is done online, the existing index is maintained while the new index is being rebuilt. This means that the index tablespace needs a minimum amount of free space that is equal to the size of the index being rebuilt. Also, the rebuild process requires a lot of space in the TEMP tablespace. To determine what size the index tablespace and the TEMP tablespace need to be to successfully rebuild the first index, use the check_rebuild_space utility.

Frequency of rebuilding indexes

• The frequency of rebuilding indexes is configurable, but it also depends on the amount of activity at a site. There is no default - this value must be set manually. If set to a value of 120 days, an index will not be rebuilt more frequently than every 120 days.

• To rebuild indexes on a weekly basis, create the crontab file (AS3000) or a Scheduled Tasks (AS300). It rebuilds only those indexes meeting the frequency criteria in the configuration file.