Skip to main content

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 8th 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 8th 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 8th record of the data table since it knows the exact location (ROWID of 8th 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 8th 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[8th index record here]) is going to be returned (“AAAAA8”). That’s why it is called as “Index Unique Scan” access method.
Labels:

Comments

Post a Comment

Popular posts from this blog

B*Tree Reverse index

B*Tree Reverse index is one of the index types that is supported by oracle. It more or less behaves like b*tree unique & non-unique index but only difference is the way it constructs the tree. In this index, index value is stored in the reverse direction when compared to the values in the actual data table. This index is used in order to distribute the index data evenly across all the leaf index nodes if table data is skewed very high. Like b*tree index, this index doesn’t understand NULL. This B*Tree Reverse index can be created on a single column or more than one column. More than one B*Tree Reverse index can be created for the same data table. HOW TO VERIFY: How to verify whether the created index is reverse index or not? Fire this query, select index_type from user_indexes where index_name = 'DEPT_ID_INDX' ð   This query would return “ NORMAL/REV ” (it means it is B*Tree index but of “Reverse” type) LITTLE-KNOWN FACTS TO BE REMEMBERED: ·          If this B*Tree Reve
Post a Comment
Read more

Nested Loop Outer Join

Before we go into this topic in detail, first we will understand what outer join is meant by. For an example, take 2 tables which have to be joined, say “Parent” table & “Child” table [I have taken these table names, only to discuss this topic in an easy way]. If Child table is outer joined with Parent table, then you can say, the matching records from both these tables will be retrieved and then the records which are available only in Parent table will also be retrieved though the corresponding matching records are not available in Child table. This is called outer join. [in natural or equi-join, only the matching records from both the tables will be retrieved.] To perform this outer join, 3 different join techniques have been provided by oracle. ·          Nested loop outer join ·          Hash outer join ·          Sort merge outer join In this, we will discuss about nested loop outer join. To perform this nested loop outer join, Oracle follows these steps (assume, child ta
Post a Comment
Read more

Index Fast Full Scan

Index Fast Full Scan is one of the index access methods supported by Oracle. Index Fast Full Scan is otherwise called as Fast Full Index Scan also. Index Fast Full Scan can be imposed by Oracle Optimizer only in the certain scenario. The scenario where this index would be invoked is, Whenever the Oracle SQL optimizer detects that the query is serviceable without touching table rows, Oracle invokes this fast full index scan and quickly reads every block of the index without touching the table itself provided that query doesn’t contain any ORDER BY clause. The definition for this scan is more or less similar to the index full scan but the only difference is that former  will be invoked when ORDER BY clause is not mentioned in the query. It differs from the index full scan in another way where output won’t be in the sorted order since ORDER BY clause is not mentioned. If Oracle detects the query is serviceable without touching the table rows, it means, all the columns mentioned in th
Post a Comment
Read more