Indexes are created to improve access and sorting performance in large tables. When you index a table by one or more of its columns, you can access data directly and more efficiently. Without an index, ORACLE must read every row in a table to find rows that match the WHERE clause of a query. If you create an index, ORACLE looks in the index to determine the location of rows that match the WHERE clause, then returns those rows in the results table. It is important to create indexes only on rows that are likely to appear in WHERE clauses because indexes must be updated each time a new record is added and because indexes take up disk space.

Small tables will generally perform better without indexes unless the purpose is to enforce uniqueness in the primary key. If you specify a primary key when creating a table, ORACLE creates an index on the primary key that guarantees uniqueness of the primary key. You will receive an error message if you try to insert a row that has a primary key identical to one that already exists in the table.

Generic and actual syntax for creating an index is as follows:

Specifying that an index is UNIQUE is optional. A UNIQUE index guarantees that each indexed row is unique on the values of the index columns. If you create an index as UNIQUE and you try to insert or update the indexed columns with non-unique values, an error message will be returned. Let's create an index on the EMPLOYEE table. Since we are creating the index on the DEPTNO field, the index will not be unique.

ORACLE returns an Index created message. Look at the results table of a query after this index is created. With such a small table, speed will not be noticeable, but look at the order of the employees listed. They are ordered by DEPTNO, which is the indexed column. Indexes in ORACLE are in ascending order.

The results table is shown below.