Index in SQL

Indexes in SQL are created on existing tables to retrieve rows quickly. In the following article, will provide information to better understand Index in SQL, syntax and usage.

If a table has thousands of records, retrieving it will take a lot of time. Therefore, indexes are created on frequently accessed columns to retrieve information faster.

Indexes in SQL can be created on a column or a group of columns. When an SQL Index is created, it will first sort the data, then assign a ROWID to each row.

Syntax for creating Index in SQL

The syntax for creating an Index in SQL is:

CREATE INDEX index_name

ON table_name (column_name1, column_name2 …);

Syntax for creating a unique SQL Index:


ON table_name (column_name1, column_name2 …);


– index_name is the name of INDEX.
– table_name is the name of the table in which the column is to be indexed.
– column_name1, column_name2 .. is a list of columns that create INDEX.

In Oracle, there are 2 types of SQL Index: Implicit Index and Explicit Index.

Implicit Index

Implicit Index is created when the column is clearly described with PRIMARY KEY, UNIQUE KEY Constraint.

Explicit Index

Explicit Index is created using the syntax “create index ..”.

Note the Index in SQL

1. Although the SQL Index is created to access table rows quickly, it slows down the execution of DML queries like INSERT, UPDATE, DELETE on the table because the SQL Index and the table are updated together. when DML operations are performed. So only use Index in SQL in columns used to search regular tables.
2. It is not required to create an Index on a table with little data.
3. In an Oracle database, you can specify up to 16 columns in INDEX.
So above has just introduced you to the information about Index in SQL as well as some notes with Index in SQL. In addition, readers can find out the order details VIEW in SQL To create a virtual table in SQL, initially familiarize yourself with the database management structure.


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *