INDEX

It is a pointer locates the physical address of data.

• We will be creating indexes explicitly to speed up SQL statement execution on a table.
• It will improve the performance of oracle while retrieving or manipulating the data from table.
• It is automatically activated when index column is used in “where” clause.
• Indexes can be created on a single column or a group of columns.
• When an index is created, it first sorts the data and then it assigns a ROWID for each row.
• When there are thousands of records in a table, retrieving information will have performance issue.
• Therefore indexes are created on columns which are accessed frequently, so that the information can be retrieved quickly.

TYPES OF INDEXES:-

· Unique index

· Non-unique index

· Composite index

· Function-based index

System tables for viewing indexes:

User_indexes:-

Syntax:-

Select index_name, index_type from user_indexes where table_name =’’;
 
Dba_indexes:-
Syntax:-
Select index_name, index_type from dba_indexes where table_name =’
’;
 
All_indexes:-
Syntax:-
Select index_name, index_type from all_indexes where table_name =’
’;