Index Unique Scan

Till now, We have seen the different types of tables and indexes supported by Oracle. As mentioned earlier, Index is used to improve the performance of SQL. In this topic, we will discuss how these indexes are accessed (or referred) by Oracle optimizer (which decides how query is going to be executed) while executing the query.

Oracle accesses index by any one of these access methods,

1.     Index Unique Scan

2.     Index Range Scan

3.     Index Range Scans Descending

4.     Index Skip Scan

5.     Index Full Scan

6.     Index Fast Full Scan

The below mentioned scans are for the table access method. It can be either one of these,

1.     Full Table Scan

2.     Rowid Scan

Today, we will discuss about Index Unique Scan.

Index Unique Scan is one of the index access methods supported by Oracle.

Index Unique Scan means only one index record is going to be accessed from the index table while executing the query and as a resultant, only one ROWID is going to be returned. So, this scan returns, at most, a single ROWID.

LITTLE-KNOWN FACTS TO BE REMEMBERED:

·         If Oracle has to follow Index Unique Scan, then in the SQL, equality operator (=) must be used. If any operator is used other than equality operator, then Oracle can’t impose this Index Unique Scan.

·         Only on b*tree unique index, this Index Unique Scan is imposed if equality operator is used in the query. In all other types of index (like non-unique b*tree index), Oracle Can’t impose this Index Unique Scan though equality(=) operator is used and only one index record is going to be referred as per the dataset.

ADVANTAGE:

·         Query retrieval will be fast if Index Unique Scan is followed since only one record is going to be returned.

·         In this index access method, 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:

·         If Oracle has to follow this Index Unique Scan, then index table has to be created in advance before executing this query. This index table consumes considerable amount of memory.

HOW TO VERIFY:

How to verify whether Oracle follows index unique scan or not while executing the sql query. If a query follows this, 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)                          | EMP_NO_INDX                          |            |

In the explain plan, whenever it follows the index unique scan against an unique index, it displays the keyword (UNIQUE SCAN) in the operation column against the index name.

EXAMPLE:

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

DATA TABLE:

ROWID	empid	column2	column3	column4	column5
AAAAA1	5	..	..	..	..
AAAAA2	10	..	..	..	..
AAAAA3	9	..	..	..	..
AAAAA4	4	..	..	..	..
AAAAA5	6	..	..	..	..
AAAAA6	7	..	..	..	..
AAAAA7	1	..	..	..	..
AAAAA8	8	..	..	..	..
AAAAA9	2	..	..	..	..
AAAAA10	3	..	..	..	..

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

Select * from emp where empid = 8;

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 empid, ‘8’. Since oracle is searching in the data table, it can’t directly go to 8^th record. Oracle starts from first record(AAAAA1) and traverse thro all the records till it reaches the last one(AAAAA10). So in b/w, whichever table record has this empid 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 unwanted 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 (especially unique index) on empid column.

Create an unique index on this table for empid column. (create unique index emp_no_indx on emp(empid)).

Index table will logically look like this,

UNIQUE INDEX TABLE:

INDEX	ROWID
1	AAAAA7
2	AAAAA9
3	AAAAA10
4	AAAAA4
5	AAAAA1
6	AAAAA5
7	AAAAA6
8	AAAAA8
9	AAAAA3
10	AAAAA2

First column (INDEX) : it stores all the values of empid column in the ascending order.

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 empid = 8;

When oracle executes this sql, first it looks for any index which has already been created on this “empid” column. It comes to know that the index, “emp_no_indx” has already been created on “empid” column. So, oracle refers the index table first before hitting the actual data table. Since we are looking for the empid:”8”, oracle hits index table first and get the corresponding ROWID (AAAAA8). 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 8^th index record directly in the index table. So, it won’t touch the remaining 9 index records.

After the getting the required ROWID (AAAAA8), oracle directly refers the 8^th record of the data table since it knows the exact location (ROWID of 8^th record is AAAAA8). 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)                          | EMP_NO_INDX (UNIQUE)     |     1       |

Since this index table is used, oracle will take only 2 seconds (assume 1 second to get the ROWID of 8^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)

Explain tells us that the index (EMP_NO_INDX) which is of unique type, it is being accessed via “Index Unique Scan” method. Since the query follows “Index Unique Scan” on this index, only one ROWID (actually only one index record is going to be accessed[8^th index record here]) is going to be returned (“AAAAA8”). That’s why it is called as “Index Unique Scan” access method.