4
Selecting an Index Strate gy

This chapter discusses considerations for using the differ ent types of indexes in an application. The topics include:

• Guidelines for Application-Specific Indexes
• < a href="adfns_in.htm#1006333">Creating Indexes: Basic Examples
• When to Use Domain Indexes
• When to Use Function-Based Indexes

  See Also: Oracle Database Administrat or's Guide for information about creating and managing indexes Oracle Database Performance Tuning Guide for detaile d information about using indexes Oracle Database SQL Reference for the syntax of commands to work with indexes Oracle Database Administrator's Guide for information on creating hash clusters to improve performance, as a n alternative to indexing

< font face="Arial, Helvetica, sans-serif" color="#330099">Guidelines for Application-Specific Indexes

You can create indexes on columns to speed up queries. Indexes provide faster access to data for ope rations that return a small portion of a table's rows.

In general, you should create an ind ex on a column in any of the following situations:

• The column is queried frequently.
• A referential integrity constraint exists on the column .
• A UNIQUE key integrity constraint exists on the column.

You can create an index on any column; however, if the column is not used in any of these si tuations, creating an index on the column does not increase performance and the index takes up resources unnecessarily.

Although the database creates an index for you on a column with an integrity constraint, explicitly crea ting an index on such a column is recommended.

You can use the following techniques to dete rmine which columns are best candidates for indexing:

• Use the EXPLAIN PLAN feature to show a theoretical execution plan of a given query statement.
• < a name="1008084">Use the V$SQL_PLAN view to determine the actual execution plan used for a given query statement.

Sometimes, if an index is not being used by default and it would be most efficient to u se that index, you can use a query hint so that the index is used.

See Also: Oracle Database Performance Tuning Guide for information on using the V$SQL_P LAN view, the EXPLAIN PLAN statement, query hints, and measuring the performance benefits of indexes

The following sections explain how to create, alter, and drop indexes usin g SQL commands, and give guidelines for managing indexes.

Create Indexes After Inserting Table Data

Typically, you insert or load data into a table (using SQL*Loader or Import) before creating indexes. Otherwis e, 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.

Switch Your Temporary Tablespace to Avoid Space Probl ems Creating Indexes

When you create an index on a table that alread y has data, Oracle Database must use sort space to create the index. The database uses the sort space in memory allocated for the cre ator of the index (the amount for each user is determined by the initialization parameter SORT_AREA_SIZE), but the datab ase must also swap sort information to and from temporary segments allocated on behalf of the index creation. If the index is extreme ly large, it can 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 y our new temporary tablespace.
3. Create the index using the CREATE I NDEX command.
4. Drop this tablespace using the DROP TABLESPA CE command. Then use the ALTER USER command to reset your temporary tablespace to your original temporary tablesp ace.

Under certain conditions, you can load data into a table with the SQL*Loader "di rect path load", and an index can be created as data is loaded.

Inde x the Correct Tables and Columns

Use the following guidelines for de termining when to create an index:

• Create an index if you freq uently want to retrieve less than about 15% of the rows in a large table. This threshold percentage varies greatly, however, accordin g 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 t he percentage; the more clustered the row data, the higher the percentage.
• Ind ex columns that are used for joins to improve join performance.
• Primary and un ique keys automatically have indexes, but you might want to create an index on a foreign key; see Chap ter 3, "Maintaining Data Integrity Through Constraints" for more information.
• Small tables do not require indexes; if a query is taking too long, then the table might have grown from small to large.

Some columns are strong candidates for indexing. Columns with one or more of the followi ng characteristics are good candidates for indexing:

• Values ar e unique in the column, or there are few duplicates.
• 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, a comparison that matches all the non-null values, such as:

  WHERE COL_X >= -9.99 *power(10,125)
  
  

  is preferable to

  WHERE COL_X IS NOT NULL

  Thi s 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 non-null values.
< /ul>

LONG and LONG RAW columns cannot be indexed.

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

Limit the Number of Indexes for Each Table

The more indexes, the more overhea d 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 mor e indexes; but, if a table is heavily updated, you might use fewer indexes.

Choose the Order of Columns in Composite Indexes

Although you can specify columns in any order in the CREATE INDEX< /code> command, the order of columns in the CREATE 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 column s), 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 4-1.

Figure 4-1 The VENDOR_P ARTS Table



