Indexes in DBMS

• An index is a object which is used to improve performance during retrieval of records.
• It helps to retrieve the data quickly from the tables.
• When column contains a large number of NULL values, then we can create Index.
• It a structure that provides faster access to the rows of a table based on the values of one or more columns.
• It stores data values and pointers to the rows where those data values occur.
• If a table is very small, then we cannot create Index.
• Indexes are very useful and make the data access very fast.

Syntax: CREATE INDEX

CREATE INDEX index_name ON table_name;

Code:

CREATE UNIQUE INDEX emp_ename_index
ON Employee(Ename);

• In the above example, the UNIQUE keyword is used when combined values of index should be unique. It does not allowed duplicate values to be inserted into the table.
• We created an Index on Employee name (Ename) column in the Employee table.
• Indexes can be dropped explicitly using the DROP INDEX command.

Syntax: DROP INDEX

DROP INDEX emp_ename_index;