Re: Index not used to avoid sort with calculated column
Date: Wed, 26 Jun 2019 21:31:25 +0200
Message-ID: <CALH8A93y+0y+LJBA7qucEJ-2Jq6oBvT9Y2NHphji9Ah83smMOA_at_mail.gmail.com>
Thank you Tanel for the explanation
(and sorry for my empty email).
From a higher perspective, the order of activities is a bigger problem. in the execution plans it goes
- INDEX RANGE SCAN
- TABLE ACCESS BY INDEX ROWID (BATCHED)
- WINDOW (NO)SORT...
- VIEW / SELECT
- INDEX RANGE SCAN
- WINDOW (NO)SORT...
- TABLE ACCESS BY INDEX ROWID (BATCHED)
- VIEW / SELECT
I assume the optimizer is not aware of this strategy, so a subquery with NO_MERGE will be the only method I can imagine.
I'm not sure if this is worth to rewrite Lothars original query.
Martin
Am Mi., 26. Juni 2019 um 20:45 Uhr schrieb Martin Berger < martin.a.berger_at_gmail.com>:
>
>
> 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-lReceived on Wed Jun 26 2019 - 21:31:25 CEST