Oracle9i DBA -- Database: Fundamentals I (13)

Managing Indexes
##CONTINUE##

• When you encounter index fragmentation, you can rebuild or coalesce the index. coalesce on an index is a block rebuild that is done online.
• DBA_INDEXES : index name , index type, table owner, index owner.
• Index rebuilds have the following characteristics

  O A new index is built using an existing index as the data source

  O Sorts are not needed when an index is built using an existing index, resulting in better performance.

  O The old index is deleted after the new index is build. During the rebuild, sufficient space is needed to accommodate both the old and the new index in their respective tablespaces.

  O The resulting index does not contain any deleted entries. This index uses space more efficiently

  O Queries can continue to use the existing index while the new index is being built.

• DBA_IND_EXPRESSIONS : Provides information on columns or expressions used to create index
• V$OBJECT_USAGE : Provide information on usage of index
• Cluster maintains a “Cluster Index” that is used to locate rows and other information
• B-tree indexes work well in single indexes rather than multiple indexes. B-tree support row-level both unique and non-unique indexes. B-tree index are suitable for retrival of a small number of rows.
• The individual partitions are stored in separate segments whether it is a table partition or index partition. Partitions are beneficial in large objects that are read or written to simultaneously.
•