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.

This entry was posted in Database Stuff, Life of a Oracle DBA by rlockard. Bookmark the permalink.

About rlockard

Robert Lockard is a professional Oracle Designer, Developer and DBA working in the world of financial intelligence. In 1987 his boss called him into his office and told him that he is now their Oracle Wizard then handed him a stack of Oracle tapes and told him to load it on the VAX. Sense then, Robert has worked exclusively as an Oracle database designer, developer and Database Administrator. Robert enjoys flying vintage aircraft, racing sailboats, photography, and technical diving. Robert owns and fly’s the “Spirit of Baltimore Hon” a restored 1948 Ryan Navion and lives in Glen Burnie Maryland on Marley Creek

Welcome to oraclewizard