The function-based-index column -> virtual column transformation causes
trouble here. You can set the virtual column replacement feature to false
and try again:
SQL> ALTER SESSION SET "*_replace_virtual_columns*"=false;
Session altered.
SQL> select * from emp where lower(job)=lower('CLERK') order by hiredate
fetch first 2 rows only;
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ----------
---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800
20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950
30
SQL> @x
Display execution plan for last statement for this session from library
cache...
-----------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost
(%CPU)|
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
3 (100)|
|* 1 | VIEW | | 2 | 218 |
3 (34)|
|* 2 | WINDOW *NOSORT* STOPKEY | | 1 | 38 |
3 (34)|
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 |
2 (0)|
|* 4 | INDEX RANGE SCAN | E_LOW_SORT1 | 1 | |
1 (0)|
-----------------------------------------------------------------------------------
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')
This MOS note describes the issue:
- Behavior of "_replace_virtual_columns" Parameter in Respect to
Function Based Indexes (Doc ID 1643178.1)
Also, should be aware of bugs & issues in some versions:
- Wrong result/Query creash on evaopn2/evaopn3 when
_replace_virtual_columns is FALSE (Doc ID 1537939.1)
--
Tanel Poder
https://blog.tanelpoder.com/seminar
On Wed, Jun 26, 2019 at 9:20 AM l.flatz@xxxxxxxxxx <l.flatz@xxxxxxxxxx>
wrote:
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
