Using Indexes in Database Applications

Indexes are optional structures, associated with tables and clusters, which allow SQL queries to execute more quickly. Just as the index in this guide 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.

See Also:

Topics:

Guidelines for Managing Indexes

The summary of guidelines for managing the indexes are as follows:

See Also: Oracle Database Administrator’s Guide

Managing Indexes

Oracle Database Administrator’s Guide has this information about managing indexes:

See Also: Creating Indexes for Use with Constraints

When to Use Domain Indexes

A domain index (also called an application domain index) is a customized index specific to an application that was implemented using a data cartridge (for example, a search engine or geographic information system).

See Also:

When to Use Function-Based Indexes

A function-based index computes the value of an expression that involves one or more columns and stores it in the index. The index expression can be an arithmetic expression or an expression that contains a SQL function, PL/SQL function, package function, or C callout. Function-based indexes also support linguistic sorts based on collation keys, efficient linguistic collation of SQL statements, and case-insensitive sorts.

A function-based index improves the performance of queries that use the index expression (especially if the expression computation is intensive). However:

The optimizer can use function-based indexes only for cost-based optimization, while it can use indexes on columns for both cost-based and rule-based optimization.

Note:

Topics:

See Also:

Advantages of Function-Based Indexes

A function-based index has these advantages:

See Also:

Disadvantages of Function-Based Indexes

A function-based index has these disadvantages:

CREATE OR REPLACE FUNCTION initials (
  name  IN VARCHAR2
) RETURN VARCHAR2
  DETERMINISTIC
IS
BEGIN
  RETURN('A. J.');
END;
/

/* Invoke SUBSTR both when creating index and when referencing
   function in queries. */

CREATE INDEX func_substr_index ON
EMPLOYEES(SUBSTR(initials(FIRST_NAME),1,10));

SELECT SUBSTR(initials(FIRST_NAME),1,10) FROM EMPLOYEES;

See Also:

Example: Function-Based Index for Precomputing Arithmetic Expression

You can create composite indexes to computer arithmetic expressions. Example 12-1 creates a table with columns a, b, and c; creates an index on the table, and then queries the table. The index is a composite index on three columns: a virtual column that represents the expression a+b*(c-1), column a, and column b. The query uses the indexed expression in its WHERE clause; therefore, it can use an index range scan instead of a full index scan.

Example 12-1 Function-Based Index for Precomputing Arithmetic Expression

Create table on which to create index:

DROP TABLE Fbi_tab;
CREATE TABLE Fbi_tab (
  a INTEGER,
  b INTEGER,
  c INTEGER
);

Create index:

CREATE INDEX Idx ON Fbi_tab (a+b*(c-1), a, b);

This query can use an index range scan instead of a full index scan:

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

Note: This example uses composite indexes (indexes on multiple table columns).

See Also:

Example: Function-Based Indexes on Object Column

In Example 12-2, assume that the object type Reg_obj has been defined, and that it stores information about a city. The example creates a table whose first column has type Reg_obj, a deterministic function with a parameter of type Reg_obj, and two function-based indexes that invoke the function. The first query uses the first index to quickly find cities further than 1000 miles from the equator. The second query uses the second index (which is composite) to quickly find cities where the temperature delta is less than 20 and the maximum temperature is greater than 75. (The table is not populated for the example, so the queries return no rows.)

Example 12-2 Function-Based Indexes on Object Column

Create table with object column:

DROP TABLE Weatherdata_tab;
CREATE TABLE Weatherdata_tab (
  Reg_obj INTEGER,
  Maxtemp INTEGER,
  Mintemp INTEGER
);

Create deterministic function with parameter of type Reg_obj:

CREATE OR REPLACE FUNCTION Distance_from_equator (
  Reg_obj IN INTEGER
) RETURN INTEGER
  DETERMINISTIC
IS
BEGIN
  RETURN(3000);
END;
/

Create first function-based index:

CREATE INDEX Distance_index
ON Weatherdata_tab (Distance_from_equator (Reg_obj));

Use index expression in query:

**SELECT * FROM Weatherdata_tab**
**WHERE (Distance_from_equator (Reg_Obj)) > '1000';**

Result:

no rows selected

Create second function-based (and composite) index:

CREATE INDEX Compare_index
ON Weatherdata_tab ((Maxtemp - Mintemp) DESC, Maxtemp);

Use index expression and indexed column in query:

SELECT * FROM Weatherdata_tab
WHERE ((Maxtemp - Mintemp) < '20' AND Maxtemp > '75');

Result:

no rows selected

Example: Function-Based Index for Faster Case-Insensitive Searches

Example 12-3 creates an index that allows faster case-insensitive searches in the EMPLOYEES table and then uses the index expression in a query.

Example 12-3 Function-Based Index for Faster Case-Insensitive Searches

Create index:

CREATE INDEX emp_lastname ON EMPLOYEES (UPPER(LAST_NAME));

Use index expression in query:

SELECT first_name, last_name
FROM EMPLOYEES
WHERE UPPER(LAST_NAME) LIKE 'J%S_N';

Result:

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Charles              Johnson

1 row selected.

Example: Function-Based Index for Language-Dependent Sorting

You can use the NLSSORT API for language-dependent sorting. Example 12-4 creates a table with one column, NAME, and a function-based index to sort that column using the collation sequence GERMAN, and then selects all columns of the table, ordering them by NAME. Because the query can use the index, the query is faster. (Assume that the query is run in a German session, where NLS_SORT is GERMAN and NLS_COMP is ANSI. Otherwise, the query would have to specify the values of these Globalization Support parameters.)

Example 12-4 Function-Based Index for Language-Dependent Sorting

Create table on which to create index:

DROP TABLE nls_tab;
CREATE TABLE nls_tab (NAME VARCHAR2(80));

Create index:

CREATE INDEX nls_index
  ON nls_tab (NLSSORT(NAME, 'NLS_SORT = GERMAN'));

Select all table columns, ordered by NAME:

SELECT * FROM nls_tab
WHERE NAME IS NOT NULL
ORDER BY NAME;