Thanks Martin,
this is of course correct. It means to change the statement though. You know
how happy software companies are on that proposal.
I even got a Patent on the optimizer to produce such behaviour without changing
the statement.
So far it has not been implemented.
Regards
Lothar
----Ursprüngliche Nachricht----
Von : martin.a.berger@xxxxxxxxx
Datum : 26/06/2019 - 21:31 (MS)
An : tanel@xxxxxxxxxxxxxx
Cc : l.flatz@xxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
Betreff : Re: Index not used to avoid sort with calculated column
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@xxxxxxxxx>:
Am Mi., 26. Juni 2019 um 18:22 Uhr schrieb Tanel Poder <
tanel@xxxxxxxxxxxxxx>:
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
