This chapter discusses the management of indexes, and contains the following topics:
Indexes are optional structures associated with tables and clusters that allow SQL statements to execute more quickly against a table. Just as the index in this manual helps you locate information faster than if there were no index, 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 you see them more quickly.
Oracle Database provides several indexing schemes that provide complementary performance functionality. These are:
B-tree indexes: the default and the most common
B-tree cluster indexes: defined specifically for cluster
Hash cluster indexes: defined specifically for a hash cluster
Global and local indexes: relate to partitioned tables and indexes
Reverse key indexes: most useful for Oracle Real Application Clusters applications
Bitmap indexes: compact; work best for columns with a small set of values
Function-based indexes: contain the precomputed value of a function/expression
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.
See Also:Chapter 14, "Managing Space for Schema Objects" is recommended reading before attempting tasks described in this chapter.
This section discusses guidelines for managing indexes and contains the following topics:
Oracle Database Concepts for conceptual information about indexes and indexing, including descriptions of the various indexing schemes offered by Oracle
Oracle Database Data Cartridge Developer's Guide for information about defining domain-specific operators and indexing schemes and integrating them into the Oracle Database server
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.
Under certain conditions, data can be loaded into a table with SQL*Loader direct-path load and an index can be created as data is loaded.
See Also:Oracle Database Utilities for information about using SQL*Loader for direct-path load
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.
To improve performance on joins of multiple tables, index columns used for joins.
Note:Primary and unique keys automatically have indexes, but you might want to create an index on a foreign key.
Small tables do not require indexes. If a query is taking too long, then the table might have grown from small to large.
Values are relatively unique in the column.
There is a wide range of values (good for regular indexes).
There is a small range of values (good for bitmap indexes).
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:
There are many nulls in the column and you do not search on the not null values.
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.
The order of columns in the
INDEX statement can affect query performance. In general, specify the most frequently used columns first.
If you create a single index across columns to speed up queries that access, for example,
col3; then queries that access just
col1, or that access just
col2, are also speeded up. But a query that accessed just
col3, or just
col3 does not use the index.
A table can have any number of indexes. However, the more indexes there are, the more overhead is incurred as the table is modified. Specifically, when rows are inserted or deleted, all indexes on the table must be updated as well. Also, when a column is updated, all indexes that contain the column must be updated.
Thus, there is a trade-off between the speed of retrieving data from a table and the speed of updating the table. For example, if a table is primarily read-only, having more indexes can be useful; but if a table is heavily updated, having fewer indexes could be preferable.
Consider dropping an index if:
It does not speed up queries. The table could be very small, or there could be many rows in the table but very few index entries.
The queries in your applications do not use the index.
The index must be dropped before being rebuilt.
See Also:"Monitoring Index Usage"
Estimating the size of an index before creating one can facilitate better disk space planning and management. You can use the combined estimated size of indexes, along with estimates for tables, the undo tablespace, and redo log files, to determine the amount of disk space that is required to hold an intended database. From these estimates, you can make correct hardware purchases and other decisions.
Use the estimated size of an individual index to better manage the disk space that the index uses. When an index is created, you can set appropriate storage parameters and improve I/O performance of applications that use the index. For example, assume that you estimate the maximum size of an index before creating it. If you then set the storage parameters when you create the index, fewer extents are allocated for the table data segment, and all of the index data is stored in a relatively contiguous section of disk space. This decreases the time necessary for disk I/O operations involving this index.
The maximum size of a single index entry is approximately one-half the data block size.
See Also:"Managing Storage Parameters" for specific information about storage parameters
Indexes can be created in any tablespace. An index can be created in the same or different tablespace as the table it indexes. If you use the same tablespace for a table and its index, it can be more convenient to perform database maintenance (such as tablespace or file backup) or to ensure application availability. All the related data is always online together.
Using different tablespaces (on different disks) for a table and its index produces better performance than storing the table and index in the same tablespace. Disk contention is reduced. But, if you use different tablespaces for a table and its index and one tablespace is offline (containing either data or index), then the statements referencing that table are not guaranteed to work.
You can parallelize index creation, much the same as you can parallelize table creation. Because multiple processes work together to create the index, the database can create the index more quickly than if a single server process created the index sequentially.
When creating an index in parallel, storage parameters are used separately by each query server process. Therefore, an index created with an
INITIAL value of 5M and a parallel degree of 12 consumes at least 60M of storage during index creation.
Note:Because indexes created using
NOLOGGINGare not archived, perform a backup after you create the index.
Creating an index with
NOLOGGING has the following benefits:
Space is saved in the redo log files.
The time it takes to create the index is decreased.
Performance improves for parallel creation of large indexes.
In general, the relative performance improvement is greater for larger indexes created without
LOGGING than for smaller ones. Creating small indexes without
LOGGING has little effect on the time it takes to create an index. However, for larger indexes the performance improvement can be significant, especially when you are also parallelizing the index creation.
Improper sizing or increased growth can produce index fragmentation. To eliminate or reduce fragmentation, you can rebuild or coalesce the index. But before you perform either task weigh the costs and benefits of each option and choose the one that works best for your situation. Table 16-1 is a comparison of the costs and benefits associated with rebuilding and coalescing indexes.
|Rebuild Index||Coalesce Index|
Quickly moves index to another tablespace
Cannot move index to another tablespace
Higher costs: requires more disk space
Lower costs: does not require more disk space
Creates new tree, shrinks height if applicable
Coalesces leaf blocks within same branch of tree
Enables you to quickly change storage and tablespace parameters without having to drop the original index.
Quickly frees up index leaf blocks for use.
In situations where you have B-tree index leaf blocks that can be freed up for reuse, you can merge those leaf blocks using the following statement:
ALTER INDEX vmoore COALESCE;
Figure 16-1 illustrates the effect of an
ALTER INDEX COALESCE on the index
vmoore. Before performing the operation, the first two leaf blocks are 50% full. This means you have an opportunity to reduce fragmentation and completely fill the first block, while freeing up the second.
Because unique and primary keys have associated indexes, you should factor in the cost of dropping and creating indexes when considering whether to disable or drop a
PRIMARY KEY constraint. If the associated index for a
UNIQUE key or
PRIMARY KEY constraint is extremely large, you can save time by leaving the constraint enabled rather than dropping and re-creating the large index. You also have the option of explicitly specifying that you want to keep or drop the index when dropping or disabling a
PRIMARY KEY constraint.
See Also:"Managing Integrity Constraints"
The table or cluster to be indexed is in your own schema.
INDEX privilege on the table to be indexed.
CREATE ANY INDEX system privilege.
To create an index in another schema, all of the following conditions must be true:
CREATE ANY INDEX system privilege.
The owner of the other schema has a quota for the tablespaces to contain the index or index partitions, or
UNLIMITED TABLESPACE system privilege.
This section contains the following topics:
You can create indexes explicitly (outside of integrity constraints) using the SQL statement
CREATE INDEX. The following statement creates an index named
emp_ename for the
ename column of the
CREATE INDEX emp_ename ON emp(ename) TABLESPACE users STORAGE (INITIAL 20K NEXT 20k PCTINCREASE 75);
Notice that several storage settings and a tablespace are explicitly specified for the index. If you do not specify storage options (such as
NEXT) for an index, the default storage options of the default or specified tablespace are automatically used.
See Also:Oracle Database SQL Reference for syntax and restrictions on the use of the
Indexes can be unique or non-unique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns). Non-unique indexes do not impose this restriction on the column values.
CREATE UNIQUE INDEX dept_unique_index ON dept (dname) TABLESPACE indx;
Alternatively, you can define
UNIQUE integrity constraints on the desired columns. The database enforces
UNIQUE integrity constraints by automatically defining a unique index on the unique key. This is discussed in the following section. However, it is advisable that any index that exists for query performance, including unique indexes, be created explicitly.
See Also:Oracle Database Performance Tuning Guide for more information about creating an index for performance
Oracle Database enforces a
UNIQUE key or
PRIMARY KEY integrity constraint on a table by creating a unique index on the unique key or primary key. This index is automatically created by the database when the constraint is enabled. No action is required by you when you issue the
CREATE TABLE or
ALTER TABLE statement to create the index, but you can optionally specify a
USING INDEX clause to exercise control over its creation. This includes both when a constraint is defined and enabled, and when a defined but disabled constraint is enabled.
To enable a
PRIMARY KEY constraint, thus creating an associated index, the owner of the table must have a quota for the tablespace intended to contain the index, or the
UNLIMITED TABLESPACE system privilege. The index associated with a constraint always takes the name of the constraint, unless you optionally specify otherwise.
Note:An efficient procedure for enabling a constraint that can make use of parallelism is described in"Efficient Use of Integrity Constraints: A Procedure".
You can set the storage options for the indexes associated with
PRIMARY KEY constraints using the
USING INDEX clause. The following
CREATE TABLE statement enables a
PRIMARY KEY constraint and specifies the storage options of the associated index:
CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, age INTEGER) ENABLE PRIMARY KEY USING INDEX TABLESPACE users;
If you require more explicit control over the indexes associated with
PRIMARY KEY constraints, the database lets you:
Specify an existing index that the database is to use to enforce the constraint
INDEX statement that the database is to use to create the index and enforce the constraint
These options are specified using the
INDEX clause. The following statements present some examples.
CREATE TABLE a ( a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));
CREATE TABLE b( b1 INT, b2 INT, CONSTRAINT bu1 UNIQUE (b1, b2) USING INDEX (create unique index bi on b(b1, b2)), CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi);
CREATE TABLE c(c1 INT, c2 INT); CREATE INDEX ci ON c (c1, c2); ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;
If a single statement creates an index with one constraint and also uses that index for another constraint, the system will attempt to rearrange the clauses to create the index before reusing it.
See Also:"Managing Integrity Constraints"
Oracle Database provides you with the opportunity to collect statistics at very little resource cost during the creation or rebuilding of an index. These statistics are stored in the data dictionary for ongoing use by the optimizer in choosing a plan for the execution of SQL statements. The following statement computes index, table, and column statistics while building index e
mp_ename on column
ename of table
CREATE INDEX emp_ename ON emp(ename) COMPUTE STATISTICS;
Create a new temporary tablespace using the
CREATE TABLESPACE or
CREATE TEMPORARY TABLESPACE statement.
TEMPORARY TABLESPACE option of the
ALTER USER statement to make this your new temporary tablespace.
Create the index using the
CREATE INDEX statement.
Drop this tablespace using the
DROP TABLESPACE statement. Then use the
ALTER USER statement to reset your temporary tablespace to your original temporary tablespace.
Using this procedure can avoid the problem of expanding your usual, and usually shared, temporary tablespace to an unreasonably large size that might affect future performance.
You can create and rebuild indexes online. This enables you to update base tables at the same time you are building or rebuilding indexes on that table. You can perform DML operations while the index build is taking place, but DDL operations are not allowed. Parallel execution is not supported when creating or rebuilding an index online.
The following statements illustrate online index build operations:
CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;
Note:While you can perform DML operations during an online index build, Oracle recommends that you do not perform major/large DML operations during this procedure. This is because while the DML on the base table is taking place it holds a lock on that resource. The DDL to build the index cannot proceed until the transaction acting on the base table commits or rolls back, thus releasing the lock.
For example, if you want to load rows that total up to 30% of the size of an existing table, you should perform this load before the online index build.
See Also:"Rebuilding an Existing Index"
To create a function-based index, you must have the
COMPATIBLE parameter set to 126.96.36.199.0 or higher. In addition to the prerequisites for creating a conventional index, if the index is based on user-defined functions, then those functions must be marked
DETERMINISTIC. Also, you just have the
EXECUTE object privilege on any user-defined function(s) used in the function-based index if those functions are owned by another user.
Additionally, to use a function-based index:
The table must be analyzed after the index is created.
The query must be guaranteed not to need any
NULL values from the indexed expression, since
NULL values are not stored in indexes.
CREATE INDEXstores the timestamp of the most recent function used in the function-based index. This timestamp is updated when the index is validated. When performing tablespace point-in-time recovery of a function-based index, if the timestamp on the most recent function used in the index is newer than the timestamp stored in the index, then the index is marked invalid. You must use the
ANALYZE INDEX...VALIDATE STRUCTUREstatement to validate this index.
To illustrate a function-based index, consider the following statement that defines a function-based index (
area_index) defined on the function
CREATE INDEX area_index ON rivers (area(geo));
In the following SQL statement, when
area(geo) is referenced in the
WHERE clause, the optimizer considers using the index
SELECT id, geo, area(geo), desc FROM rivers WHERE Area(geo) >5000;
Table owners should have
EXECUTE privileges on the functions used in function-based indexes.
Because a function-based index depends upon any function it is using, it can be invalidated when a function changes. If the function is valid, you can use an
ALTER INDEX...ENABLE statement to enable a function-based index that has been disabled. The
ALTER INDEX...DISABLE statement lets you disable the use of a function-based index. Consider doing this if you are working on the body of the function.
Oracle Database Concepts for more information about function-based indexes
Oracle Database Application Developer's Guide - Fundamentals for information about using function-based indexes in applications and examples of their use
Key compression breaks an index key into a prefix and a suffix entry. Compression is achieved by sharing the prefix entries among all the suffix entries in an index block. This sharing can lead to huge savings in space, allowing you to store more keys for each index block while improving performance.
Key compression can be useful in the following situations:
You have a non-unique index where
ROWID is appended to make the key unique. If you use key compression here, the duplicate key is stored as a prefix entry on the index block without the
ROWID. The remaining rows become suffix entries consisting of only the
You have a unique multicolumn index.
You enable key compression using the
COMPRESS clause. The prefix length (as the number of key columns) can also be specified to identify how the key columns are broken into a prefix and suffix entry. For example, the following statement compresses duplicate occurrences of a key in the index leaf block:
CREATE INDEX emp_ename ON emp(ename) TABLESPACE users COMPRESS 1;
COMPRESS clause can also be specified during rebuild. For example, during rebuild you can disable compression as follows:
ALTER INDEX emp_ename REBUILD NOCOMPRESS;
See Also:Oracle Database Concepts for a more detailed discussion of key compression
Rebuild or coalesce an existing index
Deallocate unused space or allocate a new extent
Specify parallel execution (or not) and alter the degree of parallelism
Alter storage parameters or physical attributes
Enable or disable key compression
Mark the index unusable
Start or stop the monitoring of index usage
You cannot alter index column structure.
More detailed discussions of some of these operations are contained in the following sections:
Alter the storage parameters of any index, including those created by the database to enforce primary and unique key integrity constraints, using the
ALTER INDEX statement. For example, the following statement alters the
ALTER INDEX emp_ename STORAGE (PCTINCREASE 50);
The storage parameters
MINEXTENTS cannot be altered. All new settings for the other storage parameters affect only extents subsequently allocated for the index.
For indexes that implement integrity constraints, you can adjust storage parameters by issuing an
ALTER TABLE statement that includes the
USING INDEX subclause of the
ENABLE clause. For example, the following statement changes the storage options of the index created on table
emp to enforce the primary key constraint:
ALTER TABLE emp ENABLE PRIMARY KEY USING INDEX;
See Also:Oracle Database SQL Reference for syntax and restrictions on the use of the
Before rebuilding an existing index, compare the costs and benefits associated with rebuilding to those associated with coalescing indexes as described in Table 16-1.
When you rebuild an index, you use an existing index as the data source. Creating an index in this manner enables you to change storage characteristics or move to a new tablespace. Rebuilding an index based on an existing data source removes intra-block fragmentation. Compared to dropping the index and using the
CREATE INDEX statement, re-creating an existing index offers better performance.
The following statement rebuilds the existing index
ALTER INDEX emp_name REBUILD;
REBUILD clause must immediately follow the index name, and precede any other options. It cannot be used in conjunction with the
DEALLOCATE UNUSED clause.
You have the option of rebuilding the index online. Rebuilding online enables you to update base tables at the same time that you are rebuilding. The following statement rebuilds the
emp_name index online:
ALTER INDEX emp_name REBUILD ONLINE;
Note:Online index rebuilding has stricter limitations on the maximum key length that can be handled, compared to other methods of rebuilding an index. If an ORA-1450 (maximum key length exceeded) error occurs when rebuilding online, try rebuilding offline, coalescing, or dropping and recreating the index.
Oracle Database provides a means of monitoring indexes to determine whether they are being used. If an index is not being used, then it can be dropped, eliminating unnecessary statement overhead.
To start monitoring the usage of an index, issue this statement:
ALTER INDEX index MONITORING USAGE;
Later, issue the following statement to stop the monitoring:
ALTER INDEX index NOMONITORING USAGE;
V$OBJECT_USAGE can be queried for the index being monitored to see if the index has been used. The view contains a
USED column whose value is
NO, depending upon if the index has been used within the time period being monitored. The view also contains the start and stop times of the monitoring period, and a
MONITORING column (
NO) to indicate if usage monitoring is currently active.
Each time that you specify
MONITORING USAGE, the
V$OBJECT_USAGE view is reset for the specified index. The previous usage information is cleared or reset, and a new start time is recorded. When you specify
NOMONITORING USAGE, no further monitoring is performed, and the end time is recorded for the monitoring period. Until the next
ALTER INDEX...MONITORING USAGE statement is issued, the view information is left unchanged.
If key values in an index are inserted, updated, and deleted frequently, the index can lose its acquired space efficiently over time. Monitor index efficiency of space usage at regular intervals by first analyzing the index structure, using the
ANALYZE INDEX...VALIDATE STRUCTURE statement, and then querying the
SELECT PCT_USED FROM INDEX_STATS WHERE NAME = 'index';
The percentage of index space usage varies according to how often index keys are inserted, updated, or deleted. Develop a history of average efficiency of space usage for an index by performing the following sequence of operations several times:
Validating the index
Dropping and rebuilding (or coalescing) the index
When you find that index space usage drops below its average, you can condense the index space by dropping the index and rebuilding it, or coalescing it.
The index is no longer required.
The index is not providing anticipated performance improvements for queries issued against the associated table. For example, the table might be very small, or there might be many rows in the table but very few index entries.
Applications do not use the index to query the data.
The index has become invalid and must be dropped before being rebuilt.
The index has become too fragmented and must be dropped before being rebuilt.
When you drop an index, all extents of the index segment are returned to the containing tablespace and become available for other objects in the tablespace.
How you drop an index depends on whether you created the index explicitly with a
CREATE INDEX statement, or implicitly by defining a key constraint on a table. If you created the index explicitly with the
CREATE INDEX statement, then you can drop the index with the
DROP INDEX statement. The following statement drops the
DROP INDEX emp_ename;
Note:If a table is dropped, all associated indexes are dropped automatically.
The following views display information about indexes:
||These views describe the columns of indexes on tables. Some columns in these views contain statistics that are generated by the
||These views describe the expressions of function-based indexes on tables.|
||These views contain optimizer statistics for indexes.|
||Stores information from the last
||Stores information from the last
||Contains index usage information produced by the
See Also:Oracle Database Reference for a complete description of these views