Index not used to avoid sort with calculated column

 My testcase below is based on the Scott schema.  

 create index e_sort1 on emp (job, hiredate);  

 create index e_low_sort1 on emp (lower(job), hiredate);     

 Index e_sort1 can be used to avoid sorting.           

 select * from emp where job='CLERK' order by hiredate fetch first 2 rows only;      

|   0 | SELECT STATEMENT              |         |       |       |     2 (100)|          |


|*  1 |  VIEW                         |         |     2 |   218 |     2   (0)| 00:00:01 |


|*  2 |   WINDOW NOSORT STOPKEY       |         |     4 |   348 |     2   (0)| 00:00:01 |


|   3 |    TABLE ACCESS BY INDEX ROWID| EMP     |     4 |   348 |     2   (0)| 00:00:01 |


|*  4 |     INDEX RANGE SCAN          | E_SORT1 |     1 |       |     1   (0)| 00:00:01 |


-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=2)    2 - filter(ROW_NUMBER() OVER ( ORDER BY "EMP"."HIREDATE")<=2)  

    4 - access("JOB"='CLERK')     

 The function based index can not be used to avoid sorting.  select * from emp where lower(job)=lower('CLERK') order by hiredate fetch first 2 rows only;        

| Id  | Operation                             | Name        | Rows  | Bytes | Cost (%CPU)| Time     | 
----------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT                      |             |       |       |     2 (100)|          | 


|*  1 |  VIEW                                 |             |     2 |   218 |     2   (0)| 00:00:01 | 


|*  2 |   WINDOW SORT PUSHED RANK             |             |     4 |   372 |     2   (0)| 00:00:01 | 


|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMP         |     4 |   372 |     2   (0)| 00:00:01 | 


|*  4 |     INDEX RANGE SCAN                  | E_LOW_SORT1 |     1 |       |     1   (0)| 00:00:01 | 


----------------------------------------------------------------------------------------------------- 
  

Predicate Information (identified by operation id):

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=2) 
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "EMP"."HIREDATE")<=2) 
   4 - access("EMP"."SYS_NC00009$"='clerk') 
 
  

 What do I miss out here ?  

 Thanks & Regards  

 Lothar

--
http://www.freelists.org/webpage/oracle-l