Index and Types of Index in Oracle and it’s Advantages #Diksha Oracle Training Centre

         Index and Types of Index in Oracle and it’s Advantages.

 

Hello Everyone,

Today I will discuss about Index and types of index in Oracle and its advantages. I suggest you to please follow my previous blogs related to SQL topics for better understanding. Let’s start why index is used in SQL database and the types of index and benefits of using index for query performance. Index is an object which is associated with tables or table cluster that can used to speed data access and reducing disk I/O. By creating an index, you can retrieve related set of rows from table instead of all Rows. In database technologies (Oracle, SQL Server, Sybase, DB2, MySQL, etc.), the objects we create to access the data for fast retrieval is known as Index:

The usage of Index is very common especially in the performance tuning of database. Index is one of the most important objects that are used for performance tuning of databases. When any data is queried from a table in Oracle database, Oracle first check if an index exists in that table and if there is an index and the desired number of records is huge in the table, Oracle returns the requested data via the Index. This type of data access takes less time than usual and is less costly and is called Index Scan. If there is no index or if the number of records huge in the table then the whole table will scan, then Oracle thinks that querying from index is more costly than scans the whole table that is full table scan. In this case Query Optimizer decides whether it is full table scan or index scan.

 Let’s create Index and see how it works , Syntax For creating SQL is as below :

Creating Index on one column

CREATE INDEX idx_salary

ON employee(salary);

We can create index on multiple column as well as per requirement.

CREATE INDEX idx_id

ON employee(salary,emp_id);

Now see how it’s improve the performance of SQL query as I am trying to fetch some records from employee table against salary or id column in query where condition.

Below is the query  before creating index where we can see Unindexed column shown in red :

EXPLAIN PLAN FOR

Select name,designation

From employee

Where salary > 40000

And EMP_ID between 2000 and 3000;

Output of the Query is as below as index is not used in this query:

 

After Creating index the output of above query is as below:

 

 Once we run this query it will give you output fast as index is created on salary and  id column and Database can understand and it’s directly go to salary and id column and  fetch the record fast . If we not created index in Employee table and write the above  query then whole table will scan and it will take time to give the output as query doing  full table scan. That’s why index is mainly used when we have huge number of records in a table. But here in employee table record is less so no need of index.

 

 Types of Index

 

B-tree index

 

The most common index type is the b-tree index. B-tree actually stores row level data in the leaf nodes of the index  ,It is named b-tree after a computer science construct of the same name.

When we create s create index syntax by default it is b-tree index.

B-tree Index Syntax:

-- Create Syntax

CREATE INDEX idx_empsalary

ON employee(salary);

Above Index created on one column .

Also note that if your index is on more than one column, the leading column is very important.  For example if you have a multi-column  index on columns salary and id  in that order, you can also do a query on column salary alone and use that index.

Below index is Multi column Index

-- Create Syntax

CREATE INDEX idx_name_id

ON employee(salary,emp_id);

We can drop index by using below command :

Syntax:

Drop index idx _name_id;

Drop index idx _empsalary;

 

Output of the above command is as below :

 

 

We usually issue the above command when index is not required. Since it’s an object so we can drop index as it will take space in database. It’s depends on requirement.

 

Note: If table is dropped index automatically dropped from database.

Bitmap Index:

 

Bitmap Index is basically used when we have duplicity of records in column for Particular table like if we have table employee we have gender column and values is duplicate like M,F,M,M,F . and that values is huge and also values like Y, N etc in other column then we use Bitmap index . As B-tree index will go to that particular column but it will do row by row scan but bitmap index groups all the records at same time and give the result fast. Like once it’s reach to Gender column and find first Record as M it will not check second record as it group all M records on that particular column then go to next column N and group all the records of N value exist in table. So whenever we have duplicates records in table for Gender or other column where duplicity or repeated value is there then we should use Bitmap Index instead of B-tree Index .

 

Syntax of Bitmap index is as below :

 

CREATE BITMAP INDEX idx_gender

On employee (gender);

We can drop bitmap index as well by using Drop index index_name command.

Note: If table is dropped index automatically dropped from database.

Function Based Index:

If value in Table is in upper and lower case then we need to create function Index. If we write below query for our requirement   then we can use Function Index:

Select * from employee

Where UPPER(name)='Prashant';

Create Function Index Syntax:

Create index employee_name_fidx

On employee (UPPER(name));

Output of the above query is as below :

 

Explain plan for above query output  is as below :

 

 

We can drop index by using drop index index_name command if index is not required.

 

Note: Function based index can be a b-tree index or bitmap index.

 

 Cluster Index and Non Cluster Index.

 

A clustered index determines the order in which the rows of the table will be stored on disk – and it actually stores row level data in the leaf nodes of the index itself . But, a table can have only one clustered index. Non clustered indexes store both a value and a pointer to the actual row that holds that value.

 Advantages of Index:

 

Fast Retrieval of data

 

Speed up SELECT queries

 

Reduce I/O and Lowest I/O

 

Fast Data Access

 

Disadvantage of Index:

 

When data is in table is less then no need to use index as index itself an object at it takes space in database.

 

In general, indexes improve performance in our Select queries and slow down DML (insert, update, delete) operations. When in query we used Multiple DML operation then index should be avoided.

 

When we use index, database has an extra load because the maintenance load of database will increase. Also we can see the execution plan of query by using EXPLAIN PLAN FOR  query in select statement. So Index play vital role in Performance Tuning of Database while we create complex queries to generate big reports by using several queries from tables to generate report. We need to see whether index used in the query or not etc.

 

Hope you  are able to understand what is index and why index is important in Database. Index mainly used for fast retrieval of data and if records is huge we need to create index on table as per query requirement.

Please read the blog as this topic is very important for candidate who is preparing for interview as index related questions asked very frequently. Go through this  blog  and let me know if you have any doubts.

Thanks.