|Oracle9i Application Developer's Guide - Fundamentals
Release 2 (9.2)
Part Number A96590-01
This chapter discusses the considerations for using the different types of indexes in an application. The topics include:
Indexes are used in Oracle to provide quick access to rows in a table. Indexes provide faster access to data for operations that return a small portion of a table's rows.
Although Oracle allows an unlimited number of indexes on a table, the indexes only help if they are used to speed up queries. Otherwise, they just take up space and add overhead when the indexed columns are updated. You should use the EXPLAIN PLAN feature to determine how the indexes are being used in your queries. Sometimes, if an index is not being used by default, you can use a query hint so that the index is used.
The following sections explain how to create, alter, and drop indexes using SQL commands. Some simple guidelines to follow when managing indexes are included.
Oracle9i Database Performance Guide and Reference for information on query hints and measuring the performance benefits of indexes.
Typically, you insert or load data into a table (using SQL*Loader or Import) before creating indexes. Otherwise, the overhead of updating the index slows down the insert or load operation. The exception to this rule is that you must create an index for a cluster before you insert any data into the cluster.
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 for each 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:
TEMPORARY TABLESPACEoption of the
ALTER USERcommand to make this your new temporary tablespace.
DROP TABLESPACEcommand. Then use the
ALTER USERcommand to reset your temporary tablespace to your original temporary tablespace.
Under certain conditions, you can load data into a table with the SQL*Loader "direct path load", and an index can be created as data is loaded.
Oracle9i Database Utilities for information on direct path load.
Use the following guidelines for determining when to create an index:
Some columns are strong candidates for indexing. Columns with one or more of the following characteristics are candidates for indexing:
is preferable to
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:
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. Consult with the database administrator for assistance in determining the space required by an index.
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.
You must weigh the performance benefit of indexes for queries against the performance overhead of updates. For example, if a table is primarily read-only, you might use more indexes; but, if a table is heavily updated, you might use fewer indexes.
Although you can specify columns in any order in the
INDEX command, the order of columns in the
INDEX statement can affect query performance. In general, you should put the column expected to be used most often first in the index. You can create a composite index (using several columns), and the same index can be used for queries that reference all of these columns, or just some of them.
For example, assume the columns of the
VENDOR_PARTS table are as shown in Figure 5-1.
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:
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:
Composite indexes speed up queries that use 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.
The database can use indexes more effectively when it has statistical information about the tables involved in the queries. You can gather statistics when the indexes are created by including the keywords
COMPUTE STATISTICS in the
CREATE INDEX statement. As data is updated and the distribution of values changes, you or the DBA can periodically refresh the statistics by calling procedures like
You might drop an index if:
When you drop an index, all extents of the index's segment are returned to the containing tablespace and become available for other objects in the tablespace.
Use the SQL command
INDEX to drop an index. For example, the following statement drops a specific named index:
If you drop a table, then all associated indexes are dropped.
To drop an index, the index must be contained in your schema or you must have the
INDEX system privilege.
When using indexes in an application, you might need to request that the DBA grant privileges or make changes to initialization parameters.
To create a new index, you must own, or have the
INDEX object privilege for, the corresponding table. The schema that contains the index must also have a quota for the tablespace intended to contain the index, or the
TABLESPACE system privilege. To create an index in another user's schema, you must have the
INDEX system privilege.
Function-based indexes also require the
QUERY_REWRITE privilege, and that the
QUERY_REWRITE_ENABLED initialization parameter to be set to
You can create an index for a table to improve the performance of queries issued against the corresponding table. You can also create an index for a cluster. You can create a composite index on multiple columns up to a maximum of 32 columns. A composite index key cannot exceed roughly one-half (minus some overhead) of the available space in the data block.
Oracle automatically creates an index to enforce a
KEY integrity constraint. In general, it is better to create such constraints to enforce uniqueness, instead of using the obsolete
Use the SQL command
INDEX to create an index.
In this example, an index is created for a single column, to speed up queries that test that column:
In this example, several storage settings are explicitly specified for the index:
CREATE INDEX emp_ename ON emp_tab(ename) TABLESPACE users STORAGE (INITIAL 20K NEXT 20k PCTINCREASE 75) PCTFREE 0 COMPUTE STATISTICS;
In this example, the index applies to two columns, to speed up queries that test either the first column or both columns:
In this example, the query is going to sort on the function
UPPER(ENAME). An index on the
ENAME column itself would not speed up this operation, and it might be slow to call the function for each result row. A function-based index precomputes the result of the function for each column value, speeding up queries that use the function for searching or sorting:
Domain indexes are appropriate for special-purpose applications implemented using data cartridges. The domain index helps to manipulate complex data, such as spatial, time-series, audio, or video data. If you need to develop such an application, see Oracle9i Data Cartridge Developer's Guide .
Oracle supplies a number of specialized data cartridges to help manage these kinds of complex data. So, if you need to create a search engine, or a geographic information system, you can do much of the work simply by creating the right kind of index.
A function-based index is an index built on an expression. It extends your indexing capabilities beyond indexing on a column. A function-based index increases the variety of ways in which you can access data.
The expression indexed by a function-based index can be an arithmetic expression or an expression that contains a PL/SQL function, package function, C callout, or SQL function. Function-based indexes also support linguistic sorts based on collation keys, efficient linguistic collation of SQL statements, and case-insensitive sorts.
Like other indexes, function-based indexes improve query performance. For example, if you need to access a computationally complex expression often, then you can store it in an index. Then when you need to access the expression, it is already computed. You can find a detailed description of the advantages of function-based indexes in "Advantages of Function-Based Indexes".
Function-based indexes have all of the same properties as indexes on columns. However, unlike indexes on columns which can be used by both cost-based and rule-based optimization, function-based indexes can be used by only by cost-based optimization. Other restrictions on function-based indexes are described in "Restrictions for Function-Based Indexes".
CREATE INDEX Idx ON Example_tab(Column_a + Column_b); SELECT * FROM Example_tab WHERE Column_a + Column_b < 10;
The optimizer can use a range scan for this query because the index is built on (
column_b). Range scans typically produce fast response times if the predicate selects less than 15% of the rows of a large table. The optimizer can estimate how many rows are selected by expressions more accurately if the expressions are materialized in a function-based index. (Expressions of function-based indexes are represented as virtual columns and
ANALYZE can build histograms on such columns.)
MAPmethod to build indexes on an object type column.
LOWERfunctions, descending order sorts with the
DESCkeyword, and linguistic-based sorts with the
Oracle sorts columns with the
Another function-based index calls the object method
distance_from_equator for each city in the table. The method is applied to the object column
Reg_Obj. A query could use this index to quickly find cities that are more than 1000 miles from the equator:
CREATE INDEX Distance_index ON Weatherdata_tab (Distance_from_equator (Reg_obj)); SELECT * FROM Weatherdata_tab WHERE (Distance_from_equator (Reg_Obj)) > '1000';
Another index stores the temperature delta and the maximum temperature. The result of the delta is sorted in descending order. A query could use this index to quickly find table rows where the temperature delta is less than 20 and the maximum temperature is greater than 75.
CREATE INDEX compare_index ON Weatherdata_tab ((Maxtemp - Mintemp) DESC, Maxtemp); SELECT * FROM Weatherdata_tab WHERE ((Maxtemp - Mintemp) < '20' AND Maxtemp > '75');
The following command allows faster case-insensitive searches in table
SELECT command uses the function-based index on
e_name) to return all of the employees with name like :
The following command computes a value for each row using columns A, B, and C, and stores the results in the index.
SELECT statement can either use index range scan (since the expression is a prefix of index
IDX) or index fast full scan (which may be preferable if the index has specified a high parallel degree).
This example demonstrates how a function-based index can be used to sort based on the collation order for a national language. The
NLSSORT function returns a sort key for each name, using the collation sequence
SELECT statement selects all of the contents of the table and orders it by
NAME. The rows are ordered using the German collation sequence. The Globalization Support parameters are not needed in the
SELECT statement, because in a German session,
NLS_SORT is set to
NLS_COMP is set to
Note the following restrictions for function-based indexes:
QUERY_REWRITE_ENABLEDinitialization parameter to
TRUE, and call
DBMS_STATS.GATHER_SCHEMA_STATISTICS, for the function-based index to be effective.
DETERMINISTIC. That is, they always return the same result given the same input, like the
UPPERfunction. You must ensure that the subprogram really is deterministic, because Oracle does not check that the assertion is true.
The following semantic rules demonstrate how to use the keyword
PACKAGElevel subprogram can be declared as
PACKAGEspecification but not in the
BODY. Errors are raised if
DETERMINISTICis used inside a
BODY) cannot be declared as
DETERMINISTICsubprogram can call another subprogram whether the called program is declared as
COMPATIBLEset to 220.127.116.11.0 or higher,
NOT NULL. To avoid a full table scan, you must ensure that the query cannot fetch null values.
RAWdata types of unknown length from PL/SQL functions. A workaround is to limit the size of the function's output by indexing a substring of known length:
-- The INITIALS() function might return 1 letter, 2 letters, 3 letters, etc. -- We limit the return value to 10 characters for purposes of the index. CREATE INDEX func_substr_index ON emp_tab(substr(initials(ename),1,10); -- Call SUBSTR both when creating the index and when referencing -- the function in queries. SELECT SUBSTR(initials(ename),1,10) FROM emp_tab;