The Index Unique Scan is used when one unique piece of information (row) is needed. A primary key would be a perfect example. Since a primary key is 1) Not Null and 2) Unique. The uniqueness can be realized with a index…a unique index. This is actually a normal btree index, with the added rule that any value may appear only once in the index.
The Index Unique Scan is probably the most cost effective way to get to a piece of data. Oracle will get very effectivly get to the entry in the index, and then knows that it only have to visit the one block that the index-entry is pointing to (because of the uniqueness).
Let’s see the Index Unique Scan in action. The data used is described here.
Some statistics about the table and index:
(2020-10-31 12:10:16) MARTIJN@_DUN > select table_name,
num_rows,
blocks
from user_tables
where table_name='INDEX_DEMO';
TABLE_NAME NUM_ROWS BLOCKS
------------------------- ---------- ----------
INDEX_DEMO 100000 611
(2020-10-31 12:16:36) MARTIJN@_DUN > select column_name,
data_type,
num_distinct,
num_nulls
from user_tab_columns
where table_name='INDEX_DEMO';
COLUMN_NAM DATA_TYPE NUM_DISTINCT NUM_NULLS
---------- ---------- ------------ ----------
N NUMBER 100000 0
D NUMBER 1001 0
M NUMBER 100 0
S VARCHAR2 99840 0
(2020-10-31 12:34:20) MARTIJN@_DUN > select index_name,
uniqueness,
blevel,
leaf_blocks,
clustering_factor,
distinct_keys,
num_rows
from user_indexes
where table_name='INDEX_DEMO';
INDEX_NAME UNIQUENES BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR DISTINCT_KEYS NUM_ROWS
-------------------- --------- ---------- ----------- ----------------- ------------- ----------
INDEX_DEMO_U_N UNIQUE 1 208 593 100000 100000
INDEX_DEMO_D NONUNIQUE 1 208 595 1001 100000
INDEX_DEMO_S NONUNIQUE 1 516 99837 99840 100000
INDEX_DEMO_M NONUNIQUE 1 196 59268 100 100000
(2020-10-31 14:16:08) MARTIJN@_DUN > select index_name,
column_name index_on,
column_position pos
from user_ind_columns
where table_name='INDEX_DEMO';
INDEX_NAME INDEX_ON POS
-------------------- -------------------- ----------
INDEX_DEMO_M M 1
INDEX_DEMO_D D 1
INDEX_DEMO_S S 1
INDEX_DEMO_U_N N 1
I can tell a lot about above statistics…but I won’t 🙂
It suffices to say that we can see there is a unique index on column N (index_demo_u_n). This one would be perfectly suitable for an index unique scan.
Now select a single value out of column N (which has the unique index on it). My expectation is that a Index Unique Scan will be used:
(2020-10-31 14:21:27) MARTIJN@_DUN > explain plan for select * from index_demo where n=100;
(2020-10-31 14:23:26) MARTIJN@_DUN > select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 882761573
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| INDEX_DEMO | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | INDEX_DEMO_U_N | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N"=100)
(2020-10-31 14:23:52) MARTIJN@_DUN >
And my expectation is met: An Index Unique Scan is used to find a pointer to the rowid. And then the table is accessed with the found ROWID. The cost of the whole operation is 2. (I will get back on the calculation of the cost some other (far future) time.
Just for fun I will drop this index and will search again just to see what cost is when a FTS (FUll Table Scan) is used:
(2020-10-31 14:32:00) MARTIJN@_DUN > drop index index_demo_u_n;
(2020-10-31 14:32:08) MARTIJN@_DUN > explain plan for select * from index_demo where n=100;
(2020-10-31 14:32:16) MARTIJN@_DUN > select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1033748746
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 168 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| INDEX_DEMO | 1 | 38 | 168 (1)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N"=100)
(2020-10-31 14:32:20) MARTIJN@_DUN >
There you have it, th cost for the query is 168, that is 134 times more expensive !!!!
Let me conclude by saying…yes indeed, an Index Unique Scan is a very cost-effective way to get to your data.