Oracle9i Database Performance Guide and Reference
Release 1 (9.0.1)

Part Number A87503-02
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index


Go to previous page Go to next page

Understanding Indexes and Clusters

This chapter provides an overview of data access methods that can enhance performance and of situations to avoid.

This chapter contains the following sections:

Understanding Indexes

This section describes the following:

Tuning the Logical Structure

Although cost-based optimization helps avoid the use of nonselective indexes within query execution, the SQL engine must continue to maintain all indexes defined against a table regardless of whether they are used. Index maintenance can present a significant CPU and I/O resource demand in any write-intensive application. In other words, do not build indexes unless necessary.

To maintain optimal performance, drop indexes that an application is not using. You can find indexes that are not being used by using the ALTER INDEX MONITORING USAGE functionality over a period of time that is representative of your workload. This monitoring feature records whether or not an index has been used. If you find that an index has not been used, then drop it. Be careful to select a representative workload to monitor.

See Also:

Oracle9i SQL Reference 

Indexes within an application sometimes have uses that are not immediately apparent from a survey of statement execution plans. An example of this is a foreign key index on a parent table which prevents share locks being taken out on a child table.

If you are deciding whether to create new indexes to tune statements, then you can also use the EXPLAIN PLAN statement to determine whether the optimizer will choose to use these indexes when the application is run. If you create new indexes to tune a statement that is currently parsed, then Oracle invalidates the statement. When the statement is next executed, the optimizer automatically chooses a new execution plan that could potentially use the new index. If you create new indexes on a remote database to tune a distributed statement, then the optimizer considers these indexes when the statement is next parsed.

Also keep in mind that the way you tune one statement can affect the optimizer's choice of execution plans for others. For example, if you create an index to be used by one statement, then the optimizer can choose to use that index for other statements in the application as well. For this reason, reexamine the application's performance and rerun the SQL trace facility after you have tuned those statements that you initially identified for tuning.


You can use the Oracle Index Tuning Wizard to detect tables with inefficient indexes. The Oracle Index Tuning wizard is an Oracle Enterprise Manager integrated application available with the Oracle Tuning Pack. Similar functionality is available from the Virtual Index Advisor (a feature of SQL Analyze) and Oracle Expert. For more information, see the Database Tuning with the Oracle Tuning Pack manual. 

Choosing Columns and Expressions to Index

A key is a column or expression on which you can build an index. Follow these guidelines for choosing index keys to index:

Choosing Composite Indexes

A composite index contains more than one key column. Composite indexes can provide additional advantages over single-column indexes:

Improved selectivity

Sometimes two or more columns or expressions, each with poor selectivity, can be combined to form a composite index with higher selectivity.

Reduced I/O

If all columns selected by a query are in a composite index, then Oracle can return these values from the index without accessing the table.

A SQL statement can use an access path involving a composite index if the statement contains constructs that use a leading portion of the index.


This is no longer the case with index skip scans. See "Index Skip Scans"

A leading portion of an index is a set of one or more columns that were specified first and consecutively in the list of columns in the CREATE INDEX statement that created the index. Consider this CREATE INDEX statement:

CREATE INDEX comp_ind 
ON tab1(x, y, z);

These combinations of columns are leading portions of the index: x, xy, and xyz. These combinations of columns are not leading portions of the index: yz, y, and z.

Follow these guidelines for choosing keys for composite indexes:

Of course, consider the guidelines associated with the general performance advantages and trade-offs of indexes described in the previous sections. Follow these guidelines for ordering keys in composite indexes:

Writing Statements that Use Indexes

Even after you create an index, the optimizer cannot use an access path that uses the index simply because the index exists. The optimizer can choose such an access path for a SQL statement only if it contains a construct that makes the access path available. To allow the CBO the option of using an index access path, ensure that the statement contains a construct that makes such an access path available.

Writing Statements that Avoid Using Indexes

