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.
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.
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).
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.
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.