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

Index Types

Oracle Database provides several indexing schemes that provide complementary performance functionality. These are:

• B-tree indexes: the default and the most common.
• B-tree cluster indexes: defined specifically for cluster.
• Hash cluster indexes: defined specifically for a hash cluster.
• Global and local indexes: relate to partitioned tables and indexes.
• Reverse key indexes: most useful for Oracle Real Application Clusters applications.
• Bitmap indexes: compact; work best for columns with a small set of values
• Function-based indexes: contain the precomputed value of a function/expression.
• Domain indexes: specific to an application or cartridge.

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.

Guidelines for Managing Indexes

Here are some guidelines from creating efficient indexes:

• Index if you need to access no more than 10-15% of the data in the table. A full table scan (read all the table) is better if you intend to retrieve a high percentage of the table data, this is because a index read requires two reads
• Do not use indexes on small tables, a full table scan would be fine.
• Create primary keys for all tables as a index will be created by default.
• Index the columns that are involved in multi-table join operations
• Index columns that are used frequently in where clauses.
• Index columns that are involved in order by, group by, union and distinct operations.
• Columns that have long character strings are bad for indexing.
• Columns that are frequently update are bad for indexing.
• Choose tables where few rows have similar values.
• Keep the number of indexes small, to many will cause performance problems on inserting data.

Creating Indexes

This section describes how to create indexes. To create an index in your own schema, at least one of the following conditions must be true:

1. The table or cluster to be indexed is in your own schema.
2. You have INDEX privilege on the table to be indexed.
3. You have CREATE ANY INDEX system privilege.

Creating an Index Explicitly

You can create indexes explicitly (outside of integrity constraints) using the SQL statement CREATE INDEX. The following statement creates an index named emp_ename for the ename column of the emp table:

CREATE INDEX emp_ename ON emp(ename)
      TABLESPACE users
      STORAGE (INITIAL 20K
      NEXT 20k
      PCTINCREASE 75);

Creating a Unique Index Explicitly

Indexes can be unique or non-unique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns). Non-unique 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;

Creating an Index Associated with a Constraint

Oracle Database enforces a UNIQUE key or PRIMARY KEY integrity constraint on a table by creating a unique index on the unique key or primary key. This index is automatically created by the database when the constraint is enabled. No action is required by you when you issue the CREATE TABLE or ALTER TABLE statement to create the index, but you can optionally specify a USING INDEX clause to exercise control over its creation. This includes both when a constraint is defined and enabled, and when a defined but disabled constraint is enabled.

To enable a UNIQUE or PRIMARY KEY constraint, thus creating an associated index, the owner of the table must have a quota for the tablespace intended to contain the index, or the UNLIMITED TABLESPACE system privilege. The index associated with a constraint always takes the name of the constraint, unless you optionally specify otherwise.

Specifying Storage Options for an Index Associated with a Constraint

You can set the storage options for the indexes associated with UNIQUE and PRIMARY KEY constraints using the USING INDEX clause. The following CREATE TABLE statement enables a PRIMARY KEY constraint and specifies the storage options of the associated index:

   CREATE TABLE emp (
     empno NUMBER(5) PRIMARY KEY, age INTEGER)
     ENABLE PRIMARY KEY USING INDEX
     TABLESPACE users;

Collecting Incidental Statistics when Creating an Index

Oracle Database provides you with the opportunity to collect statistics at very little resource cost during the creation or rebuilding of an index. These statistics are stored in the data dictionary for ongoing use by the optimizer in choosing a plan for the execution of SQL statements. The following statement computes index, table, and column statistics while building index emp_ename on column ename of table emp:

CREATE INDEX emp_ename ON emp(ename)
     COMPUTE STATISTICS;

Creating an Index Online

You can create and rebuild indexes online. This enables you to update base tables at the same time you are building or rebuilding indexes on that table. You can perform DML operations while the index build is taking place, but DDL operations are not allowed. Parallel execution is not supported when creating or rebuilding an index online.

The following statements illustrate online index build operations:

CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;

Creating a Function-Based Index

Function-based indexes facilitate queries that qualify a value returned by a function or expression. The value of the function or expression is precomputed and stored in the index.

To illustrate a function-based index, consider the following statement that defines a function-based index (area_index) defined on the function area(geo):

CREATE INDEX area_index ON rivers (area(geo));

In the following SQL statement, when area(geo) is referenced in the WHERE clause, the optimizer considers using the index area_index.

SELECT id, geo, area(geo), desc
     FROM rivers     
     WHERE Area(geo) >5000;

Table owners should have EXECUTE privileges on the functions used in function-based indexes.

Creating a Key-Compressed Index

Creating an index using key compression enables you to eliminate repeated occurrences of key column prefix values.

CREATE INDEX  emp_ename ON emp(ename)
   TABLESPACE users
   COMPRESS 1;

The COMPRESS clause can also be specified during rebuild. For example, during rebuild you can disable compression as follows:

ALTER INDEX emp_ename REBUILD NOCOMPRESS;

Altering Indexes

To alter an index, your schema must contain the index or you must have the ALTER ANY INDEX system privilege.

Altering Storage Characteristics of an Index

ALTER INDEX emp_ename
     STORAGE (PCTINCREASE 50);

ALTER TABLE emp
     ENABLE PRIMARY KEY USING INDEX;

Rebuilding an Existing Index

ALTER INDEX emp_name REBUILD;

Dropping Indexes

To drop an index, the index must be contained in your schema, or you must have the DROP ANY INDEX system privilege.

DROP INDEX emp_ename;