What are types of indexes in SQL with examples ?

• Types of Indexes in SQL :

In most of SQL projects the data is in millions and billions. Because of huge volume of the data performance of SQL statement degrades.So there are lot of different ways to improve the performance of application,performance of reports or performance of SQL query.So most of people prefer SQL Indexes to improve the performance of the query.Indexing sometimes improve the performance but make sure that the table is not inserting or updating the data while you are running the query.This article will give you idea about the different Types of indexes in SQL.The article is different from other indexing articles because this article on Types of indexes in SQL will give concept description with its example.

CLICK HERE TO GET BASIC IDEA OF PERFORMANCE TUNING…

Definition Of Index:

“SQL Indexes are nothing but optional structure associated with the table which may or may not improve the performance of Query”

“In simple words suppose we want to search the topic in to book we go to index page of that book and search the topic which we want.Just like that to search the values from the table when indexing is there you need not use the full table scan.”

SQL Indexes are nothing but way of reducing the cost of the query.More the cost of the query less the performance of the query.The main task of query tuner is to reduce the cost of the query using indexing,Reduce the Full table scans,reduce the time to fetch the records from the query.

There are following types of SQL Indexes:

1.Normal index

2.Unique Index

3.Bit Map Index

4.Composite Index

5.B-Tree Index(Oracle considered Normal indexes as B-Tree Indexes)

6.Function Based Index

7.Clustered Index

8.Non-Clustered Index.

In this article i will give brief introduction about Normal index,Bit map index and unique index which are most widely used indexes to tune the Query performance.

CLICK HERE TO GET INTERVIEW QUSTIONS ON PERFORMANCE TUNING..

Normal Indexes:

To create normal index you must have CREATE ANY INDEX privilege.After that you can create index on the table.Before creating these indexes you need to go to table and check the cost of the table.If you are using SQL developer editor you will get query cost by pressing F10 key else you are using the direct statement  ‘Explain Plan’ before SQL Query.After checking the cost of query user needs to check whether any indexes are there on the table or not.’ALL_INDEXES’ is the system table which gives user the information about the indexes on the table.If Indexing is not there on table level you can do the indexing on column of the table.

Syntax of Normal Index:

Create index Index_name on Table_Name(Column_Name);

Example:

Create index NI_EMP_NAME on EMP(NAME);

Unique Index:

To create unique index you must have CREATE ANY INDEX privilege.Here the concept is bit different.User needs to check the values of the table to create unique index.If table contains uniquely identified values in specified column then you should use unique index.Especially while creating the table if we specify the primary key  then unique index is automatically created on that column.But for Unique key constaint columns you separately need to do indexing.Kindly make sure that Unique key indexes created on the columns which has unique values only.

Syntax for Unique Index:

Create Unique index  Index_name on Table_name(Unique column name);

Example:

CREATE UNIQUE INDEX UI1_EMP on EMP(EMP_ID);

CLICK HERE TO GET INFORMATION ON OPTIMIZER…

Bit-Map Index:

If Table contains the distinct values which are not more than 20 distinct values then user should go for Bit map indexes.User should avoid the indexing on each and every row and do the indexing only on distinct records of the table column.You should able to check drastic change in query cost after changing the normal index to Bit map index.The bit map indexes are very much useful in dataware housing where there are low level of concurrent transactions.Bit map index stores row_id as associated key value with bitmap and did the indexing only distinct values.Means If in  1 million  records only 20 distinct values are there so Bitmap index only stores 20 values as bitmap and fetches the records from that 20 values only.

Syntax:

Create bitmap index Index_name on Table_name(Columns which have distinct values);

Example:

CREATE BITMAP index  BM_DEPT_NAME on DEPT(Department_name);

Example/Real Life Scenario:

“Suppose There are 2 tables which has milions of records.We need to improve the performance of Query.Now It is taking 4 mins to fetch 1 million Records.”

Step 1:

Explain Plan select * from DEPT d,EMP e where d.DEPTNO= e.deptno;

Output:

Cost of DEPT Table-20000  – table is Full Scanned

Cost of EMP tabe-20000-table is Full Scanned

Step 2:

Check whether there are SQL Indexes on table columns:

Select * from ALL_INDEXES where Table name in (‘EMP’,’DEPT’);

Output:

No indexes found.

Step 3:

Check description of the table and check whether the normal index where the Unique index and where bitmap indexes are applicable.

Step 4:

Creation of normal index on EMP table name column.

Create index NI_EMP_NAME on EMP(NAME);

Step 5:

EMP_ID has unique values so kindly create UNIQUE INDEX ON that column.DEPTNO has also unique values so for DEPTNO column we need to create unique index.

