title: Why is Oracle ignoring index with ORDER BY?
body: <p>My intention is to obtain a paginated resultset of customers. I am using this algorithm, from <a href="http://www.oracle.com/technetwork/issue-archive/2007/07-jan/o17asktom-093877.html">Tom</a>:</p>
  
  <pre><code>select * from (
    select /*+ FIRST_ROWS(20) */ FIRST_NAME, ROW_NUMBER() over (order by FIRST_NAME) RN
    from CUSTOMER C
  )
  where RN between 1 and 20
  order by RN;
  </code></pre>
  
  <p>I also have an index defined on the column "CUSTOMER"."FIRST_NAME":</p>
  
  <pre><code>CREATE INDEX CUSTOMER_FIRST_NAME_TEST ON CUSTOMER (FIRST_NAME ASC);
  </code></pre>
  
  <p>The query returns the expected resultset, but from the explain plan I notice that the index is not used:</p>
  
  <pre><code>--------------------------------------------------------------------------------------
  | Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
  --------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT          |          | 15467 |   679K|   157   (3)| 00:00:02 |
  |   1 |  SORT ORDER BY            |          | 15467 |   679K|   157   (3)| 00:00:02 |
  |*  2 |   VIEW                    |          | 15467 |   679K|   155   (2)| 00:00:02 |
  |*  3 |    WINDOW SORT PUSHED RANK|          | 15467 |   151K|   155   (2)| 00:00:02 |
  |   4 |     TABLE ACCESS FULL     | CUSTOMER | 15467 |   151K|   154   (1)| 00:00:02 |
  --------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     2 - filter("RN"&gt;=1 AND "RN"&lt;=20)
     3 - filter(ROW_NUMBER() OVER ( ORDER BY "FIRST_NAME")&lt;=20)
  </code></pre>
  
  <p>I am using Oracle 11g. Since I just query for the first 20 rows, ordered by the indexed column, I would expect the index to be used.</p>
  
  <p>Why is the Oracle optimizer ignoring the index? I assume it's something wrong with the pagination algorithm, but I can't figure out what.</p>
  
  <p>Thanks.</p>