DBMS Index

Index

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:

  • Simple index
  • Composite Index

creating Simple Index

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.

Syntax:

 CREATE INDEX <IndexName> ON <TableName> (<ColumnName>);

Example:

 Create a simple index on Student_id column of the Student table

Output:

Command Prompt
SQL> CREATE INDEX index_id ON Student(Student_id);

 Index created. 

Creating Composite Index

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:

syntax:

  CREATE INDEX <Index Name> ON <Table Name> (<ColumnNamel> <ColumnName2>);

Example:

 Example 1: Create a composite index on the Student table on columns Student_id and Faculty_id;

Output:

Command Prompt
SQL> CREATE INDEX index_id ON Student(Student_id, Faculty_id);


 Index created. 

Creation of Unique index

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:

Syntax:

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

Example:

 Create a unique index on student_id column of the student table

Output:

Command Prompt
SQL> CREATE UNIQUE INDEX index_id ON Student(Student_id);


 Index created. 

Note:

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).



Share This Page on:


Ask Question

Advanced SQL