Re: Re: Index not used to avoid sort with calculated column
From: <l.flatz_at_bluewin.ch>
Date: Thu, 27 Jun 2019 09:57:02 +0200 (CEST)
Message-ID: <1124426009.8997.1561622222236_at_bluewin.ch>
Regards
Lothar
----Ursprüngliche Nachricht----
Von : tanel_at_tanelpoder.com
Datum : 27/06/2019 - 01:00 (MS)
An : martin.a.berger_at_gmail.com
Cc : tanel_at_tanelpoder.com, l.flatz_at_bluewin.ch, oracle-l_at_freelists.org Betreff : Re: Index not used to avoid sort with calculated column
Date: Thu, 27 Jun 2019 09:57:02 +0200 (CEST)
Message-ID: <1124426009.8997.1561622222236_at_bluewin.ch>
Hi Tanel,
thanks for your help. I is good to have collegues who can help. I was suspecting something like that, but I am up to my neck in work and could not affort testing on suspicion.
As from the doc I think going for the virtual column being preferrable. It avoids using an underscore parameter.
drop index e_low_sort1;
alter table emp add (low_job as (lower(job)) virtual);
create index e_low_sort1 on emp (low_job, hiredate);
select * from emp e where lower(job) =lower('CLERK') order by lower(job), hiredate fetch first 2 rows only;
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | VIEW | | 2 | 242 | 3 (34)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY | | 1 | 45 | 3 (34)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 45 | 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 "E"."LOW_JOB","E"."HIREDATE")<=2)
4 - access("E"."LOW_JOB"='clerk')
Regards
Lothar
----Ursprüngliche Nachricht----
Von : tanel_at_tanelpoder.com
Datum : 27/06/2019 - 01:00 (MS)
An : martin.a.berger_at_gmail.com
Cc : tanel_at_tanelpoder.com, l.flatz_at_bluewin.ch, oracle-l_at_freelists.org Betreff : Re: Index not used to avoid sort with calculated column
Yep, in the current plan structure, the WINDOW (NO)SORT has to project all required columns for the window function before returning anything back. So, if accessing columns that are not present in the index alone, you need both index + table access happen in the pipeline before the WINDOW operator kicks in. The WINDOW operator itself is not capable of fetching rows/remaining columns from the table. As you said, a separate table access step would be needed if the index/window processing needs to be as "narrow" as possible.
Something like this:
select * from emp where rowid in (
select rowid from emp where
lower(job)=lower('CLERK')
order by hiredate fetch first 2 rows only
)
Plan hash value: 2983619680
---------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)|
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)|
| 1 | NESTED LOOPS | | 2 | 126 | 4 (25)|
|* 2 | VIEW | | 2 | 50 | 2 (50)|
|* 3 | WINDOW NOSORT STOPKEY | | 1 | 28 | 2 (50)|
|* 4 | INDEX RANGE SCAN | E_LOW_SORT1 | 1 | 28 | 1 (0)|
| 5 | TABLE ACCESS BY USER ROWID| EMP | 1 | 38 | 1 (0)|
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=2)
3 - filter(ROW_NUMBER() OVER ( ORDER BY "HIREDATE")<=2)
4 - access("EMP"."SYS_NC00009$"='clerk')
- this is an adaptive plan
Although now that this is an adaptive plan due to the semi-join, would not be fun to have it turn into a hash join + full table scan instead of the index loop later on :-) As you said, a properly placed hint (NO_UNNEST in this case) would be one way to avoid this. Or just force UNNEST + NL join from start.
--
Tanel Poder
https://blog.tanelpoder.com/seminar
On Wed, Jun 26, 2019 at 3:31 PM Martin Berger < martin.a.berger_at_gmail.com> wrote:
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
1)
INDEX RANGE SCAN
2)
TABLE ACCESS BY INDEX ROWID (BATCHED)
3)
WINDOW (NO)SORT...
4) VIEW / SELECT
The order
1)
INDEX RANGE SCAN
2)
WINDOW (NO)SORT...
3)
TABLE ACCESS BY INDEX ROWID (BATCHED)
4) VIEW / SELECT
would make more sense: If only the small amount of data retrieved from the index is used for sorting, the impact of (no)sort is much smaller.
Afterwards only those rows from the table are accessed which are really needed. Here again the impact of BATCHED (or not) should not be big, especially if FETCH FIRST n the <n> is small.
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.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jun 27 2019 - 09:57:02 CEST