SQL Index

SQL Index

Index in sql is created on existing tables to retrieve the rows quickly.

When there are thousands of records in a table, retrieving information will take a long time. Therefore indexes are created on columns which are accessed frequently, so that the information can be retrieved quickly. Indexes can be created on a single column or a group of columns. When a index is created, it first sorts the data and then it assigns a ROWID for each row.

Syntax to create Index:

CREATE INDEX index_name
ON table_name (column_name1,column_name2...);

Syntax to create SQL unique Index:

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

• index_name is the name of the INDEX.
• table_name is the name of the table to which the indexed column belongs.
• column_name1, column_name2.. is the list of columns which make up the INDEX.

In Oracle there are two types of SQL index namely, implicit and explicit.

Implicit Indexes:

They are created when a column is explicity defined with PRIMARY KEY, UNIQUE KEY Constraint.

Explicit Indexes:

They are created using the "create index.. " syntax.

NOTE:

1) Even though sql indexes are created to access the rows in the table quickly, they slow down DML operations like INSERT, UPDATE, DELETE on the table, because the indexes and tables both are updated along when a DML operation is performed. So use indexes only on columns which are used to search the table frequently.
2) Is is not required to create indexes on table which have less data.
3) In oracle database you can define up to sixteen (16) columns in an INDEX.