Why is Oracle ignoring index with ORDER BY?

more than likely your FIRST_NAME column is nullable.

SQL> create table customer (first_name varchar2(20), last_name varchar2(20));Table created.SQL> insert into customer select dbms_random.string('U', 20), dbms_random.string('U', 20) from dual connect by level <= 100000;100000 rows created.SQL> create index c on customer(first_name);Index created.SQL> explain plan for select * from (  2    select /*+ FIRST_ROWS(20) */ FIRST_NAME, ROW_NUMBER() over (order by FIRST_NAME) RN  3    from CUSTOMER C  4  )  5  where RN between 1 and 20  6  order by RN;Explained.SQL> @explain ""Plan hash value: 1474094583----------------------------------------------------------------------------------------------| Id  | Operation                 | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |----------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT          |          |   117K|  2856K|       |  1592   (1)| 00:00:20 ||   1 |  SORT ORDER BY            |          |   117K|  2856K|  4152K|  1592   (1)| 00:00:20 ||*  2 |   VIEW                    |          |   117K|  2856K|       |   744   (2)| 00:00:09 ||*  3 |    WINDOW SORT PUSHED RANK|          |   117K|  1371K|  2304K|   744   (2)| 00:00:09 ||   4 |     TABLE ACCESS FULL     | CUSTOMER |   117K|  1371K|       |   205   (1)| 00:00:03 |----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("RN">=1 AND "RN"<=20)   3 - filter(ROW_NUMBER() OVER ( ORDER BY "FIRST_NAME")<=20)Note-----   - dynamic sampling used for this statement (level=2)21 rows selected.SQL> alter table customer modify first_name not null;Table altered.SQL> explain plan for select * from (  2    select /*+ FIRST_ROWS(20) */ FIRST_NAME, ROW_NUMBER() over (order by FIRST_NAME) RN  3    from CUSTOMER C  4  )  5  where RN between 1 and 20  6  order by RN;Explained.SQL> @explain ""Plan hash value: 1725028138----------------------------------------------------------------------------------------| Id  | Operation               | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT        |      |   117K|  2856K|       |   850   (1)| 00:00:11 ||   1 |  SORT ORDER BY          |      |   117K|  2856K|  4152K|   850   (1)| 00:00:11 ||*  2 |   VIEW                  |      |   117K|  2856K|       |     2   (0)| 00:00:01 ||*  3 |    WINDOW NOSORT STOPKEY|      |   117K|  1371K|       |     2   (0)| 00:00:01 ||   4 |     INDEX FULL SCAN     | C    |   117K|  1371K|       |     2   (0)| 00:00:01 |----------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("RN">=1 AND "RN"<=20)   3 - filter(ROW_NUMBER() OVER ( ORDER BY "FIRST_NAME")<=20)Note-----   - dynamic sampling used for this statement (level=2)21 rows selected.SQL>

add a NOT NULL in there to resolve it.

SQL> explain plan for select * from (  2    select /*+ FIRST_ROWS(20) */ FIRST_NAME, ROW_NUMBER() over (order by FIRST_NAME) RN  3    from CUSTOMER C  4    where first_name is not null  5  )  6  where RN between 1 and 20  7  order by RN;Explained.SQL> @explain ""Plan hash value: 1725028138----------------------------------------------------------------------------------------| Id  | Operation               | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT        |      |   117K|  2856K|       |   850   (1)| 00:00:11 ||   1 |  SORT ORDER BY          |      |   117K|  2856K|  4152K|   850   (1)| 00:00:11 ||*  2 |   VIEW                  |      |   117K|  2856K|       |     2   (0)| 00:00:01 ||*  3 |    WINDOW NOSORT STOPKEY|      |   117K|  1371K|       |     2   (0)| 00:00:01 ||*  4 |     INDEX FULL SCAN     | C    |   117K|  1371K|       |     2   (0)| 00:00:01 |----------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("RN">=1 AND "RN"<=20)   3 - filter(ROW_NUMBER() OVER ( ORDER BY "FIRST_NAME")<=20)   4 - filter("FIRST_NAME" IS NOT NULL)Note-----   - dynamic sampling used for this statement (level=2)22 rows selected.SQL>

You're querying for more columns than first_name. The index on first_name just contains the first_name column and a reference to the table. So to retrieve the other columns, Oracle has to perform a lookup to the table itself for each row. Most databases try to avoid this if they can't guarantee a low record count.

A database is typically not smart enough to know the effects of a where clause on a row_number column. However, your hint /*+ FIRST_ROWS(20) */ might have done the trick.

Perhaps the table is really small, so that Oracle expects the table scan to be cheaper than lookups, even for just 20 rows.