• Some specific steps are perform by the oracle engine each time whenever user access record from the data base table.
  • It will first search for a specific table on the hard disk from which record is to be retrieve.
  • In next step it will perform sequential search on the table to access record.
  • Oracle has to search the entire table row by row locate specific record.
  • Since record are store in the manner in which they are inserted, a complete sequential search has to be perform hence it down the speed of data retrieval.
  • Oracle provides an access technique to improve data access operation known as index.
  • Oracle creates a separate index file for the data base table they contain index.
  • It contains two fields.
  • The first field stores data of the index column.
  • The next field contains address of the record.
  • In the index file index column record are store in a sorted meaner.
  • Oracle uses row id as address field.
  • A row_id is a unique value which is generated and assign by the oracle to each record.
  • A row_id is use by the oracle for identifying records uniquely.
  • After creating an index whenever a record is to be fetched the oracle will loop out into the index file inserted or searching from the data base table.
  • Since records are stored in the sorted meaner with its row id, it can be access quickly without searching the entire table.
  • It is similar to a book index that helps to locate specific information without referring a complete book sequentially.
  • Oracles allow two types of index.

Duplicate index

  • An index that allows duplicate value for index is called duplicate index.

Unique index

  • An index does not allow duplicate value for index is called unique index.
  • An index can be created either on single or multiple columns.
  • An index created on multiple columns is known as composite index or compound index.
Syntax:  (Duplicate index simple/composite)
CREATE INDEX <index_name>
       ON <table_name> (<column_name(s)>);

Example:

CREATE INDEX emp_idx ON EMP (emp_code[,dept_no]);
Syntax:  (Unique index simple/composite)
CREATE UNIQUE INDEX <index_name>
       ON <table_name> (<column_name(s)>);

Example:

CREATE UNIQUE INDEX emp_idx
       ON EMP (emp_code[,dept_no]);