Re: Index not used to avoid sort with calculated column

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

| Id  | Operation                     | Name        | E-Rows |E-Bytes| Cost

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