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

A index range scan gets the rowid by getting the adjacent index entries.  Once Oracle has gathered the rowid’s the rows are returned in ascending order. Unless you use sort descending.

image

From our previous example we saw that if you use a equity predicate on a non-unique index, Oracle will do a index range scan.

image

An index range scan will also be done if your query has a range in the predicate.

Lets drop the normal btree index we created and create a unique index on t1 (object_id).

image

We used a between clause and would up getting a index range scan on t1_idx.

But what if we did this a little different, lets create an index on owner and use an equity predicate.

image

The first thing we notice is we are still doing an index range scan.  We analyzed the table, we analyzed the index but still the optimizer got the cardinality estimate all wrong. We are going to come back and revisit this problem in a later post.  You really need to get the cardinality right, Maria Colgan of Oracle Optimizer fame speaks frequently on this subject.

What are the where clauses that can cause the optimizer to do a index range scan?

LIKE, >, <, =, BETWEEN

You should also realize a index range scan can be bound or un-bound.

Examples of bound:

WHERE column = ‘x’

WHERE column > 1 and column < 5

WHERE column LIKE ‘x%’

WHERE column between 1 and 5

Examples of unbound:

WHERE column > 5

WHERE column < 5

We are going to come back after we address access paths, join methods and join orders to talk about histograms and getting cardinality just right.

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