5 IndexesHow do I…
5.1 Determine all of the indexes on a particular table?
5.2 Determine all of the indexes owned by a particular user?
5.3 Determine an index’s initial size parameters?
5.4 Detect unbalanced indexes?
5.5 Rebuild unbalanced indexes?
5.6 Enforce uniqueness via an index?
Indexes are important objects used for data integrity and improving database performance. An index works as a “look-up” guide to directly locate a record, similar to how someone would look up a subject in the index of a book. Strategically placed indexes can improve query performance dramatically, decreasing result times from hours to seconds. Indexes are also useful to enforce business rules within the database, especially uniqueness within a column or group of columns. An index is separate from the data it is based upon, and so the index may be dropped or modified without affecting the underlying data. Once created, Oracle updates all relevant indexes during data manipulation. There are several types of indexes, such as bitmap indexes, regular indexes, partitioned indexes, and index-organized tables, each with its own unique advantages and disadvantages. This chapter covers the creation and manipulation of indexes within Oracle, and describes the pros and cons of each type of index.
5.1 Determine All of the Indexes on a Particular Table
The most commonly used method to improve data queries is the creation of indexes. Many times, it is important to know how a table has been indexed. This How-To explains the steps used to see which indexes exist on a table.
5.2 Determine All of the Indexes Owned by a Particular User
When a database contains many applications and/or user accounts, there may be a large number of indexes. It is important to keep track of owners, indexes, and tables. This How-To explains the steps used to see all indexes of one user account.
5.3 Determine an Index’s Initial Size Parameters
Before creating an index, it is important to determine how much storage space will be needed, as well as to know how quickly and how large the table that the index is based upon will eventually grow. This information is important to make the index fit within a single extent, which is important for performance reasons. Also, if not sized properly, the index will either waste disk space or need more space. This How-To takes you through the steps of determining an index’s initial size parameters.
5.4 Detect Unbalanced Indexes
When a table is heavily inserted to, updated, or deleted from, its indexes may become fragmented or unbalanced. An unbalanced index has many records clumped close together on the index tree due to their similar indexed values. When an index is unbalanced, parts of an index are accessed more frequently than others, from which disk contention may occur, creating a bottleneck in performance. It is important to periodically examine all indexes to determine if they have become unbalanced. This How-To takes you through the steps of detecting unbalanced indexes.
5.5 Rebuild Unbalanced Indexes
How-To 5.4 shows how to detect unbalanced indexes. For performance reasons, these indexes should be rebuilt to improve the disk throughput. This How-To takes you through the steps of rebuilding any unbalanced indexes, as well as providing suggestions for fast index rebuilding.
5.6 Enforce Uniqueness via an Index
Unique indexes may be created that ensure that the values of the indexed columns are unique for each record. This fulfills business rules and is created from a primary or unique constraint. A unique index may also be created manually with no corresponding constraint. Any attempt to add or update a record that duplicates the values of any other record will fail with an error. This How-To describes the steps used to create a unique index.