In some cases, you might want to prevent a SQL statement from using an access path that uses an existing index. You might want to do this if you know that the index is not very selective and that a full table scan would be more efficient. If the statement contains a construct that makes such an index access path available, then you can force the optimizer to use a full table scan through one of these methods:

Parallel execution uses indexes effectively. It does not perform parallel index range scans, but it does perform parallel index lookups for parallel nested loop join execution. If an index is very selective (there are few rows per index entry), then it might be better to use sequential index lookup than parallel table scan.

Re-creating Indexes

You might want to re-create an index to compact it and minimize fragmented space, or to change the index's storage characteristics. When creating a new index that is a subset of an existing index or when rebuilding an existing index with new storage characteristics, Oracle might use the existing index instead of the base table to improve the performance of the index build.


Remember to include the CREATE STATISTICS statement on the CREATE or REBUILD to avoid calling DBMS_STATS after the index creation or rebuild. You can use the Oracle Enterprise Manager Reorg Wizard to identify indexes that require rebuilding. The Reorg Wizard can also be used to rebuild the indexes. 

However, there are cases where it can be beneficial to use the base table instead of the existing index. Consider an index on a table on which a lot of DML has been performed. Because of the DML, the size of the index can increase to the point where each block is only 50% full, or even less. If the index refers to most of the columns in the table, then the index could actually be larger than the table. In this case, it is faster to use the base table rather than the index to re-create the index.

Use the ALTER INDEX ... REBUILD statement to reorganize or compact an existing index or to change its storage characteristics. The REBUILD statement uses the existing index as the basis for the new one. All index storage statements are supported, such as STORAGE (for extent allocation), TABLESPACE (to move the index to a new tablespace), and INITRANS (to change the initial number of entries).

Usually, ALTER INDEX ... REBUILD is faster than dropping and re-creating an index, because this statement uses the fast full scan feature. It reads all the index blocks using multiblock I/O, then discards the branch blocks. A further advantage of this approach is that the old index is still available for queries while the rebuild is in progress.

See Also:

Oracle9i SQL Reference for more information about the CREATE INDEX and ALTER INDEX statements, as well as restrictions on rebuilding indexes 

Compacting Indexes

You can coalesce leaf blocks of an index using the ALTER INDEX statement with the COALESCE option. This allows you to combine leaf levels of an index to free blocks for reuse. You can also rebuild the index online.

See Also:

Oracle9i SQL Reference and Oracle9i Database Administrator's Guide for more information about the syntax for this statement 

Using Nonunique Indexes to Enforce Uniqueness

You can use an existing nonunique index on a table to enforce uniqueness, either for UNIQUE constraints or the unique aspect of a PRIMARY KEY constraint. The advantage of this approach is that the index remains available and valid when the constraint is disabled. Therefore, enabling a disabled UNIQUE or PRIMARY KEY constraint does not require rebuilding the unique index associated with the constraint. This can yield significant time savings on enable operations for large tables.

Using a nonunique index to enforce uniqueness also lets you eliminate redundant indexes. You do not need a unique index on a primary key column if that column already is included as the prefix of a composite index. You can use the existing index to enable and enforce the constraint. You also save significant space by not duplicating the index. However, if the existing index is partitioned, then the partitioning key of the index must also be a subset of the UNIQUE key; otherwise, Oracle creates an additional unique index to enforce the constraint.

Using Enabled Novalidated Constraints

An enabled novalidated constraint behaves similarly to an enabled validated constraint for new data. Placing a constraint in the enabled novalidated state signifies that any new data entered into the table must conform to the constraint. Existing data is not checked. Placing a constraint in the enabled novalidated state allows you to enable the constraint without locking the table.

If you change a constraint from disabled to enabled, then the table must be locked. No new DML, queries, or DDL can occur because there is no mechanism to ensure that operations on the table conform to the constraint during the enable operation. The enabled novalidated state prevents operations violating the constraint from being performed on the table.

