Hi,
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
