How to force oracle to use index range scan?

indexing, oracle

Solution

To "force" Oracle to use an index range scan, simply use an optimizer hint `INDEX_RS_ASC`. For example:

CREATE TABLE mytable (a NUMBER NOT NULL, b NUMBER NOT NULL, c CHAR(10)) NOLOGGING;

INSERT /*+ APPEND */ INTO mytable(a,b,c) 
SELECT level, mod(level,100)+1, 'a'  FROM dual CONNECT BY level <= 1E6;

CREATE INDEX myindex_ba ON mytable(b, a);
EXECUTE dbms_stats.gather_table_stats(NULL,'mytable');

SELECT /*+ FULL(m)         */ b FROM mytable m WHERE b=10; -- full table scan
SELECT /*+ INDEX_RS_ASC(m) */ b FROM mytable m WHERE b=10; -- index range scan
SELECT /*+ INDEX_FFS(m)    */ b FROM mytable m WHERE b=10; -- index fast full scan

Whether this will make your query actually run faster depends on many factors like the selectivity of the indexed value or the physical order of the rows in your table. For instance, if you change the query to `WHERE b BETWEEN 10 AND <xxx>`, the following costs appear in the execution plans on my machine:

b BETWEEN 10 AND    10     20      40     80
FULL               749    750     751    752
INDEX_RS_ASC        29    325     865   1943
INDEX_FFS          597    598     599    601

If you change the query very slightly to not only select the indexed column `b`, but also other, non-index columns, the costs change dramatically:

b BETWEEN 10 AND    10     20      40     80
FULL               749    750     751    754
INDEX_RS_ASC      3352  40540  108215 243563
INDEX_FFS         3352  40540  108215 243563

Problem

I have a series of extremely similar queries that I run against a table of 1.4 billion records (with indexes), the only problem is that at least 10% of those queries take > 100x more time to execute than others. I ran an explain plan and noticed that the for the fast queries (roughly 90%) Oracle is using an index range scan; on the slow ones, it's using a full index scan. Is there a way to force Oracle to do an index range scan?

Original source