An enabled novalidated constraint can be validated with a parallel, consistent-read query of the table to determine whether any data violates the constraint. No locking is performed and the enable operation does not block readers or writers to the table. In addition, enabled novalidated constraints can be validated in parallel: multiple constraints can be validated at the same time and each constraint's validity check can be determined using parallel query.

Use the following approach to create tables with constraints and indexes:

  1. Create the tables with the constraints. NOT NULL constraints can be unnamed and should be created enabled and validated. All other constraints (CHECK, UNIQUE, PRIMARY KEY, and FOREIGN KEY) should be named and "created disabled".


    By default, constraints are created in the ENABLED state. 

  2. Load old data into the tables.

  3. Create all indexes including indexes needed for constraints.

  4. Enable novalidate all constraints. Do this to primary keys before foreign keys.

  5. Allow users to query and modify data.

  6. With a separate ALTER TABLE statement for each constraint, validate all constraints. Do this to primary keys before foreign keys. For example,


    At this point, use Import or Fast Loader to load data into t.

    CREATE UNIQUE INDEX tai ON t (a); 
    CREATE INDEX tci ON x (c); 

    Now, users can start performing inserts, updates, deletes, and selects on t.


    Now, the constraints are enabled and validated.

    See Also:

    Oracle9i Database Concepts for a complete discussion of integrity constraints 

Using Function-based Indexes

A function-based index includes columns that are either transformed by a function (for example, the UPPER function), or columns that are included in an expression (for example, col1 + col2).

Defining a function-based index on the transformed column or expression allows that data to be returned using the index when that function or expression is used in a WHERE clause or an ORDER BY clause. Therefore, a function-based index can be beneficial when frequently-executed SQL statements include transformed columns (or columns in expressions) in a WHERE (or ORDER BY) clause.

Function-based indexes defined with the UPPER(column_name) or LOWER(column_name) keywords allow case-insensitive searches. For example, the following index:

CREATE INDEX uppercase_idx ON emp (UPPER(empname)); 

facilitates processing queries such as:

WHERE UPPER(empname) = 'MARK'; 


You must set the QUERY_REWRITE_ENABLED session parameter to true to enable function-based indexes for queries. If QUERY_REWRITE_ENABLED is false, then function-based indexes are not used for obtaining the values of an expression in the function-based index. However, function-based indexes can still be used for obtaining values in real columns. QUERY_REWRITE_ENABLED is a session-level and also an instance-level parameter.

If the QUERY_REWRITE_INTEGRITY parameter is set to ENFORCED (the default), then Oracle uses function-based indexes to derive values of SQL expressions only. This also includes SQL functions. If QUERY_REWRITE_INTEGRITY is set to any value other than ENFORCED, then Oracle uses the function-based index, even if it is based on a user-defined (rather than SQL) function. 

Function-based indexes are an efficient mechanism for evaluating statements that contain functions in WHERE clauses. You can create a function-based index to store computational-intensive expressions in the index. This permits Oracle to bypass computing the value of the expression when processing SELECT and DELETE statements. When processing INSERT and UPDATE statements, however, Oracle evaluates the function to process the statement.

For example, if you create the following index:

CREATE INDEX idx ON table_1 (a + b * (c - 1), a, b); 

then Oracle can use it when processing queries such as:

FROM table_1 
WHERE a + b * (c - 1) < 100;

You can also use function-based indexes for linguistic sort indexes that provide efficient linguistic collation in SQL statements.

Oracle treats descending indexes as function-based indexes. The columns marked DESC are sorted in descending order.

See Also:


Using Index-Organized Tables

An index-organized table differs from an ordinary table in that the data for the table is held in its associated index. Changes to the table data, such as adding new rows, updating rows, or deleting rows, result only in updating the index. Because data rows are stored in the index, index-organized tables provide faster key-based access to table data for queries that involve exact match or range search, or both.

See Also:

Oracle9i Database Concepts 

Using Bitmap Indexes

This section describes:

