SQL Index

Index in SQL is created on existing tables and is a way to boost up the data retrieval performance of table.
Index should be created on the columns that are frequently used for searching data.

An Index is an ordered list of contents of a column ( or a group of columns) of a table.

Updating a table with index takes more time than updating a table without indexes as indexes also needs to be updated along with the data. So Indexes should only be created on the columns that are frequently used for searching.

SQL Create Index Syntax
CREATE INDEX index_name 
ON table_name (column_name1, column_name2,...);

Note that the Index created with above syntax can hold duplicate values for columns used for Indexing.

SQL Create Unique Index Syntax

Create a unique Index on the table

CREATE UNIQUE INDEX index_name 
ON table_name (column_name, column_name2,...);

Examples

Create Index Example
CREATE INDEX I_EMP_PHONE 
ON EMPLOYEE (FIRST_NAME, LAST_NAME);
Create Unique Index Example
CREATE UNIQUE INDEX I_EMP_PHONE 
ON EMPLOYEE (PHONE);

Implicit Indexes

These Indexes are created by database itself when use constraints like PRIMARY KEY or UNIQUE KEY

Explicit Indexes

They are created using the “create index.. ” syntax.

Drop Index Statement

The DROP INDEX statement is used to delete an index in a table.

Syntax

Oracle

DROP INDEX index_name ON table_name

MySql

ALTER TABLE table_name DROP INDEX index_name
Advertisements

,

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: