Index Unique Scans returns at most 1 rowid, this means that whenever the condition in WHERE clause guarantees that only one row would be returned Oracle optimizer chooses Index Unique Scans.

To summarize this, Index Unique Scan access path is used when all columns of a unique (B-tree) index or an index created as a result of a primary key constraint are specified with equality conditions.

SQL_ID  3uxq5tsd5x2gd, child number 0
-------------------------------------
select * from products where prod_id = 19
 
Plan hash value: 4047888317
--------------------------------------------------------------------------------
| Id| Operation                   | Name        |Rows|Bytes|Cost (%CPU)|Time   |
--------------------------------------------------------------------------------
|  0| SELECT STATEMENT            |             |    |     |  1 (100)|         |
|  1|  TABLE ACCESS BY INDEX ROWID| PRODUCTS    |  1 | 173 |  1   (0)| 00:00:01|
|* 2|   INDEX UNIQUE SCAN         | PRODUCTS_PK |  1 |     |  0   (0)|         |
--------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("PROD_ID"=19)