- 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]);