Text description of the illustration adfns043.gif

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 * F
ROM 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);

Composite indexes speed up queries that use the leading portion of the index. So in this example, qu eries 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.

Gather Statistics to Make Index Usage More A ccurate

The database can use indexes more effectively when it has st atistical information about the tables involved in the queries. You can gather statistics when the indexes are created by including t he keywords COMPUTE STATISTICS in the CREATE INDEX statement. As data is updated and the distribution of va lues changes, you or the DBA can periodically refresh the statistics by calling procedures like DBMS_STATS.GATHER_TABLE_STATIST ICS and DBMS_STATS.GATHER_SCHEMA_STATISTICS.

Drop Indexes That Are No Longer Required

You might drop an index if:

• It do es not speed up queries. The table might be very small, or there might 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.

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 DROP INDEX to drop an index. For example, the following statement drops a specific named index:

DROP INDEX Emp_ename;

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 DROP ANY INDEX system privilege.

Privileges Required to Create an Index

When using indexes in an application, you might need to request that the DBA grant privileges or make changes to ini tialization 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 UNLIMITED TABLESPACE system privilege. To create an index in another user's s chema, you must have the CREATE ANY INDEX system privilege.

Creating Indexes: Basic Examples

You can create an index for a table to improve the performance of queries issu ed against the corresponding table. You can also create an index for a cluster. You can create a composite in dex 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 Database automatically creates an index to en force a UNIQUE or PRIMARY KEY integrity constraint. In general, it is better to create such co nstraints to enforce uniqueness, instead of using the obsolete CREATE UNIQUE INDEX syntax.

Use the SQL command CREATE INDEX to create an index.

In this example, an index is created for a single column, to speed up queries that test that column:

< pre class="CE">CREATE INDEX emp_ename ON emp_tab(ename);

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 col umns:

CREATE INDEX emp_ename ON emp_tab(ename, empno) COMPUTE STATISTICS;

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 fo r 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:

CREATE INDEX emp_upper_ename ON emp_tab(UPPER(ename)
) COMPUTE STATISTICS;

When to Use Domain Indexes

Do main indexes are appropriate for special-purpose applications implemented using data cartridges. The domain index helps to manipulate complex data, such as spatial, audio, or video data. If you need to develop such an application, see Oracle Data Cartridge Developer's Guide.

Oracle Database supplies a number of specialized data cartridges to help manage these kinds of complex data. So, if you n eed to create a search engine, or a geographic information system, you can do much of the work simply by creating the right kind of i ndex.

Whe n to Use Function-Based Indexes

A function-based index is an index b uilt on an expression. It extends your indexing capabilities beyond indexing on a column. A function-based index increases the variet y of ways in which you can access data.

See Also: Oracle Database Utilities for information on direct path load

No te: The index is more effe ctive if you gather statistics for the table or schema, using the procedures in the DBMS_STATS package. The index cannot contain any null values. Either make sure the appropriate columns contain no null values, or use the NVL function in the index expression to substitute some other value for nulls.

The expression indexed by a function-based index can be an arithmetic exp ression or an expression that contains a PL/SQL function, package function, C callout, or SQL function. Function-based indexes also s upport 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, i t 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 co st-based and rule-based optimization, function-based indexes can be used by only by cost-based optimization. Other restrictions on fu nction-based indexes are described in "Restrictions for Function-Based Indexes".

See Also: Oracle Database Concepts < /a>Oracle Database Administrator's Guide for information on creating function-based indexes

Advantages of Function-Based Indexes

Function-based indexes:

• Increase the number of situations where the optimizer can perform a range scan instead of a full table scan. For example, consider the expression in this WHERE clause:

  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_a + column_b). Range scans typically produce fast response times if the predicate selects less t han 15% of the rows of a large table. The optimizer can estimate how many rows are selected by expressions more accurately if the exp ressions 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.)

• Precompute the value of a computationally intensive function and store it in the index. An index can store computationally in tensive expression that you access often. When you need to access a value, it is already computed, greatly improving query execution performance.
• Create indexes on object columns and REF colum ns. Methods that describe objects can be used as functions on which to build indexes. For example, you can use the MAP method to build indexes on an object type column.
• Creat e more powerful sorts. You can perform case-insensitive sorts with the UPPER and LOWER functions, desc ending order sorts with the DESC keyword, and linguistic-based sorts with the NLSSORT function.

  Note: Oracle Database sorts columns with the DESC keyword in descending order. Such indexes are treated as function- based indexes. Descending indexes cannot be bitmapped or reverse, and cannot be used in bitmapped optimizations. To get the DES C functionality prior to Oracle Database version 8, remove the DESC keyword from the CREATE IN DEX statement.

