Managing Indexes Part #1

Indexes are optional structures associated with tables and clusters that allow SQL statements to execute more quickly against a table. An Oracle Database index provides a faster access path to table data. You can use indexes without rewriting any queries. Your results are the same, but the execution time increase.

Oracle Database provides several indexing schemes that provide complementary performance functionality. These are:

1. B-tree indexes: the default and the most common
2. B-tree cluster indexes: defined specifically for cluster
3. Hash cluster indexes: defined specifically for a hash cluster
4. Global and local indexes: relate to partitioned tables and indexes
5. Reverse key indexes: most useful for Oracle Real Application Clusters applications
6. Bitmap indexes: compact; work best for columns with a small set of values
7. Function-based indexes: contain the precomputed value of a function/expression
8. Domain indexes: specific to an application or cartridge.
   

Indexes are logically and physically independent of the data in the associated table. Being independent structures, they require storage space. You can create or drop an index without affecting the base tables, database, applications, or other indexes. The database automatically maintains indexes when you insert, update, and delete rows of the associated table. If you drop an index, all applications continue to work. However, access to previously indexed data might be slower.

Guidelines for Managing Indexes

Create Indexes After Inserting Table Data

Data is often inserted or loaded into a table using either the SQL*Loader or an import utility. It is more efficient to create an index for a table after inserting or loading the data. If you create one or more indexes before loading data, the database then must update every index as each row is inserted.

Creating an index on a table that already has data requires sort space. Some sort space comes from memory allocated for the index creator. The amount for each user is determined by the initialization parameter SORT_AREA_SIZE. The database also swaps sort information to and from temporary segments that are only allocated during the index creation in the users temporary tablespace.


Index the Correct Tables and Columns

Use the following guidelines for determining when to create an index:

1. Create an index if you frequently want to retrieve less than 15% of the rows in a large table. The percentage varies greatly according to the relative speed of a table scan and how the distribution of the row data in relation to the index key. The faster the table scan, the lower the percentage; the more clustered the row data, the higher the percentage.
2. To improve performance on joins of multiple tables, index columns used for joins.
3. Primary and unique keys automatically have indexes, but you might want to create an index on a foreign key.
4. Small tables do not require indexes. If a query is taking too long, then the table might have grown from small to large.
   

Some columns are strong candidates for indexing. Columns with one or more of the following characteristics are candidates for indexing:

1. Values are relatively unique in the column.
2. There is a wide range of values (good for regular indexes).
3. There is a small range of values (good for bitmap indexes).
4. The column contains many nulls, but queries often select all rows having a value. In this case, use the following phrase: WHERE COL_X > -9.99 * power(10,125)Using the preceding phrase is preferable to: WHERE COL_X IS NOT NULL. This is because the first uses an index on COL_X (assuming that COL_X is a numeric column).
   

Columns with the following characteristics are less suitable for indexing:

1. There are many nulls in the column and you do not search on the not null values.
2. LONG and LONG RAW columns cannot be indexed.
   

The size of a single index entry cannot exceed roughly one-half (minus some overhead) of the available space in the data block.

Discussion Continued in Managing Indexes Part #2