Managing Indexes

Oracle does not limit the number of indexes you can create on a table. However, you should consider the performance benefits of indexes and the needs of your database applications to determine which columns to index.

The following sections explain how to create, alter, and drop indexes using SQL commands. Some simple guidelines to follow when managing indexes are included. See the Oracle7 Server Tuning manual for performance implications of index creation.

Create Indexes After Inserting Table Data

When you create an index on a table that already has data, Oracle must use sort space to create the index. Oracle uses the sort space in memory allocated for the creator of the index (the amount per user is determined by the initialization parameter SORT_AREA_SIZE), but must also swap sort information to and from temporary segments allocated on behalf of the index creation. If the index is extremely large, it might be beneficial to complete the following steps:

1. Create a new temporary tablespace using the CREATE TABLESPACE command.

2. Use the TEMPORARY TABLESPACE option of the ALTER USER command to make this your new temporary tablespace.

3. Create the index using the CREATE INDEX command.

4. Drop this tablespace using the DROP TABLESPACE command. Then use the ALTER USER command to reset your temporary tablespace to your original temporary tablespace.

Index the Correct Tables and Columns Use the following guidelines for determining when to create an index:

• 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 clustered the row data is about the index key. The faster the table scan, the lower the percentage; the more clustered the row data, the higher the percentage.

• Index columns used for joins to improve performance on joins of multiple tables.

• Small tables do not require indexes; if a query is taking too long, the table might have grown from small to large.

• Values are relatively unique in the column.

• There is a wide range of values.

• The column contains many nulls, but queries often select all rows having a value. In this case, the phrase

      WHERE COL_X > -9.99 x 10^125

is preferable to

      WHERE COL_X IS NOT NULL

because the first uses an index on COL_X (assuming that COL_X is a numeric column).

• The column has few distinct values (for example, a column for the sex of employees).

• There are many nulls in the column and you do not search on the non-null values.

The size of a single index entry cannot exceed roughly one-half (minus some overhead) of the available space in the data block. Consult with the database administrator for assistance in determining the space required by an index.

Limit the Number of Indexes per Table A table can have any number of indexes. However, the more indexes, the more overhead is incurred as the table is altered. When rows are inserted or deleted, all indexes on the table must be updated. When a column is updated, all indexes on the column must be updated.

Thus, there is a tradeoff between speed of retrieval for queries on a table and speed of accomplishing updates on the table. For example, if a table is primarily read-only, more indexes might be useful, but if a table is heavily updated, fewer indexes might be preferable.

Order Index Columns for Performance The order in which columns are named in the CREATE INDEX command need not correspond to the order in which they appear in the table. However, the order of columns in the CREATE INDEX statement is significant because query performance can be affected by the order chosen. In general, you should put the column expected to be used most often first in the index.

For example, assume the columns of the VENDOR_PARTS table are as shown in Figure 4 - 1.

Figure 4 - 1. The VENDOR_PARTS Table

Assume that there are five vendors, and each vendor has about 1000 parts.

Suppose that the VENDOR_PARTS table is commonly queried by SQL statements such as the following:

SELECT * FROM vendor_parts
   WHERE part_no = 457 AND vendor_id = 1012;

To increase the performance of such queries, you might create a composite index putting the most selective column first; that is, the column with the most values:

CREATE INDEX ind_vendor_id
   ON vendor_parts (part_no, vendor_id);

Indexes speed retrieval on any query using the leading portion of the index. So in the above example, queries with WHERE clauses using only the PART_NO column also note a performance gain. Because there are only five distinct values, placing a separate index on VENDOR_ID would serve no purpose.

Creating Indexes

Oracle automatically creates an index to enforce a UNIQUE or PRIMARY KEY integrity constraint. In general, it is better to create such constraints to enforce uniqueness and not explicitly use the obsolete CREATE UNIQUE INDEX syntax.

Use the SQL command CREATE INDEX to create an index. The following statement creates an index named EMP_ENAME for the ENAME column of the EMP table:

CREATE INDEX emp_ename ON emp(ename)
   TABLESPACE users
   STORAGE (INITIAL 20K
            NEXT 20k
            PCTINCREASE 75)
   PCTFREE 0;

Notice that several storage settings are explicitly specified for the index.

Privileges Required to Create an Index

Dropping Indexes

• The index is not providing anticipated performance improvements for queries issued against the associated table (the table is very small, or there are many rows in the table but very few index entries, etc.).

• Applications do not contain queries that use the index.

• The index is no longer needed and must be dropped before being rebuilt.

Use the SQL command DROP INDEX to drop an index. For example, to drop the EMP_ENAME index, enter the following statement:

DROP INDEX emp_ename;

If you drop a table, all associated indexes are dropped.

Privileges Required to Drop an Index To drop an index, the index must be contained in your schema or you must have the DROP ANY INDEX system privilege.