Indexes in SQL

Indexes:

   Ø Index on table is used to speed up the execution of queries in the table. This concept is similar to that of the index in a book.

   Ø An index can be considered as an ordered list of data of a column or a group of columns, together with a location.

Some of the Indexes are,

            1.    Simple Index

            2.    Composite Index

            3.    Reverse Index

            4.    Unique Index

Simple Index:

   Ø If the index is created by using only one column is known as simple index.

   Ø Up to 16 indexes can be used on a table.

Syntax:

1.           Create index <indexname> on  <tablename> (column name);

Example:     

1. Create index emp_record on emp(job);

Composite Index:

Ø If the index is created by more than one column is known as composite index.

Ø Indexed column of a table can have duplicate values.

 Example:

1.  Create index emp_records on emp(ename,job);

Unique Index:

Ø Unique index used to avoid Duplicate values.

Syntax:

1. Create unique index index_name on tablename(col_name);

Example:

1. Create unique index  stud_ind  on student(roll_no);

  Here, Roll_no column does not allow duplicate values.

 Reverse Index:

Ø It can be done by using “REVERSE” keyword

Syntax:

1.  Create index index_name on tablename (col_name) REVERSE;

Example:

1.  Create index Rev_ind on emp (empno) REVERSE;

Sample output, 

Normal Index	Reverse Index
Empno	Empno
12345652	25654321

 Alter REVERSE:

Ø Alter the reverse index into normal.

 Syntax:

1. Alter index index_name REBUILD NOREVERSE;

Example:

1. Alter index Rev_ind REBUILD NOREVERSE;

Drop index:

Syntax:

1. DROP index Index_name;

Example:

1.          DROP index stud_ind;       

Advantages:

Ø Involving exact match or range search.

Ø Table occupies less space.

Ø Key columns are not duplicated in the table and the index.

 

 

 

 

 

Share: