SQL indexes

SQL indexes

An index is a schema object. It is used by the server to speed up the retrieval of rows by using a pointer. It can reduce disk I/O(input/output) by using a rapid path access method to locate data quickly. An index helps to speed up select queries and where clauses, but it slows down data input, with the update and the insert statements. Indexes can be created or dropped with no effect on the data.

Creating an Index :

CREATE INDEX index ON TABLE column;

where index is the name given to that index and TABLE is the name of the table on which that index is created and column is the name of that column for which it is applied.

For creating index on multiple columns:

 CREATE INDEX index ON TABLE (cloumn1, column2,.....);

Removing an Index : 

To remove an index from the data dictionary by using the DROP INDEX command.

DROP INDEX index;

To drop an index, you must be the owner of the index or have the DROP ANY INDEX privilege.