Re: Index not used to avoid sort with calculated column

Am Mi., 26. Juni 2019 um 18:22 Uhr schrieb Tanel Poder <tanel_at_tanelpoder.com
>:

> 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> _at_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_at_bluewin.ch <l.flatz_at_bluewin.ch>
> 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
>>
>>

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