How to avoid sort using index

This one is not so well known, largely because it is so poorly documented (and in many cases, unpredicatably implemented by the Optimizer as well). Oracle performs a sort for many reasons: ORDER BY, GROUP BY, DISTINCT, Set operations (eg. UNION), Sort-Merge Joins, uncorrelated IN-subqueries, Analytic Functions). If a sort operation requires rows in the same order as the index, then Oracle may read the table rows via the index. A sort operation is not necessary since the rows are returned in sorted order.
Despite all of the instances listed above where a sort is performed, I have only seen three cases where a sort is actually avoided.

1. GROUP BY

     1  select src_sys, sum(actl_expns_amt), count(*)
     2  from ef_actl_expns
     3  where src_sys = 'CDW'
     4  and actl_expns_amt > 0
     5* group by src_sys
   
   -------------------------------------------------------------
   | Id  | Operation                           | Name          |
   -------------------------------------------------------------
   |   0 | SELECT STATEMENT                    |               |
   |   1 |  SORT GROUP BY NOSORT               |               |
   |*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| EF_ACTL_EXPNS |
   |*  3 |    INDEX RANGE SCAN                 | EF_AEXP_PK    |
   -------------------------------------------------------------
   
   Predicate Information (identified by operation id):
   ---------------------------------------------------
   
      2 - filter("ACTL_EXPNS_AMT">0)
      3 - access("SRC_SYS"='CDW')

   Note the NOSORT qualifier in Step 1.
   
   
2. ORDER BY

     1  select *
     2  from ef_actl_expns
     3  where src_sys = 'CDW'
     4  and actl_expns_amt > 0
     5* order by src_sys
   
   ------------------------------------------------------------
   | Id  | Operation                          | Name          |
   ------------------------------------------------------------
   |   0 | SELECT STATEMENT                   |               |
   |*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| EF_ACTL_EXPNS |
   |*  2 |   INDEX RANGE SCAN                 | EF_AEXP_PK    |
   ------------------------------------------------------------
   
   Predicate Information (identified by operation id):
   ---------------------------------------------------
   
      1 - filter("ACTL_EXPNS_AMT">0)
      2 - access("SRC_SYS"='CDW')
   

   Note that there is no SORT operation, despite the ORDER BY clause. Compare this to the following:
   

     1  select *
     2  from ef_actl_expns
     3  where src_sys = 'CDW'
     4  and actl_expns_amt > 0
     5* order by actl_expns_amt
   
   -------------------------------------------------------------
   | Id  | Operation                           | Name          |
   -------------------------------------------------------------
   |   0 | SELECT STATEMENT                    |               |
   |   1 |  SORT ORDER BY                      |               |
   |*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| EF_ACTL_EXPNS |
   |*  3 |    INDEX RANGE SCAN                 | EF_AEXP_PK    |
   -------------------------------------------------------------
   
   Predicate Information (identified by operation id):
   ---------------------------------------------------
   
      2 - filter("ACTL_EXPNS_AMT">0)
      3 - access("SRC_SYS"='CDW')

3. DISTINCT

     1  select distinct src_sys
     2  from ef_actl_expns
     3  where src_sys = 'CDW'
     4* and actl_expns_amt > 0
   
   -------------------------------------------------------------
   | Id  | Operation                           | Name          |
   -------------------------------------------------------------
   |   0 | SELECT STATEMENT                    |               |
   |   1 |  SORT UNIQUE NOSORT                 |               |
   |*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| EF_ACTL_EXPNS |
   |*  3 |    INDEX RANGE SCAN                 | EF_AEXP_PK    |
   -------------------------------------------------------------
   
   Predicate Information (identified by operation id):
   ---------------------------------------------------
   
      2 - filter("ACTL_EXPNS_AMT">0)
      3 - access("SRC_SYS"='CDW')

   Again, note the NOSORT qualifier.
   

This is an extraordinary tuning technique in OLTP systems. A SQL with a DISTINCT, GROUP BY, or ORDER BY that uses an index to sort can return just the first page of matching rows without having to fetch the entire result set for a sort. This can be the difference between sub-second response time and several minutes or hours.