When to Use Bitmap Indexes

This section describes three aspects of indexing that you must evaluate when deciding whether to use bitmap indexing on a given table:

Performance Considerations for Bitmap Indexes

Bitmap indexes can substantially improve performance of queries with the following characteristics:

You can use multiple bitmap indexes to evaluate the conditions on a single table. Bitmap indexes are thus highly advantageous for complex ad hoc queries that contain lengthy WHERE clauses. Bitmap indexes can also provide optimal performance for aggregate queries and for optimizing joins in star schemas.

See Also:

Oracle9i Database Concepts for more information on optimizing anti-joins and semi-joins 

Comparing B-Tree Indexes to Bitmap Indexes

Bitmap indexes can provide considerable storage savings over the use of B-tree indexes. In databases containing only B-tree indexes, you must anticipate the columns that are commonly accessed together in a single query, and create a composite B-tree index on these columns.

Not only would this B-tree index require a large amount of space, it would also be ordered. That is, a B-tree index on (marital_status, region, gender) is useless for queries that only access region and gender. To completely index the database, you must create indexes on the other permutations of these columns. For the simple case of three low-cardinality columns, there are six possible composite B-tree indexes. You must consider the trade-offs between disk space and performance needs when determining which composite B-tree indexes to create.

Bitmap indexes solve this dilemma. Bitmap indexes can be efficiently combined during query execution, so three small single-column bitmap indexes can do the job of six three-column B-tree indexes.

Bitmap indexes are much more efficient than B-tree indexes, especially in data warehousing environments. Bitmap indexes are created not only for efficient space usage but also for efficient execution, and the latter is somewhat more important.

Do not create bitmap indexes on unique key columns. However, for columns where each value is repeated hundreds or thousands of times, a bitmap index typically is less than 25% of the size of a regular B-tree index. The bitmaps themselves are stored in compressed format.

Simply comparing the relative sizes of B-tree and bitmap indexes is not an accurate measure of effectiveness, however. Because of their different performance characteristics, keep B-tree indexes on high-cardinality columns while creating bitmap indexes on low-cardinality columns.

Maintenance Considerations for Bitmap Indexes

Bitmap indexes benefit data warehousing applications, but they are not appropriate for OLTP applications with a heavy load of concurrent INSERTs, UPDATEs, and DELETEs. In a data warehousing environment, data is maintained usually by way of bulk inserts and updates. Index maintenance is deferred until the end of each DML operation. For example, if you insert 1000 rows, then the inserted rows are placed into a sort buffer, and then the updates of all 1000 index entries are batched. (This is why SORT_AREA_SIZE must be set properly for good performance with inserts and updates on bitmap indexes.) Thus, each bitmap segment is updated only once per DML operation, even if more than one row in that segment changes.


The sorts described previously are regular sorts and use the regular sort area, determined by SORT_AREA_SIZE. The BITMAP_MERGE_AREA_SIZE and CREATE_BITMAP_AREA_SIZE initialization parameters described in "Initialization Parameters for Bitmap Indexing" only affect the specific operations indicated by the parameter names.  

DML and DDL statements, such as UPDATE, DELETE, DROP TABLE, affect bitmap indexes the same way they do traditional indexes: the consistency model is the same. A compressed bitmap for a key value is made up of one or more bitmap segments, each of which is at most half a block in size (but can be smaller). The locking granularity is one such bitmap segment. This can affect performance in environments where many transactions make simultaneous updates. If numerous DML operations have caused increased index size and decreasing performance for queries, then you can use the ALTER INDEX ... REBUILD statement to compact the index and restore efficient performance.

A B-tree index entry contains a single rowid. Therefore, when the index entry is locked, a single row is locked. With bitmap indexes, an entry can potentially contain a range of rowids. When a bitmap index entry is locked, the entire range of rowids is locked. The number of rowids in this range affects concurrency. As the number of rowids increases in a bitmap segment, concurrency decreases.

