Planet For Application Life Development Presents
MY IT World

Explore and uptodate your technology skills...

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:

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.