Life of an Oracle DBA – The Basics Access Paths: Index Unique Scan

The index unique scan is good for getting one row from and index and returning the rowid. You will get a index unique scan if you have a unique index or primary key on a table.

image
In this example; we created an unique index on object_id.  When we executed the query, the optimizer did a index unique scan.
image
And the index unique scan did just what it was designed to do, select one row.
Now lets try this with a normal btree index.

SQL> drop index t1_idx;

Index dropped.

SQL> create index t1_idx on t1(object_id);

Index created.

SQL> analyze index t1_idx compute statistics;

Index analyzed.

As you can see here, because we uses a normal index thimagee The optimizer decided to do a index range scan.

Next we will be covering Index range scan.

Leave a Reply