Oracle:Indexes

Main Page >> Oracle and SQL >> Workbook >> Miscellaneous features >> Indexes

Indexing Tables in Oracle

Creating an index

A table index should include the columns you use most frequently in search conditions.

To index the ENAME column of the EMP table:

CREATE INDEX EMP_ENAME
ON EMP(ENAME);

Using an index to enforce uniqueness

The primary key of the EMP table is EMPNO, however there may also be another candidate key, for example the National Insurance Number of every person is also unique, or all department names must be unique.

To create a unique index on a column such as this you could:

CREATE UNIQUE INDEX DEPT_UNQ ON DEPT(DNAME);

• For information, the UNIQUE clause could have been used instead to achieve this, when the table was created (note, do not type this in):

CREATE TABLE Dept(
 deptno NUMBER(2) CONSTRAINT pk_dept PRIMARY KEY,
 dname VARCHAR2(14) NOT NULL CONSTRAINT uc_dname CHECK (dname=UPPER(dname)),
 loc VARCHAR2(13) CONSTRAINT uc_loc CHECK (loc=UPPER(loc)),
 constraint unq_dept UNIQUE(dname));

Deleting an index

To remove an index use the command DROP.

DROP INDEX EMP_EMPNI ON EMP

General rules for indexes

• If a table has more than a few hundred rows, index it
• Try not to create more than two or three indexes on a table
• Choose which columns to index based on frequency of use
• DO NOT Create a unique index on primary keys, specify the primary key constraint at table creation time, or alter the table to add the primary key constraint. The former is preferable.

Next Step

Data Dictionary