Index in SQL | What is Index in SQL | How to create Index in SQL

INDEX in SQL

Index in SQL is the most powerful and useful aspect of SQL. It helps in improving the performance of database queries and provides direct and fast access to database rows. The users can’t see indexes they are just used to speed up searches and queries.

Why Index?

In the current scenario, databases are getting larger day by day. The total number of rows reached millions and billions. when we retrieve a few rows out of these huge number of rows then it will take a lot of time to retrieve these rows. To save time SQL INDEX is being used.

An index stores data in index column(s) and each value has a location of the rows that it is in. So when running a query then it will start searching from the lower limit and will stop at the upper limit. It is just like the index of the book and lets us concentrate on the point where we are interested.

Types of Index

The SQL database provides different kinds of indexes. These indexes can be categorized as below

• B-Tree index : These are default index used by oracle. It is suitable for primary key and highly selective indexes.
• Bitmap index : In a bitmap index, an index entry uses a bitmap to point to multiple rows. A bitmap join index is a bitmap index for the join of two or more tables.
• Function based index : In Function based index, index includes columns that are either transformed by a function, such as the lower function, or it included in an expression. B-tree or bitmap indexes can be function-based.
• Application domain index : In domain based index, index is created by a user for data in an application-specific domain. The physical index need not use a traditional index structure and can be stored either in the Oracle database as tables or externally as a file.

How to create index in SQL

We can create an index on a table by using the following SQL command. It is to be noted duplicate values are allowed in SQL index:

CREATE INDEX index_name
ON table_name (column1, column2, ...);

In the above SQL query, we can see create index command followed by index_name. After that, we will use SQL table name after using ON. In the last, we will mention SQL column names. We can use single or multiple column names depending on our requirements. If we don’t want to allow duplicate values then we will use CREATE UNIQUE INDEX index_name instead of the above query SQL.

Index in SQL example

In the above example, we can see that we wanted to create COUNTRY_C_ID_PK index which is available in SQL HR schema. We created oracle indexes on COUNTRY_ID column to CREATE UNIQUE INDEX. It will only unique records in SQL table index. After writing this SQL script run the query. A new index name will be shown on the left side as illustrated in the above image.

DROP INDEX Statement

We can also drop INDEX SQL by using the below Oracle script.

DROP INDEX index_name;

Pre requisite to create INDEX

To create an indexing table we need to fulfill the below condition

1. The table that we eanted to be indexed must be in our own schema.
2. We must have the INDEX object privilege on the table to be indexed.
3. We must have the CREATE ANY INDEX system privilege for indexation table.

You can read more on SQL from this link or click here.