Index Selection and Creation Strategy - Managing Indexes

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.

In this post I will sharing few guidelines to be considered while creating indexes and 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. 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.

Creating Large Index: Switch Your Temporary Tablespace to Avoid Space Problems Creating Indexes

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:

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 your new temporary tablespace.
3. Create the index using the CREATE INDEX command.
4. Drop this tablespace using the DROP TABLESPACE command. Then use the ALTER USER command 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 15% of the rows in a large table. The percentage varies greatly 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 used for joins to improve performance on joins of multiple tables.
• 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.

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

• 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, 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 NUL
  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.

Order of Index Columns in Composite Index for Performance

Although you can specify columns in any order in the CREATE INDEX 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 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 below.

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

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.

Gather Statistics to Make Index Usage More Accurate

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 likeDBMS_STATS.GATHER_TABLE_STATISTICS and DBMS_STATS.GATHER_SCHEMA_STATISTICS.

Drop Indexes That Are No Longer Required

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

Specify the Tablespace for Each Index

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

Consider Parallelizing Index Creation
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.

Consider Creating Indexes with NOLOGGING
You can create an index and generate minimal redo log records by specifying NOLOGGING in the CREATE INDEX statement.
Note:
Because indexes created using NOLOGGING are 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 withoutLOGGING 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.

Viewing Index Information

The following views display information about indexes:

View	Description
DBA_INDEXES ALL_INDEXES USER_INDEXES	DBA view describes indexes on all tables in the database. ALL view describes indexes on all tables accessible to the user. USER view is restricted to indexes owned by the user. Some columns in these views contain statistics that are generated by the DBMS_STATSpackage or ANALYZE statement.
DBA_IND_COLUMNS ALL_IND_COLUMNS USER_IND_COLUMNS	These views describe the columns of indexes on tables. Some columns in these views contain statistics that are generated by theDBMS_STATS package or ANALYZE statement.
DBA_IND_EXPRESSIONS ALL_IND_EXPRESSIONS USER_IND_EXPRESSIONS	These views describe the expressions of function-based indexes on tables.
DBA_IND_STATISTICS ALL_IND_STATISTICS USER_IND_STATISTICS	These views contain optimizer statistics for indexes.
INDEX_STATS	Stores information from the last ANALYZE INDEX...VALIDATE STRUCTURE statement.
INDEX_HISTOGRAM	Stores information from the last ANALYZE INDEX...VALIDATE STRUCTURE statement.
V$OBJECT_USAGE	Contains index usage information produced by the ALTER INDEX...MONITORING USAGE functionality.

http://docs.oracle.com/cd/A97630_01/appdev.920/a96590/adg06idx.htm

Exploring Indexes Concepts in Oracle

About Indexes

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

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.

The absence or presence of an index does not require a change in the wording of any SQL statement. An index is merely a fast access path to the data. It affects only the speed of execution. Given a data value that has been indexed, the index points directly to the location of the rows containing that value.

You can create many indexes for a table as long as the combination of columns differs for each index. You can create more than one index using the same columns if you specify distinctly different combinations of the columns. For example, the following statements specify valid combinations:

CREATE INDEX employees_idx1 ON employees (last_name, job_id);

CREATE INDEX employees_idx2 ON employees (job_id, last_name);

If you create index on a column combination which has already been indexed, you will get following errors:

SQL> create index emp_pkk on emp(empno);

create index emp_pkk on emp(empno)

                            *

ERROR at line 1:

ORA-01408: such column list already indexed

Oracle Database automatically maintains and uses indexes after they are created. Oracle Database automatically reflects changes to data, such as adding new rows, updating rows, or deleting rows, in all relevant indexes with no additional action by users.

Retrieval performance of indexed data remains almost constant, even as new rows are inserted. However, the presence of many indexes on a table decreases the performance of updates, deletes, and inserts, because Oracle Database must also update the indexes associated with the table.

The optimizer can use an existing index to build another index. This results in a much faster index build.

Classification of Indexes:

On bases of Logical design Indexes are classified as:

■ Unique and Nonunique Indexes

■ Visible and Invisible Indexes

■  Single Column or Composite Indexes

■ Function-Based Indexes

On bases of Physical Implementation Indexes are classified as:

■ B-Tree: Normal or Reverse Key

■ Bitmap Indexes

■ Bitmap Join Indexes
■ Partition or Non Partitioned

Single Column or Composite Indexes

Index could be created on a single column or on multiple columns. 

A composite index (also called a concatenated index) is an index that you create on multiple columns in a table. Columns in a composite index can appear in any order and need not be adjacent in the table.

Composite indexes can speed retrieval of data for SELECT statements in which the WHERE clause references all or the leading portion of the columns in the composite index. Therefore, the order of the columns used in the definition is important.

Generally, the most commonly accessed or most selective columns go first.

No more than 32 columns can form a regular composite index. For a bitmap index, the maximum number columns is 30. A key value cannot exceed roughly half (minus some overhead) the available data space in a data block.

Unique and Non-unique Indexes

Indexes can be unique or nonunique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns). Nonunique indexes do not impose this restriction on the column values. 

Use the CREATE UNIQUE INDEX statement to create a unique index. The following example creates a unique index:

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. However, it is advisable that any index that exists for query performance, including unique indexes, be created explicitly.

Creating unique indexes through a primary key or unique constraint is not guaranteed to create a new index, and the index they create is not guaranteed to be a unique index.

You cannot create Unique index on column which has duplicate records:

Table: EMP
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
      7566 JONES      MANAGER         7839 02-APR-81       2975
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850
      7782 CLARK      MANAGER         7839 09-JUN-81       2450
      7788 SCOTT      ANALYST         7566 19-APR-87       3000
      7839 KING       PRESIDENT            17-NOV-81       5000
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
      7876 ADAMS      CLERK           7788 23-MAY-87       1100

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950
      7902 FORD       ANALYST         7566 03-DEC-81       3000
      7934 MILLER     CLERK           7782 23-JAN-82       1300

14 rows selected.


SQL> CREATE UNIQUE INDEX EMP_D_i ON EMP(DEPTNO);
CREATE UNIQUE INDEX EMP_D_i ON EMP(DEPTNO)
                               *
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

Indexes and Nulls

NULL values in indexes are considered to be distinct except when all the non-NULL values in two or more rows of an index are identical, in which case the rows are considered to be identical. Therefore, UNIQUE indexes prevent rows containing NULL values from being treated as identical. This does not apply if there are no non-NULL values—in other words, if the rows are entirely NULL. Oracle Database does not index table rows in which all key columns are NULL, except in the case of bitmap indexes or when the cluster key column value is NULL.

Unique Indexes doesn't identify NULL values. So even if a column has all or few NULL values we are still able to create Unique index on it.

SQL> CREATE UNIQUE INDEX EMP_D_i ON EMP(comm);
Index created.
SQL> CREATE UNIQUE INDEX EMP_D_i ON EMP(deptno);
Index created.

Function-Based Indexes

You can create indexes on functions and expressions that involve one or more columns in the table being indexed. A function-based index computes the value of the function or expression and stores it in the index. You can create a function-based index as either a B-tree or a bitmap index.

The expression cannot contain any aggregate functions, and it must be DETERMINISTIC. For building an index on a column containing an object type, the function can be a method of that object, such as a map method. However, you cannot build a function-based index on a LOB column, REF, or nested table column, nor can you build a function-based index if the object type contains a LOB, REF, or nested table.

Uses of Function-Based Indexes
Function-based indexes provide an efficient mechanism for evaluating statements that contain functions in their WHERE clauses. The value of the expression is computed and
stored in the index. When it processes INSERT and UPDATE statements, however, Oracle Database must still evaluate the function to process the statement.

For example, if you create the following index:
CREATE INDEX idx ON table_1 (a + b * (c - 1), a, b);
Oracle Database can use it when processing queries such as this:
SELECT a FROM table_1 WHERE a + b * (c - 1) < 100;

Function-based indexes defined on UPPER(column_name) or LOWER(column_name) can facilitate case-insensitive searches. For example, the following index: 
CREATE INDEX uppercase_idx ON employees (UPPER(first_name));
can facilitate processing queries such as this:

SELECT * FROM employees WHERE UPPER(first_name) = 'RICHARD';