Locking issues affect DML operations and can affect heavy OLTP environments. Locking issues do not, however, affect query performance. As with other types of indexes, updating bitmap indexes is a costly operation. Nonetheless, for bulk inserts and updates where many rows are inserted or many updates are made in a single statement, performance with bitmap indexes can be better than with regular B-tree indexes.

Using Bitmap Indexes with Good Performance

Using Hints with Bitmap Indexes

The INDEX hint works with bitmap indexes in the same way as with traditional indexes.

The INDEX_COMBINE hint identifies the most cost effective indexes for the optimizer. The optimizer recognizes all indexes that can potentially be combined, given the predicates in the WHERE clause. However, it might not be cost effective to use all of them. Oracle recommends using INDEX_COMBINE rather than INDEX for bitmap indexes, because it is a more versatile hint.

In deciding which of these hints to use, the optimizer includes nonhinted indexes that appear cost effective, as well as indexes named in the hint. If certain indexes are given as arguments for the hint, then the optimizer tries to use some combination of those particular bitmap indexes.

If the hint does not name indexes, then all indexes are considered hinted. Hence, the optimizer tries to combine as many as possible given the WHERE clause, without regard to cost effectiveness. The optimizer always tries to use hinted indexes in the plan regardless of whether it considers them cost effective.

See Also:

Chapter 5, "Optimizer Hints" for more information on the INDEX_COMBINE hint 

Performance Tips for Bitmap Indexes

To get optimal performance and disk space usage with bitmap indexes, consider the following tips:

This is because Oracle needs to consider the theoretical maximum number of rows that will fit in a data block when creating bitmap indexes.

See Also:

Chapter 9, "Using EXPLAIN PLAN" for more information about bitmap EXPLAIN PLAN output 

Mapping Bitmaps to Rowids Efficiently

Use SQL statements with the ALTER TABLE syntax to optimize the mapping of bitmaps to rowids. The MINIMIZE RECORDS_PER_BLOCK clause enables this optimization and the NOMINIMIZE RECORDS_PER_BLOCK clause disables it.

When enabled, Oracle scans the table and determines the maximum number of records in any block and restricts this table to this maximum number. This enables bitmap indexes to allocate fewer bits per block and results in smaller bitmap indexes. The block and record allocation restrictions this statement places on the table are only beneficial to bitmap indexes. Therefore, Oracle does not recommend using this mapping on tables that are not heavily indexed with bitmap indexes.

See Also:


Using Bitmap Indexes on Index-Organized Tables

The rowids used in bitmap indexes on index-organized tables are in a mapping table, not the base table. The mapping table maintains a mapping of logical rowids (needed to access the index-organized table) to physical rowids (needed by the bitmap index code). There is one mapping table per index-organized table, and it is used by all the bitmap indexes created on the index-organized table.


Moving rows in an index-organized table does not make the bitmap indexes built on that index-organized table unusable.  

See Also:

Oracle9i Database Concepts for information on bitmap indexes and index-organized tables 

Indexing Null Values

Bitmap indexes index nulls, whereas all other index types do not. Consider, for example, a table with STATE and PARTY columns, on which you want to perform the following query:

FROM people 
WHERE state='CA' 

AND party !='D'; 

Indexing nulls enables a bitmap minus plan where bitmaps for party equal to D and NULL are subtracted from state bitmaps equal to CA. The EXPLAIN PLAN output looks like the following:


If a NOT NULL constraint existed on party, then the second minus operation (where party is null) is left out because it is not needed.

Initialization Parameters for Bitmap Indexing

The following initialization parameters affect various aspects of using bitmap indexes:

CREATE_BITMAP_AREA_SIZE: memory allocated for bitmap creation

BITMAP_MERGE_AREA_SIZE: memory used to merge bitmaps from an index range scan

SORT_AREA_SIZE: memory used when inserting or updating bitmap indexes

See Also:

Oracle9i Database Reference for more information on these parameters 

