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.
