Indexes in Oracle SQL

What is index ?

  • Analogy : Index is a db object that makes a data retrieval faster.
  • Def : Index is a schema object that contains entry of each record of data present in indexed column which provides faster access to the rows of that table.
  • Only create for performance of data retrieval and there is no other advantages
  • Note : Oracle does not index NULL values

How indexes work ?

  • In general, it work just like a textbook index. So that we can directly navigate to that page. Similarly db index work in the similar way but for records.
  • Behind the schemes a BTree is created based on the indexed value and the leaf nodes contains the a very small set of data like (3 to 4) and each data is mapped to a rowid, rowid directly provides the access to the record of that table
    • What is RowId : It is a hexdecimal value used to uniquely represent the entry of the table

Types of Index in Oracle :

  • B-Tree index/Normal
  • BitMap index
  • Reverse key index
  • Function based index (Can be b-tree or bitmap)
  • Composite Index (Can be b-tree or bitmap)
  • Domain index.

How to create Index ?

B-Tree Index is created by default – Recommended for unique values

create index STUDENT_INFO_TEMP0718_INDEX on STUDENT_INFO_TEMP0718(S_AGE);

Bitmap Index – Recommended for values that are less distinct like (10,20,30 or true or false etc)

create bitmap index STUDENT_INFO_TEMP0718_INDEX on STUDENT_INFO_TEMP0718(S_AGE);

Function index – Recommended for values which has functions applied on it

create index STUDENT_INFO_TEMP0718_INDEX on STUDENT_INFO_TEMP0718(UPPER(S_NAME));

Reverse key index – To Avoid index block contention

create bitmap index STUDENT_INFO_TEMP0718_INDEX on STUDENT_INFO_TEMP0718(S_AGE) REVERSE;

Types of Scan when you fire a select query in Oracle DB

  • Table Scan – (Checks on each record 1 by 1) hence takes more time
  • Index Scan

How to check if Oracle is using Index Scan or table Scan? Using below command

SET AUTOTRACE ON EXPLAIN --IN SQL PLUS
--F10 in SQL Developer Explain plan

How to find the list of indexes for a given table or columns or its stats?

select * from user_indexes
where 1=1
--and INDEX_NAME=''
and TABLE_NAME='STUDENT_INFO_TEMP0718'
and 1=1;


--List of index on column basis

select * from user_ind_columns where 1=1
and TABLE_NAME='STUDENT_INFO_TEMP0718'
and 1=1;

--Statistic of indexes 

select * from user_ind_statistics where 1=1
and 1=1;

--Function Indexes dependent column

select * from user_ind_expressions WHERE 1=1
and 1=1;

B-Tree Index

What is B-tree index?

  • B-Tree stands for Balanced Tree
  • Most common type of indexes in oracle
  • B-Tree indexes is an ordered list of values divided by ranges
  • By associating keys with a row or range of rows, B-Tree provides excellent retrieval performance.

How data is stored?

The table with rowid
b-tree dividing indexed column into ranges and adding rowid to small ranges
  • When a index is create on a column for example we created index on emp no here
  • So oracle takes all the column data of the column that has to be indexed along with its rowid, it orders the column data and divides it into a group of ranges and stored the rowid along with the ranges into a b-tree
  • When a table is create rowid is automatically assigned to each row

How to find if the index is being used or not?

  • Using explain plan – Can be used to check future plan
  • By checking the stats of the query which is executed in past and checking if index is being used, refer below post – Can be used to check past plan

Check the stats of the SQL Query already executed

  • By monitoring the index – Can be used to check if index is used at runtime

When to create a B-Tree index ?

  • Typically created when the distinct values of a column is very high
  • When distinct value is very low we usually prefer bitmap
  • Cardinality is the term used to check the distinct values
    • Cardinality is the ratio of distinct row by the total records in the table

What types of Scan operations will B-Tree do ?

  • Range Scan
  • Unique Scan
  • Full Scan
  • Full Scan (Min/Max)
  • Fast Full Scan – Combining two index scan
Auto Trace shows the Scan type is Full Scan

In the above example Index is not used since it has a small amount of data and optimizer has ignored the index

Can we create an Index for all the columns

  • We usually create index for only those columns which we use in where clause.
  • In case if you use all the columns are used in the where clause then we would create that table as an index organised table.
  • Index organized table is a table in which stored data in a organized order.

Advantages and Disadvantages of B-Tree index

  • Index increases the performance of select statement.
  • Indexes are nothing but extra segments where the data is stored so over the time when the data size increases the DML operations will take more time to complete since it has to shuffel or indexes in the correct order. Hence create index only on those tables which are used in the where condition.

Bitmap Index

  • Def : A bitmap index is a special kind of database index that uses bitmaps.
  • Bitmap index is recommended
    • when number of distinct values in a column is very less
    • And the number of insert and update operations on the records of that table is very less somethink like read only tables
    • Not recommended for frequent DML tables because the tables will get locked when an update operation is performed on any of the record and it is not yet committed.
Student table
Bitmap index on SResult column of Students table

How to check if bitmap is being used ?

using explain plan or by using v$sql and v$sql_plan

Function Based Index

  • In bitmap index, the column value is not stored directly into the index table but it is passed to a function and then the result of the function value is stored in the index segment.
  • So when you apply a function to a where clause like where upper(ename)=’IT’, the index of ename will not be applied(if index is create on ename is the assumption) here because oracle does not work that way, we have to create a separate index with upper(ename) then only the index will be applied. This is called as a function index.
  • If the index is used or not you can determine by using explain plan.
Function Based index

Advantages and Drawbacks :

  • We can now apply index of the result of functions
  • But on every DML operation this index has to re-iterate the values of all the new values inserted every time so there is a trade off between select statement and DML operation of that column in the table.

Reverse Key index

  • Reverse key index stored the key in a reverse way so that the data in the leaf node is not accumulated but it is distributed evenly. The same BTree is used behind the scenes here.
  • Reverse key index solves the problem of key contention in leaf block
  • In the above example 40% of the records are having marks more than 90 so when we do select a specific record like 92 in the where clause the index will not perform well because b-tree leaf that stores the 90’s data in it will have a lot of data in it and it will require that much amount of time as of full scan without index
  • Below is the screenshot of the BTree that will be created, so as you can imagine that the leaf 76-100 will be crouded.
  • So to fix the index we create reverse key index, i.e we reverse the actual value 100 is taken as 001 and 96 is 69 and 94 is 49 and so on…so after reversing the data, the data is distributed along the B-Tree, screenshot below for reference
  • The disadvantage of doing a reverse index is we cannot do a range scan on these index like marks is greater than 90, since the data is reversed and distributed oracle has to do a set of long calculation to get the value, instead the oracle optimiser goes for a full table scan to get the values faster.
  • The advantage of reverse index is when you have to get the exact value from the set of clustered data in a leaf.

Example : When reverse index scan is not used

Index Montoring

Check if index is used or no :

Benefits and Drawbacks of index ?

Reference :

B-Tree
BitMap Index

Leave a Comment