Creating Database Index
Indexes are used to arrange records in a proper manner in database using references to the data. This helps in the searching of records whenever a complex Select query is fired which involves searching multiple table. In case no index is created a full table scan is performed, which increases the response time, hence reduces performance.
Note: Indexes are created on columns which are meant for searching the data.
Index can be of two types:
- Unique Index: These are automatically created by the database whenever any primary key or unique constraint is imposed on a column. It is also called as Implicit Index.
- Non-Unique Index: These indexes are explicitly created by the users.
Creating an Index
Sytax to Create Index: CREATE INDEX <INDEX_NAME> ON <TABLE_NAME>(<COLUMN_NAME>[,<COLUMN_NAME> ... N]);
Note: An index created on a single column is called as simple index, and an index created on multiple columns is called as complex index.
Examples: CREATE INDEX EMP_DEPTNO_IDX ON EMP (DEPTNO); CREATE INDEX EMP_DEPTNO_IDX ON EMP (DEPTNO, NAME);
The 1st example above is an example of simple index and the 2nd one is of complex index.
Dropping an Index
Syntax: DROP INDEX INDEX_NAME;
Example: DROP INDEX EMP_DEPTNO_IDX;
Note: An index also gets deleted when a table or a column having the index is dropped.
Disadvantages of Index
A Database index does have some disadvantages:
- They decrease performance on inserts, updates, and deletes.
- Being objects they take up space.
Thus an index should only be created when they are actually needed.