Managing Indexes Part #3

After Managing Indexes Part #2

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 PCTFREE 0;

Specifying the Index Associated with a Constraint

If you require more explicit control over the indexes associated with UNIQUE and PRIMARY KEY constraints, the database lets you:

1. Specify an existing index that the database is to use to enforce the constraint
2. Specify a CREATE INDEX statement that the database is to use to create the index and enforce the constraint
   

These options are specified using the USING INDEX clause. The following statements present some examples.

Example 1:

CREATE TABLE a (a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));


Example 2:

CREATE TABLE b( b1 INT, b2 INT,CONSTRAINT bu1 UNIQUE (b1, b2)
USING INDEX (create unique index bi on b(b1, b2)), CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi);

Example 3:

CREATE TABLE c(c1 INT, c2 INT);
CREATE INDEX ci ON c (c1, c2);
ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;


If a single statement creates an index with one constraint and also uses that index for another constraint, the system will attempt to rearrange the clauses to create the index before reusing it.


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 create a function-based index, you must have the COMPATIBLE parameter set to 8.1.0.0.0 or higher. In addition to the prerequisites for creating a conventional index, if the index is based on user-defined functions, then those functions must be marked DETERMINISTIC. Also, you just have the EXECUTE object privilege on any user-defined function(s) used in the function-based index if those functions are owned by another user.

Additionally, to use a function-based index:

1. The table must be analyzed after the index is created.
2. The query must be guaranteed not to need any NULL values from the indexed expression, since NULL values are not stored in indexes.
   

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.

Because a function-based index depends upon any function it is using, it can be invalidated when a function changes. If the function is valid, you can use an ALTER INDEX ... ENABLE statement to enable a function-based index that has been disabled. The ALTER INDEX ... DISABLE statement lets you disable the use of a function-based index. Consider doing this if you are working on the body of the function.

Altering Indexes

To alter an index, your schema must contain the index or you must have the ALTER ANY INDEX system privilege. Among the actions allowed by the ALTER INDEX statement are:

1. Rebuild or coalesce an existing index
2. Deallocate unused space or allocate a new extent
3. Specify parallel execution (or not) and alter the degree of parallelism
4. Alter storage parameters or physical attributes
5. Specify LOGGING or NOLOGGING
6. Enable or disable key compression
7. Mark the index unusable
8. Start or stop the monitoring of index usage
   

You cannot alter index column structure.

END