Another function-based index c alls 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));
<
a name="1006432">
SELECT * FROM Weatherdata_tab 
WHERE (Distance_from_equator (Reg_Ob
j)) > '1000';

Another index stores the temperature delta and t he maximum temperature. The result of the delta is sorted in descending order. A query could use this index to quickly find table row s where the temperature delta is less than 20 and the maximum temperature is greater than 75.

<
/a>CREATE INDEX compare_index 
ON Weatherdata_tab ((Maxtemp - Mintemp) DESC, Maxtemp);

<
a name="1006440">SELECT * FROM Weatherdata_tab
WHERE ((Maxtemp - Mintemp) < '20' AND Maxtemp > '75');

Examp les of Function-Based Indexes

Example: Function-Based Index for Case-Insensitive Searches

The following command allows faster case-insensitive searches in table EMP_TAB.

CREATE INDEX Idx ON Emp_tab (UPPER(Ename));

The SELECT command uses the function-based index on UPPER(e_name) to return a ll of the employees with name like :KEYCOL.

SELECT * FROM Emp_tab WHERE UPPER(
Ename) like :KEYCOL;

Example: Precomputing Arithmetic Expressions with a Function-Based Index

The following command computes a value for each row using columns A, B, and C, and stores the results in the inde x.

CREATE INDEX Idx ON Fbi_tab (A + B * (C - 1), A, B);

The 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).

< pre class="CE">SELECT a FROM Fbi_tab WHERE A + B * (C - 1) < 100;

Example: Function-Based Index for Language-Depen dent Sorting

This example demonstrates how a function-based index ca n 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 GERMAN.

CREATE INDEX Nls_index 
    ON Nls_tab (NLSSORT(Name, 'NLS_SORT = German'));

The SELECT statement selects all of the contents of the table and orders it by NAME. The rows are ordered u sing 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 German and NLS_COMP is set to ANSI.

SELECT * FROM Nls_tab WHERE Name IS NOT NULL
    ORDER BY Name;

< a name="1006464">

Restrictions fo r Function-Based Indexes

Note the following restrictions for functio n-based indexes:

• Only cost-based optimization can use function -based indexes. Remember to call DBMS_STATS.GATHER_TABLE_STATISTICS or DBMS_STATS.GATHER_SCHEMA_STATISTICS, for the function-based index to be effective.
• Any top-level or package-level PL/SQL functions that are used in the index expression must be declared as DETERMINISTIC. That is, they always return th e same result given the same input, like the UPPER function. You must ensure that the subprogram really is deterministic , because Oracle Database does not check that the assertion is true.

  The following sema ntic rules demonstrate how to use the keyword DETERMINISTIC:

  • A top level subprogram can be declared as DETERMINISTIC.
  • A PACKAGE level subprogram can be declared as DETERMINISTIC in the PACKAGE specification b ut not in the PACKAGE BODY. Errors are raised if DETERMINISTIC is used inside a PACKAGE< /code> BODY.
  • A private subprogram (declared inside another subpro gram or a PACKAGE BODY) cannot be declared as DETERMINISTIC.
  • A DETERMINISTIC subprogram can call another subprogram whether the called program is declared as < code>DETERMINISTIC or not.
• Expressions used in a function-bas ed index cannot contain any aggregate functions. The expressions should reference only columns in a row in the table.
• You must analyze the table or index before the index is used.
• Bitmap optimizations cannot use descending indexes.
• Fu nction-based indexes are not used when OR-expansion is done.
• The index functio n cannot be marked NOT NULL. To avoid a full table scan, you must ensure that the query cannot fetch null values.
< li class="LB1" type="disc">Function-based indexes cannot use expressions that return VARCHAR2 or < code>RAW data types of unknown length from PL/SQL functions. A workaround is to limit the size of the function's output by ind exing a substring of known length:

-- INITIALS() might return 1 letter, 2 letters, 3 letters,
 and so on.
-- We limit the return value to 10 characters for purposes of the index.
CREA
TE 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;