CREATE UNIQUE INDEX UI1_EMP on EMP(EMP_ID);

CREATE UNIQUE INDEX UI2_DEPT on DEPT(DEPTNO);

Step 6:

Check for Distinct values.So Department_name has 20 distinct departments so on Department_name column create Bit-map index.

CREATE BITMAP index  BM_DEPT_NAME on DEPT(Department_name);

Step 7:

Check the cost of Query

Explain Plan select * from DEPT d,EMP e where d.DEPTNO= e.deptno;

Output:

Emp Table :-> 20  Fast Unique Scan

DEPT Table:-> 10 Bit-map Scan

and Results will come in 10 Seconds..Hope everyone get idea about basic indexing and how it is been used in real life scenarios.

Composite Index:

When 2 or more columns in single table are related which each other and used in where condition of select statement then user should create composite index on the columns which are created.If all columns selected by in query are in composite index then oracle will return the values from the index without accessing the table.

Composite indexes should be avoided as they are large in size

Example:

Select e.Emp_name,d.Dept_name

from Employee e  ,Deartment d

where e.Empno=d.empno

and e.Deptno=d.Deptno;

Suppose we want to do indexing on Employee and Department table.Here in above example Empno and Deptno are related to each other.So we can create index on Employee_Num and Department_Num

Create index CI_ENO_DEPTNO on Employee(Empno,Deptno);

CLICK HERE TO GET INFORMATION ABOUT JOINS IN SQL..

Function Based Indexes:

Function based indexes allows us to index on the functional columns so that oracle engine will take the index and improves the performance of the query. As per requirements we are using lot of SQL functions to fetch the results.Function based indexs gives ability to index the computed columns.Function based indexes are easy to implement and it also provides immediate value.These indexes speeds up the application without changing application code or query.

Syntax:

Create index indexname on tablename(Function_name(column_name));

Example:

Create index FI_Employee on Employee(trunc(Hire_date));

Clustered Indexes:

1.The clustered indexes are indexes which are physically stored in order means it stores in ascending or descending order in Database.

2.Clustered indexes are created one for each table.

3.When primary key is created then clustered index has been automatically created in the table.

4.If table is under heavy data modifications the clustered indexes are preferable to use.

Non Clustered Indexes:

1.The clustered indexes are used for searching purpose as we can create clustered indexes where primary is is defined.But Non clustered indexes are indexes which will be created on the multiple joining conditions,multiple filters used in query.

2.We can create 0 to 249 non-clustered indexes on single table.

3.Foreign keys should be non clustered.When user wants to retrieve heavy data from fields other than primary key the non clustered indexes are useful.

I have explained the Basic concept of SQL Indexes here.In upcoming articles i will explain how the indexing works in detailed description with Example.Please comment if you want copy of this article or if you have any questions.Hope you like article of SQL Indexes.

What is mean by Global and Local Index? (Types of Indexes in SQL in term of access  of Table )

When there is partition on the table and we need to apply the indexes on that table then we need to use global indexes or local indexes.When table is partitioned then we need to use global or local parameters/keywords in DDL of create index statement.

Global Index:

Usually when you create index on the table has indexed but when you are using partitioned table we need to change the syntax of the create index and need to use the Global index for one to many relationship.Global index is one to many relationships which allows index partition to map to many table partitions.The global index can be partitioned by range or hash method and it can  be defind on any kind of partitioned or non partitioned table.

Syntax:

Create index Indexname

On tablename(Column name)

GLOBAL

(PARTITION Partition_name values(value_of_partition),

PARTITION Partition_name values(value_of_partition),

PARTITION Partition_name values(value_of_partition),

PARTITION Partition_other values(value_of_partition));

Example:

Create index Employee_GI

On tablename(Employee_num)

GLOBAL

(PARTITION Employee_1 values(1),

PARTITION Employee_2 values(2),

PARTITION Employee_3 values(3),

PARTITION Employee_other values(default));

LOCAL INDEX:

Local indexes are indexes where there is one to one mapping between index partition and table partition.These indexes are basically used to improve the performance of partitioned tables.Local indexes directly uses divide and conquer approach to generate the Fast and best execution plan of SQL Query.

Syntax:

Create index indexname

on table_name(Column_name)

LOCAL

(Partition Partition_name1,

Partition Partition_name2….

);

These are some Types of Indexes in SQL. Hope you will like this article on Types of Indexes in  SQL.If you likes this article on Types of Indexes in SQL it is easy to start performance tuning to Database developers or programmers. The first step of performance tuning in Oracle in Indexing and studying the different types of indexes in SQL.

CLICK HERE TO GET 20 TCS Interview Questions…

HOME