Managing Indexes

The following sections describe how to create and manage indexes:

About Indexes
Viewing Indexes
Creating Indexes
Dropping Indexes

About Indexes

Indexes are optional structures associated with tables. You can create them to improve query performance. Just as the index in this book helps you to quickly locate specific information, an Oracle index provides a quick access path to table data.

You can create indexes on one or more columns of a table. After an index is created, it is automatically maintained and used by Oracle. Changes to a table's data or structure, such as adding new rows, updating rows, or deleting rows, are automatically incorporated into all relevant indexes with complete transparency to the user.

Some indexes are created implicitly through constraints that are placed on a table. For example, a column with the constraint that its values be unique causes Oracle to create a unique key index.

When you click the Indexes link in the Database Objects section of the Administration page, an Indexes page appears. This page is similar in content and functionality to the Tables page shown in Figure 8-3.

Indexes and Performance

Before you add additional indexes, you should examine the performance of your database. You can then compare performance after the new indexes are added.

Indexes are generally of value to queries and to SQL statements that need to operate on a single, existing row or a small number of existing rows. However, too many indexes can increase the processing overhead for statements that add, modify, or delete rows.

To determine if your application can improve performance with more indexes, you can run the SQL Access Advisor in Enterprise Manager. See Using the SQL Access Advisor in Chapter 10, "Monitoring and Tuning the Database".

Index Types

Indexes can be categorized in a number of ways. The primary options are listed in the following sections.

Standard (B-tree) and Bitmap

A standard, B-tree index contains an entry for each value in the index key along with an address to the row where the value is stored. A B-tree index is the default and most common type of index in an Oracle database.

A bitmap index uses strings of bits to encapsulate values and potential row addresses. It is more compact than a B-tree and can perform some types of retrieval more efficiently. For general use, however, a bitmap index requires more overhead during row operations on the table and should be used primarily for data warehouse environments as described in the Oracle Database Data Warehousing Guide.

Ascending and Descending

The default search through an index is from lowest to highest value, where character data is sorted by ASCII values, numeric data from smallest to largest number, and date from the earliest to the latest value. This default behavior is performed in indexes created as ascending indexes. You can cause index searches to reverse the search order by creating the related index with the descending option.

Column and Functional

Typically, an index entry is based on the value or values found in the table's column or columns. This is a column index. Alternatively, you can create a function-based index in which the indexed value is derived from the table data. For example, to find character data that can be in mixed case, you could use a function-based index to look for the values as if they were all in uppercase characters.

Single Column and Concatenated

You can create an index on just one column, which is called a single column index, or on multiple columns, which is called a concatenated index. Concatenated indexes are useful when all of the columns are likely to be included in the WHERE clause of frequently executed SQL statements.

For concatenated indexes, you should define the columns used in the index carefully so that the column with the fewest duplicate values is named first, the column with next fewest duplicate values is second, and so on. Columns with many duplicate values or many rows with NULL values should not be included or should be the last-named columns in the index definition.

Non-partitioned and Partitioned

As with tables, you can create your indexes with or without partitions. In most situations, it is useful to partition an index when the associated table is also partitioned and the index uses the same partitioning scheme as the table, known as a local index. You need not partition an index like the underlying table. You may even create a non-partitioned, or global, index on a partitioned table.

Viewing Indexes

To find out which indexes exist in your database, use the search options on the Indexes page.

To view indexes:

In the Administration page, click Indexes.

The Indexes page appears.
In the Search By list, select Index Name to search for indexes by name.

Alternatively, you can select Table Name to search for the name of the table on which the index is built.
In the Schema box, enter the name of a schema. For example, enter SYS. Alternatively, click the flashlight icon adjacent to the Schema box to search for a schema.
Leave Object Name blank to search for all indexes in a schema. Alternatively, enter an object name or click the flashlight icon to search for one.
Click Go.

The indexes in the specified schema are displayed.
In the results list, select a table and then click View or the link in the Index column to view a table definition.

The View Index: index_name page appears. In the General area, you can find basic information about the index, such as its status and the column or columns on which it is built. Other areas include information about the space consumed by the index and the options used in its definition.

Creating Indexes

You can create indexes with Enterprise Manager. To create an index, you specify one or more columns to be indexed and the type of index you want to create.

The following example creates a standard B-tree index on the SUPPLIER_ID column in the SH.PRODUCTS table, which is part of the sample schema.

To create an index:

In the Database Objects section of the Administration page, click Tables.

The Tables page appears.
Enter sh in the Schema box and click Go.

The result list shows the tables in the sh schema.
Select the PRODUCTS table and select Create Index from the Actions list. Click Go.

The Create Index page appears.
Enter the following information:
- In the Name box, enter PRODUCTS_SUPPLIER_IDX.
- For Tablespace, accept the default.
- For Index Type, select Standard - B-tree.
- In the Table Columns list, select the SUPPLIER_ID column by entering 1 in the Order column.
- For Sorting Order, accept the default selection of ASC.
Click OK to create the index.

The Indexes page appears with a confirmation message. The new index is listed under Results.

Dropping Indexes

If you no longer need an index, you can drop it by using Enterprise Manager.

In this example, you drop the PRODUCTS_SUPPLIER_IDX index that you created previously.

To drop an index:

In the Administration page, click Indexes.

The Indexes page appears.
In the Search By list, select Index Name to search for indexes by name. In Schema, enter sh. In Object Name, enter PRODUCTS_SUPPLIER_IDX.
Click Go.

The results page should display the PRODUCTS_SUPPLIER_IDX index.
Select the PRODUCTS_SUPPLIER_IDX index and click Delete.

A Confirmation page appears.
Click Yes to delete the index.

The Edit table page displays a confirmation message if the index is successfully deleted.

Note:

You cannot drop an index that is currently used to enforce a constraint. You must disable or drop the constraint and then, if the index is not dropped as a result of that action, drop the index.