Using Bitmap Access Plans on Regular B-tree Indexes

If there is at least one bitmap index on the table, then the optimizer considers using a bitmap access path using regular B-tree indexes for that table. This access path can involve combinations of B-tree and bitmap indexes, but may not involve any bitmap indexes at all. However, the optimizer will not generate a bitmap access path using a single B-tree index unless instructed to do so by a hint.

To use bitmap access paths for B-tree indexes, the rowids stored in the indexes must be converted to bitmaps. After such a conversion, the various Boolean operations available for bitmaps can be used. As an example, consider the following query, where there is a bitmap index on column c1, and regular B-tree indexes on columns c2 and c3.

WHERE c1 = 2 AND c2 = 6 
OR c3 BETWEEN 10 AND 20;

         BITMAP OR
           BITMAP AND
             BITMAP INDEX c1_ind SINGLE VALUE
               INDEX c2_ind RANGE SCAN
             SORT ORDER BY
               INDEX c3_ind RANGE SCAN


This statement is executed by accessing indexes only, so no table access is necessary. 

Here, a COUNT option for the BITMAP CONVERSION row source counts the number of rows matching the query. There are also conversions FROM rowids in the plan to generate bitmaps from the rowids retrieved from the B-tree indexes. The occurrence of the ORDER BY sort in the plan is due to the fact that the conditions on column c3 result in more than one list of rowids being returned from the B-tree index. These lists are sorted before they can be converted into a bitmap.

Bitmap Index Restrictions

Bitmap indexes have the following restrictions:

Using Bitmap Join Indexes

In addition to a bitmap index on a single table, you can create a bitmap join index, which is a bitmap index for the join of two or more tables. A bitmap join index is a space efficient way of reducing the volume of data that must be joined by performing restrictions in advance. For each value in a column of a table, a bitmap join index stores the rowids of corresponding rows in another table. In a data warehousing environment, the join condition is an equi-inner join between the primary key column or columns of the dimension tables and the foreign key column or columns in the fact table.

Bitmap join indexes are much more efficient in storage than materialized join views, an alternative for materializing joins in advance. This is because the materialized join views do not compress the rowids of the fact tables.

See Also:

Oracle9i Data Warehousing Guide for examples and restrictions of bitmap join indexes 

Using Domain Indexes

Domain indexes are built using the indexing logic supplied by a user-defined indextype. An indextype provides an efficient mechanism to access data that satisfy certain operator predicates. Typically, the user-defined indextype is part of an Oracle option, like the Spatial option.

For example, the SpatialIndextype allows efficient search and retrieval of spatial data that overlap a given bounding box.

The cartridge determines the parameters you can specify in creating and maintaining the domain index. Similarly, the performance and storage characteristics of the domain index are presented in the specific cartridge documentation.

Refer to the appropriate cartridge documentation for information such as:

Using Clusters

Clusters are groups of one or more tables physically stored together because they share common columns and usually are used together. Because related rows are physically stored together, disk access time improves.

See Also:

Oracle9i Database Concepts for more information on clusters 

Follow these guidelines when deciding whether to cluster tables:

Consider the benefits and drawbacks of clusters with respect to the needs of the application. For example, you might decide that the performance gain for join statements outweighs the performance loss for statements that modify cluster key values. You might want to experiment and compare processing times with the tables both clustered and stored separately. To create a cluster, use the CREATE CLUSTER statement.

See Also:

Oracle9i Application Developer's Guide - Fundamentals for more information on creating clusters 

Using Hash Clusters

Hash clusters group table data by applying a hash function to each row's cluster key value. All rows with the same cluster key value are stored together on disk. Consider the benefits and drawbacks of hash clusters with respect to the needs of the application. You might want to experiment and compare processing times with a particular table as it is stored in a hash cluster, and as it is stored alone with an index. This section describes:

Follow these guidelines for choosing when to use hash clusters:

Go to previous page Go to next page
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index