The title for this blog post is probably more catchy than the post itself but the election is close so every sort of campaign is allowed, right?
This post is another one of those “I knew it, but I forgot and got bitten back” blog post so hopefully next time I see it I’ll be quicker in recognizing such behavior.
The goal of the SQL is to quickly return the Top-N rows that match some filter condition(s), descendingly sorted by one of such columns. Pretty common requirement if you consider the filter/sort column to be a date one (“give me the last day worth of transactions, starting with the most recent ones”) and many people would solve using a DESC index on the date column.
drop table t purge;
create table t (pk number, owner varchar2(128), object_id number, data_object_id number, created date, object_name varchar2(128), object_type varchar2(23), large_column clob, random_stuff varchar2(20));
insert into t
select /*+ LEADING(A) */ rownum pk, a.owner, a.object_id, a.data_object_id,
(sysdate-31)+(rownum/65000) created, a.object_name, a.object_type,
to_clob(null) large_column, lpad('a',20,'a') random_stuff
from dba_objects a,
(select rownum from dual connect by rownum <= 20) b
where rownum <= 2000000;
exec dbms_stats.gather_table_stats(user,'T');
create index i_desc on t(created desc);
var from_d varchar2(100);
var to_d varchar2(100);
var maxrows number;
exec :from_d := '20161001';
exec :to_d := '20161002';
exec :maxrows := '25';
SELECT data.*, ROWNUM AS rn
FROM (SELECT /*+ INDEX(t i_desc) */ *
FROM t
WHERE created BETWEEN to_date(:from_d,'YYYYMMDD') AND to_date(:to_d,'YYYYMMDD')
ORDER BY created DESC) data
WHERE ROWNUM < :maxrows
Plan hash value: 2665129471
--------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows|A-Rows|Buffers|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 24| 50|
|* 1 | COUNT STOPKEY | | 1 | | 24| 50|
| 2 | VIEW | | 1 | 27| 24| 50|
|* 3 | FILTER | | 1 | | 24| 50|
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 65002| 24| 50|
|* 5 | INDEX RANGE SCAN | I_DESC| 1 | 3| 24| 26|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<:MAXROWS)
3 - filter(TO_DATE(:TO_D,'YYYYMMDD')>=TO_DATE(:FROM_D,'YYYYMMDD'))
5 - access("T"."SYS_NC00010$">=SYS_OP_DESCEND(TO_DATE(:TO_D,'YYYYMMDD')) AND
"T"."SYS_NC00010$"<=SYS_OP_DESCEND(TO_DATE(:FROM_D,'YYYYMMDD')))
filter((SYS_OP_UNDESCEND("T"."SYS_NC00010$")<=TO_DATE(:TO_D,'YYYYMMDD') AND
SYS_OP_UNDESCEND("T"."SYS_NC00010$")>=TO_DATE(:FROM_D,'YYYYMMDD')))
Execution plan has no SORT ORDER BY [STOPKEY in case case] thanks to the way values are stored in the index (DESC order) thus no blocking operation. As soon as 24 rows are retrieved from the index/table steps (aka they satisfy the filter conditions) they can be immediately returned to the user.
Your business goes well, data volume increase and so you decide to range partition table T by time and to make partition maintenance smooth you make index I_DESC LOCAL. SQL commands are the same as before except for the PARTITION clause in the CREATE TABLE and the LOCAL keyword in the CREATE INDEX.
drop table t purge;
create table t (pk number, owner varchar2(128), object_id number, data_object_id number, created date, object_name varchar2(128), object_type varchar2(23), large_column clob, random_stuff varchar2(20))
partition by range (created) interval (numtodsinterval(7,'day'))
(partition p1 values less than (to_date('2016-09-12 00:00:00', 'yyyy-mm-dd hh24:mi:ss')));
insert into t
select /*+ LEADING(A) */ rownum pk, a.owner, a.object_id, a.data_object_id,
(sysdate-31)+(rownum/65000) created, a.object_name, a.object_type,
to_clob(null) large_column, lpad('a',20,'a') random_stuff
from dba_objects a,
(select rownum from dual connect by rownum <= 20) b
where rownum <= 2000000;
exec dbms_stats.gather_table_stats(user,'T');
create index i_desc on t(created desc) local;
SELECT data.*, ROWNUM AS rn
FROM (SELECT /*+ INDEX(t i_desc) */ *
FROM t
WHERE created BETWEEN to_date(:from_d,'YYYYMMDD') AND to_date(:to_d,'YYYYMMDD')
ORDER BY created DESC) data
WHERE ROWNUM < :maxrows;
-------------------------------------------------------------------------------------------
| Id |Operation |Name |Starts|E-Rows|A-Rows|Buffers|
-------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 1| | 24| 855|
|* 1| COUNT STOPKEY | | 1| | 24| 855|
| 2| VIEW | | 1| 27| 24| 855|
|* 3| SORT ORDER BY STOPKEY | | 1| 27| 24| 855|
|* 4| FILTER | | 1| | 65001| 855|
| 5| PARTITION RANGE ITERATOR | | 1| 65002| 65001| 855|
| 6| TABLE ACCESS BY LOCAL INDEX ROWID BATCHED|T | 1| 65002| 65001| 855|
|* 7| INDEX RANGE SCAN |I_DESC | 1| 2| 65001| 187|
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<:MAXROWS)
3 - filter(ROWNUM<:MAXROWS)
4 - filter(TO_DATE(:TO_D,'YYYYMMDD')>=TO_DATE(:FROM_D,'YYYYMMDD'))
7 - access("T"."SYS_NC00010$">=SYS_OP_DESCEND(TO_DATE(:TO_D,'YYYYMMDD')) AND
"T"."SYS_NC00010$"<=SYS_OP_DESCEND(TO_DATE(:FROM_D,'YYYYMMDD')))
filter((SYS_OP_UNDESCEND("T"."SYS_NC00010$")>=TO_DATE(:FROM_D,'YYYYMMDD') AND
SYS_OP_UNDESCEND("T"."SYS_NC00010$")<=TO_DATE(:TO_D,'YYYYMMDD')))
Unfortunately this time the plan DOES include a SORT ORDER BY STOPKEY and the effect is evident, ~65k rows are retrieved before the top 24 are returned back to the user.
Why is that? Let’s hold the question for a second and check first if an ASC index would make a difference here.
Do we have the same behavior if instead of scanning the I_DESC index using a regular “INDEX RANGE SCAN” we do a “INDEX RANGE SCAN DESCENDING” on an index defined as ASC?
Same SQL commands as before except the index is defined ASC and the hint now becomes INDEX_DESC.
create index i_asc on t(created) local;
SELECT data.*, ROWNUM AS rn
FROM (SELECT /*+ INDEX_DESC(t i_asc) */ *
FROM t
WHERE created BETWEEN to_date(:from_d,'YYYYMMDD') AND to_date(:to_d,'YYYYMMDD')
ORDER BY created DESC) data
WHERE ROWNUM < :maxrows;
---------------------------------------------------------------------------------------
| Id |Operation | Name |Starts|E-Rows|Pstart|Pstop|A-Rows|
---------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 1| | | | 24|
|* 1| COUNT STOPKEY | | 1| | | | 24|
| 2| VIEW | | 1| 27| | | 24|
|* 3| FILTER | | 1| | | | 24|
| 4| PARTITION RANGE ITERATOR | | 1| 27| KEY | KEY| 24|
| 5| TABLE ACCESS BY LOCAL INDEX ROWID| T | 1| 27| KEY | KEY| 24|
|* 6| INDEX RANGE SCAN DESCENDING | I_ASC | 1| 65002| KEY | KEY| 24|
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<:MAXROWS)
3 - filter(TO_DATE(:TO_D,'YYYYMMDDHH24')>=TO_DATE(:FROM_D,'YYYYMMDD'))
6 - access("CREATED"<=TO_DATE(:TO_D,'YYYYMMDD') AND "CREATED">=TO_DATE(:FROM_D,'YYYYMMDD'))
filter(("CREATED">=TO_DATE(:FROM_D,'YYYYMMDD') AND "CREATED"<=TO_DATE(:TO_D,'YYYYMMDD')))
Expected plan again, without SORT step, that returns 24 rows as soon as they are extracted from the index/table step.
So to answer the initial question, is there a difference?
Yes, when using a “INDEX RANGE SCAN DESCENDING” of I_ASC index the database didn’t include the SORT step. On the other hand when doing an “INDEX RANGE SCAN” of I_DESC index the SORT step is included and it affects the performance of the SQL.
Why when using the DESC index there is a SORT step?
I can’t imagine why the database needs to introduce that SORT step, beside restriction / limitations in the code (but this is based on limited knowledge so easy to be wrong here).
My guess is the additional complexity of the DESC index code / filters combined with bind variables limit the ability to recognize the SORT is unnecessary.
Reason for saying this it’s anytime Oracle does dynamic partitioning pruning (because of the binds, aka Pstart/Pstop KEY/KEY) the SORT step is present even though such step is not there when using “INDEX RANGE SCAN DESCENDING” on I_ASC index still in presence of binds. Also the SORT disappears when using “INDEX RANGE SCAN” on I_DESC index if the binds are replaced with literals.
One suspect was that the PARTITION RANGE ITERATOR step was consistently being executed in ASCENDING way, if that was the case then the SORT would make sense because the data would be returned “partially” sorted, meaning desc sorted only within each partition. But this is not the case, event 10128 (details here) show the partitions are accessed in DESCENDING order
Partition Iterator Information: partition level = PARTITION call time = RUN order = DESCENDING Partition iterator for level 1: iterator = RANGE [3, 4] index = 4 current partition: part# = 4, subp# = 1048576, abs# = 4 current partition: part# = 3, subp# = 1048576, abs# = 3
That’s why the (educated guesssed) conclusion that the SORT step is just caused my some limitations rather than being necessary here, corrections are very welcome here!!!
What just said adds to the very good reasons to question a DESC index on a single column to begin with, reported by Richard Foote here.
BTW apologies for the DDL not super-clean, they were just inherited from another example I was working on and came in handy 😀
Mauro Pagano's Blog 