5 Using Indexes in Database Applications

Which Come First, Data or 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.

Create a New Temporary Table Space Before Creating Indexes

When you create an index on a table that already has data, Oracle Database must use sort space to create the index. The database 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 the database must also swap sort information to and from temporary segments allocated on behalf of the index creation. If the index is extremely large, it can be beneficial to complete the following steps:

1. Create a new temporary tablespace using the CREATE TABLESPACE statement.

2. Use the TEMPORARY TABLESPACE option of the ALTER USER statement to make this your new temporary tablespace.

3. Create the index using the CREATE INDEX statement.

4. Drop this tablespace using the DROP TABLESPACE statement. Then use the ALTER USER statement 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.

Index the Correct Tables and Columns

Use the following guidelines for determining when to create an index:

• Create an index if you frequently want to retrieve less than about 15% of the rows in a large table. This threshold percentage varies greatly, however, according 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 the percentage; the more clustered the row data, the higher the percentage.

• Index columns that are used for joins to improve join performance.

• Primary and unique keys automatically have indexes, but you might want to create an index on a foreign key; see Chapter 6, "Maintaining Data Integrity in Database Applications" 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 following characteristics are good candidates for indexing:

• Values are 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
  

  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 non-null values.

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 available space in the data block. Consult with the database administrator for assistance in determining the space required by an index.

Limit the Number of Indexes for Each Table

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.

Choose Column Order in Composite Indexes

Although you can specify columns in any order in the CREATE INDEX statement, the order of columns in the CREATE INDEX statement can affect query performance. In general, 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 Example 5-1.

VEND ID     PART NO     UNIT COST
-------     -------     ---------
1012        10-440       .25
1012        10-441      .39
1012           457      4.95
1010        10-440       .27
1010           457      5.10
1220         8-300      1.33
1012         8-300      1.19
1292           457      5.28

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 * FROM 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, the performance of queries with WHERE clauses using only the PART_NO column improve also. Because there are only five distinct values, placing a separate index on VENDOR_ID serves no purpose.

Gather Index Statistics

The database can use indexes more effectively when it has statistical information about the tables involved in the queries. You or the DBA can periodically gather statistics by invoking procedures such as DBMS_STATS.GATHER_TABLE_STATISTICS and DBMS_STATS.GATHER_SCHEMA_STATISTICS. For information about these procedures, see Oracle Database PL/SQL Packages and Types Reference.

Drop Unused Indexes

You might drop an index if:

• It does 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.

To find out if an index is being used, you can monitor it. If you see that the index is never used, rarely used, or used in a way that seems to provide no benefit, you can either drop it immediately or you can make it invisible until you are sure that you do not need it, and then drop it. If you discover that you do need the invisible index, you can make it visible again.

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.

To drop an index, use the SQL statement DROP INDEX. For example, the following statement drops the index named Emp_name:

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.

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

• Precompute the value of a computationally intensive function and store it in the index.

  An index can store computationally intensive 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 columns.

  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.

• Create more powerful sorts.

  You can perform case-insensitive sorts with the UPPER and LOWER functions, descending order sorts with the DESC keyword, and linguistic-based sorts with the NLSSORT function.

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 can 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 can 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');

Restrictions on Function-Based Indexes

Function-based indexes have the following restrictions:

• Only cost-based optimization can use function-based indexes. Remember to invoke 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 the same result given the same input, for example, 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 semantic rules demonstrate how to use the keyword DETERMINISTIC:

  • You can declare a top level subprogram as DETERMINISTIC.

  • You can declare a PACKAGE level subprogram as DETERMINISTIC in the PACKAGE specification but not in the PACKAGE BODY. Errors are raised if DETERMINISTIC is used inside a PACKAGE BODY.

  • You can declare a private subprogram (declared inside another subprogram or a PACKAGE BODY) as DETERMINISTIC.

  • A DETERMINISTIC subprogram can invoke another subprogram whether the invoked subprogram is declared as DETERMINISTIC or not.

• If you change the semantics of a DETERMINISTIC function and recompile it, then existing function-based indexes and materialized views report results for the prior version of the function. Thus, if you change the semantics of a function, you must manually rebuild any dependent function-based indexes and materialized views.

• Expressions in a function-based index cannot contain any aggregate functions. The expressions must 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.

• Function-based indexes are not used when OR-expansion is done.

• The index function cannot be marked NOT NULL. To avoid a full table scan, you must ensure that the query cannot fetch null values.

• Function-based indexes cannot use expressions that return VARCHAR2 or RAW data 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:

  -- 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.
  CREATE INDEX func_substr_index ON
    emp_tab(substr(initials(ename),1,10);
  
  -- Invoke SUBSTR both when creating the index and when referencing
  -- the function in queries.
  SELECT SUBSTR(initials(ename),1,10) FROM emp_tab;
  

Examples of Function-Based Indexes

• Function-Based Index for Case-Insensitive Searches

• Precomputing Arithmetic Expressions with a Function-Based Index

• Function-Based Index for Language-Dependent Sorting

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

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

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

SELECT a FROM Fbi_tab WHERE A + B * (C - 1) < 100;

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

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