B*TREE unique index

B*Tree unique index, another chapter in performance tuning.

DEFINITION:

Oracle uses INDEX to improve the performance of SQL queries. Following are the different types of INDEX which are supported by ORACLE,

1.     B*TREE unique index

2.     B*TREE non-unique index

3.     Bitmap index

4.     Reverse index

5.     FBI (Function based index) for in-built functions

6.     FBI (Function based index) for user defined functions

7.     Cluster index

8.     IOT (Index Organized Table)

9.     Organization index

Today, we will discuss about b*tree unique index.

B*TREE unique index is one of the index types that is supported by oracle. If this index is imposed on a column, then all the non-null values of that column should be unique. It means this index allows NULL value to be stored in the column but make sure that all the non-NULL values are unique. So all the index values in the index table would be unique

This unique index can be created on a single column or more than one column. More than one unique index can be created for the same data table.

ADVANTAGE:

·         Query retrieval will be fast if the column (on which this unique index is created) is referred in the WHERE clause of the sql query

·         In this unique index, random search is made to look for the indexed values instead of sequential search. Performance gain is achieved since the random search (which is used while searching for the data in this unique index table) is much faster than the sequential search (which is used while searching for the data in data table directly)

·         In some cases, if all the required columns to be displayed available in the index table itself, then Oracle don’t need to refer the actual data table at all.

DISADVANTAGE:

·         It consumes considerable amount of memory since it needs the space to store this unique index table.

·         DML operations (Insert, Update, Delete, Merge) will be affected and this operations take addition time to execute the DML statements. This additional time is consumed by Oracle in order to update the index table when user tries to do any DML activity in the actual table since index table should be in sync with the actual table.

HOW TO VERIFY:

How to verify whether the created index is unique and this unique index is referred by the sql query. If query uses, then you will find similar execution plan like this,

--------------------------------------------------------------------------------------------------------

| Id  | Operation                                                     | Name                                             | Rows  |

--------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                                   |                                                          |            |

|   1 |  TABLE ACCESS (BY INDEX ROWID)      | EMP                                                 |           |

|   2 |   INDEX (UNIQUE SCAN)                          | PAN_NO_INDX (UNIQUE)     |            |

In the explain plan, whenever it displays the unique index, it displays the keyword (UNIQUE) immediately after the index name.

LITTLE-KNOWN FACTS TO BE REMEMBERED:

·         Whenever a primary key constraint is created in a table, this constraint automatically creates unique index on that table.

·         You can create unique index explicitly on a column of a table first and then you can create primary key constraint on the same column. Oracle wont’ throw any errors instead this primary key constraint would reuse the already created unique index instead of creating the duplicate

·         Null can be stored(or understand) only in data table but not in index table. It means, suppose, I create a unique index on column-a of table-a, and after I insert 10 records. If out of these 10 records, suppose 8 records have some unique values and the remaining 2 records have NULL for this column-a. After creating this unique index, only 8 index records would be created in index table and the remaining 2 records (which has NULL in column-a) won’t be available in the index table.

EXAMPLE:

Create an employee table and inserts 10 records. The table will look like this,

DATA TABLE:

ROWID	empid	column2	column3	Panno	column4	column5
AAAAA1	1	..	..	AQXBB12343	..	..
AAAAA2	2	..	..	AQXBB12342	..	..
AAAAA3	3	..	..	AQXBB12344	..	..
AAAAA4	4	..	..	AQXBB12341	..	..
AAAAA5	5	..	..		..	..
AAAAA6	6	..	..	AQXBB12346	..	..
AAAAA7	7	..	..	AQXBB12345	..	..
AAAAA8	8	..	..	AQXBB12348	..	..
AAAAA9	9	..	..		..	..
AAAAA10	10	..	..	AQXBB12347	..	..

Fire this query against this table where the requirement is to display the all the attributes of an employee whose panno is “AQXBB12345”

Select * from emp where panno = ‘AQXBB12345’;

Here, we are yet to create index. Now, to execute this query, Oracle will take 10 seconds (assume oracle takes 1 second for single data table record search).

Why it is taking 10 seconds? Reason is, Oracle follows the sequential search when it looks for the data in the data table. Here, we are looking for the panno, ‘AQXBB12345’. Since oracle is searching in the data table, it can’t directly go to 10^th record. Oracle starts from first record and traverse thro all the records till it reaches the last one. So in b/w, whichever table record has this panno would be displayed in the output. Explain plan will look like this,

--------------------------------------------------------------------------------------------------------

| Id  | Operation                                                     | Name                                             | Rows  |

--------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                                   |                                                          |      1       |

|   1 |  TABLE ACCESS FULL                                  | EMP                                                |     10      |

Problem here is, oracle has unnecessarily referred the unwantedt 9 table records which are not meant to be referred for this sql.

In order to overcome this issue, we have to create the index on panno column.

Why is panno column the ideal candidate to impose B*TREE unique index? Because panno is unique to each employee. It means if a employee is assigned with a panno, then no other employee can have the same panno. In addition to this, some employees may yet to get the panno. In this case, NULL would be stored against this type of employees. Since in the WHERE clause, we refer this panno column, it becomes the ideal column to impose this index

Create an unique index on this table for panno column. (create unique index pan_no_indx on emp(panno)).

Index table will logically look like this,

UNIQUE INDEX TABLE:

INDEX	ROWID
AQXBB12341	AAAAA4
AQXBB12342	AAAAA2
AQXBB12343	AAAAA1
AQXBB12344	AAAAA3
AQXBB12345	AAAAA7
AQXBB12346	AAAAA6
AQXBB12347	AAAAA10
AQXBB12348	AAAAA8

First column (INDEX) : it stores all the values of panno column in the ascending order but omits 2 records (AAAAA5,AAAAA9) since it doesn’t have any value in panno column.

Second column (ROWID) : it stores the ROWID of the corresponding record.

After creating this index table, fire the same query again,

Select * from emp where panno = ‘AQXBB12345’;

When oracle executes this sql, first it looks for any index which has already been created on this “panno” column. It comes to know that the index, “pan_no_indx” has already been created on “panno” column. So, oracle refers the index table first before hitting the actual data table. Since we are looking for the panno:’AWXBB12345’, oracle hits index table first and get the corresponding ROWID (AAAAA7). When oracle searches for the value in this index table, it follows random search (i.e. binary search) to go to the corresponding index record. Using this binary search, Oracle is intelligent enough to go to the 5^th index record directly in the index table. So, it won’t touch the remaining 7 index records.

After the getting the required ROWID (AAAAA7), oracle directly refers the 7^th record of the data table since it knows the exact location (ROWID of 7^th record is AAAAA7). With this, it doesn’t refer the remaining 9 table records. Now, explain table will look like this,

--------------------------------------------------------------------------------------------------------

| Id  | Operation                                                     | Name                                             | Rows  |

--------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                                   |                                                          |     1       |

|   1 |  TABLE ACCESS (BY INDEX ROWID)      | EMP                                                 |    1       |

|   2 |   INDEX (UNIQUE SCAN)                          | PAN_NO_INDX (UNIQUE)     |     1       |

Since this index table is used, oracle will take only 2 seconds (assume 1 second to get the ROWID of 7^th index record since it follows binary search + 1 second to retrieve the actual table data record since we know the exact ROWID which is retrieved in the previous step)