An Index is an ordered list of contents of a column, (or a group of columns) of a table.
When users require some record having some condition than oracle engine must search that record in the whole table.
This become very slow and very lengthy when there is mi l l i ons of record in the table.
So indexing a table is an 'access strategy', that is a way to sort and search records in the table.
By indexing we can improve the speed of searching and retrieving the record from table. (It is just li ke searching data in DSM)
Index is created on one or more columns. Based on the number of columns included in the index, an index can be:
An index created on a single column of a table is called a Simple Index. The syntax for creating simple index that allows duplicate values is as described.
CREATE INDEX <IndexName> ON <TableName> (<ColumnName>);
Create a simple index on Student_id column of the Student table
An index created on more than one column is called a Composite Index. The syntax for creating a composite index that allows duplicate values is:
CREATE INDEX <Index Name> ON <Table Name> (<ColumnNamel> <ColumnName2>);
Example 1: Create a composite index on the Student table on columns Student_id and Faculty_id;
A unique index can also be created on one or more columns. If an index is created on a single column, it is called a Simple Unique Index. The syntax for creating a simple unique index is as follows:
CREATE UNIQUE INDEX <Index Name> ON <Table Name> (<Column Name>);
If an index is created on more than one column, it is called a Composite Unique Index. The syntax for creating a composite unique index is as follows:
CREATE UNIQUE INDEX <Index Name> ON <Table Name> (<Column Name>, <Column Name>);
Create a unique index on student_id column of the student table
When the user defines a primary key or a unique key constraint at table or column level, the Oracle engine automatically creates a unique index on the primary key or unique key column(s).