Bitmap Indexes

The purpose of an index is to provide pointers to the rows in a table that contain a given key value. In a regular index, this is achieved by storing a list of rowids for each key corresponding to the rows with that key value. Oracle Database stores each key value repeatedly with each stored rowid. In a bitmap index, a bitmap for each key value is used instead of a list of rowids.
Each bit in the bitmap corresponds to a possible rowid. If the bit is set, then it means that the row with the corresponding rowid contains the key value. A mapping function converts the bit position to an actual rowid, so the bitmap index provides the same functionality as a regular index even though it uses a different representation internally. If the number of different key values is small, then bitmap indexes are very space efficient.

In a bitmap index, the database stores a bitmap for each index key. In a conventional B-tree index, one index entry points to a single row. In a bitmap index, each index key stores pointers to multiple rows.

Bitmap indexes are primarily designed for data warehousing or environments in which queries reference many columns in an ad hoc fashion. Situations that may call for a bitmap index include:

• The indexed columns have low cardinality, that is, the number of distinct values is small compared to the number of table rows.
• The indexed table is either read-only or not subject to significant modification by DML statements.

Bitmap Indexes on a Single Table

Example 2 Query of customers Table

SQL> SELECT cust_id, cust_last_name, cust_marital_status, cust_gender
  2  FROM   sh.customers 
  3  WHERE  ROWNUM < 8 ORDER BY cust_id;
 
   CUST_ID CUST_LAST_ CUST_MAR C
---------- ---------- -------- -
         1 Kessel              M
         2 Koch                F
         3 Emmerson            M
         4 Hardy               M
         5 Gowen               M
         6 Charles    single   F
         7 Ingram     single   F
 
7 rows selected.

The cust_marital_status and cust_gender columns have low cardinality, whereas cust_id and cust_last_name do not. Thus, bitmap indexes may be appropriate on cust_marital_status and cust_gender. A bitmap index is probably not useful for the other columns. Instead, a unique B-tree index on these columns would likely provide the most efficient representation and retrieval.

Table 1-2 illustrates the bitmap index for the cust_gender column output shown in Example 2. It consists of two separate bitmaps, one for each gender.

Table 1-2 Sample Bitmap

Value	Row 1	Row 2	Row 3	Row 4	Row 5	Row 6	Row 7
M	1	0	1	1	1	0	0
F	0	1	0	0	0	1	1

A mapping function converts each bit in the bitmap to a rowid of the customers table. Each bit value depends on the values of the corresponding row in the table. For example, the bitmap for the M value contains a 1 as its first bit because the gender is M in the first row of the customers table. The bitmapcust_gender='M' has a 0 for its the bits in rows 2, 6, and 7 because these rows do not contain M as their value.

DBA_INDEX, ALL_INDEXES, USER_INDEXES Data Dictionary Tables:

We use these data dictionary tables to know about created indexes:

Column	Datatype	NULL	Description
OWNER	VARCHAR2(30)	NOT NULL	Owner of the index
INDEX_NAME	VARCHAR2(30)	NOT NULL	Name of the index
INDEX_TYPE	VARCHAR2(27)		Type of the index: NORMAL BITMAP FUNCTION-BASED NORMAL FUNCTION-BASED BITMAP DOMAIN
TABLE_OWNER	VARCHAR2(30)	NOT NULL	Owner of the indexed object
TABLE_NAME	VARCHAR2(30)	NOT NULL	Name of the indexed object
TABLE_TYPE	CHAR(5)		Type of the indexed object (for example, TABLE, CLUSTER)
UNIQUENESS	VARCHAR2(9)		Indicates whether the index is UNIQUE or NONUNIQUE
COMPRESSION	VARCHAR2(8)		Indicates whether index compression is enabled (ENABLED) or not (DISABLED)

Using GOTO and NULL statement

The GOTO statement branches to a label unconditionally. The label must be unique within its scope and must precede an executable statement or a PL/SQL block. When executed, the GOTO statement transfers control to the labeled statement or block. In the following example, you go to an executable statement farther down in a sequence of statements:

BEGIN
...
GOTO insert_row;
...
<>
INSERT INTO emp VALUES ...
END;
In the next example, you go to a PL/SQL block farther up in a sequence of statements:
DECLARE
x NUMBER := 0;
BEGIN
<>
BEGIN
x := x + 1;
END;
IF x < 10 THEN
GOTO increment_x;
END IF;
END;

The label end_loop in the following example is not allowed because it does not precede an executable statement:

DECLARE
done BOOLEAN;
BEGIN
FOR i IN 1..50 LOOP
IF done THEN
GOTO end_loop;
END IF;
<> -- not allowed
END LOOP; -- not an executable statement
END;
To correct the previous example, add the NULL statement::
FOR i IN 1..50 LOOP
IF done THEN

GOTO end_loop;
END IF;
...
<>
NULL; -- an executable statement
END LOOP;

As the following example shows, a GOTO statement can branch to an enclosing block from the current block:

DECLARE
my_ename CHAR(10);
BEGIN
<>
SELECT ename INTO my_ename FROM emp WHERE ...
BEGIN
GOTO get_name; -- branch to enclosing block
END;
END;

The GOTO statement branches to the first enclosing block in which the referenced label

appears.

Restrictions on the GOTO Statement

Some possible destinations of a GOTO statement are not allowed. Specifically, a GOTO statement cannot branch into an IF statement, CASE statement, LOOP statement, or

sub-block. For example, the following GOTO statement is not allowed:

BEGIN

GOTO update_row; -- can't branch into IF statement

IF valid THEN

<>

UPDATE emp SET ...

END IF;

END;

As the example below shows, a GOTO statement cannot branch from one IF statement clause to another. Likewise, a GOTO statement cannot branch from one CASE statement WHEN clause to another.

BEGIN
   ...
   IF valid THEN
      ...
      GOTO update_row;  -- can't branch into ELSE clause
   ELSE
      ...
      <>
      UPDATE emp SET ...
   END IF;
END;

The next example shows that a GOTO statement cannot branch from an enclosing block into a sub-block:

BEGIN
   ...
   IF status = 'OBSOLETE' THEN
      GOTO delete_part;  -- can't branch into sub-block
   END IF;
   ...
   BEGIN
      ...
      <>
      DELETE FROM parts WHERE ...
   END;
END;

Also, a GOTO statement cannot branch out of a subprogram, as the following example shows:

DECLARE
   ...
   PROCEDURE compute_bonus (emp_id NUMBER) IS
   BEGIN
      ...
      GOTO update_row;  -- can't branch out of subprogram
   END;
BEGIN
   ...
   <>
   UPDATE emp SET ...
END;

Finally, a GOTO statement cannot branch from an exception handler into the current block. For example, the following GOTO statement is not allowed:

DECLARE
   ...
   pe_ratio  REAL;
BEGIN
   ...
   SELECT price / NVL(earnings, 0) INTO pe_ratio FROM ...
   <>
   INSERT INTO stats VALUES (pe_ratio, ...);
EXCEPTION
   WHEN ZERO_DIVIDE THEN
      pe_ratio := 0;
      GOTO insert_row;  -- can't branch into current block
END;

However, a GOTO statement can branch from an exception handler into an enclosing block.

NULL Statement

The NULL statement does nothing other than pass control to the next statement. In a conditional construct, the NULL statement tells readers that a possibility has been considered, but no action is necessary. In the following example, the NULL statement shows that no action is taken for unnamed exceptions:

EXCEPTION
   WHEN ZERO_DIVIDE THEN
      ROLLBACK;
   WHEN VALUE_ERROR THEN
      INSERT INTO errors VALUES ...
      COMMIT;
   WHEN OTHERS THEN
      NULL;
END;

In IF statements or other places that require at least one executable statement, the NULL statement to satisfy the syntax. In the following example, the NULL statement emphasizes that only top-rated employees get bonuses:

IF rating > 90 THEN
   compute_bonus(emp_id);
ELSE
   NULL;
END IF;

Also, the NULL statement is a handy way to create stubs when designing applications from the top down. A stub is dummy subprogram that lets you defer the definition of a procedure or function until you test and debug the main program. In the following example, the NULL statement meets the requirement that at least one statement must appear in the executable part of a subprogram:

PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS
BEGIN
   NULL;
END debit_account;