Ignore... I must have done something stupid. Now I get results consistent with
yours in all 3.
Sent from my Windows Phone
________________________________
From: Dominic Brooks<mailto:dombrooks@xxxxxxxxxxx>
Sent: 29/02/2016 12:44
To: xt.and.r@xxxxxxxxx<mailto:xt.and.r@xxxxxxxxx>;
ORACLE-L<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: RE: RANK+WINDOW NOSORT STOPKEY=> stopkey doesn't work
Seems to be an 11.2.0.4 thing?
11.2.0.3 and 12.1.0.2 were both as expected for me at least.
Sent from my Windows Phone
________________________________
From: Sayan Malakshinov<mailto:xt.and.r@xxxxxxxxx>
Sent: 29/02/2016 11:22
To: ORACLE-L<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: RANK+WINDOW NOSORT STOPKEY=> stopkey doesn't work
Hi list,
Anybody knows why STOPKEY doesn't work with RANK()over(), though it works
fine with row_number() and dense_rank()?
I have simple test case:
http://orasql.org/scripts/stopkey/denserank.sql
http://orasql.org/scripts/stopkey/rank.sql
I've created a table with index:
create table xt_test(dt not null) as
with n10000 as (select level n from dual connect by level<=10000)
,n10 as (select level n from dual connect by level<=10)
select date'2016-01-01'+n10.n dt
from n10000, n10
/
create index ix_test on xt_test(dt);
And if we use DENSE_RANK, we can see that stopkey works fine and query
executions stops when DENSE_RANK is bigger than needed:
SELECT/*+ gather_plan_statistics denserank */ DT, RID FROM (SELECT/*+
index(t (dt)) */ DT , ROWID RID , DENSE_RANK()OVER(ORDER BY DT) RN FROM
XT_TEST T ) WHERE RN<=2
Plan hash value: 1892911073
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost
(%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 160
(100)| | 20000 |00:00:00.06 | 253 |
|* 1 | VIEW | | 1 | 100K| 160
(0)| 00:00:02 | 20000 |00:00:00.06 | 253 |
|* 2 | WINDOW NOSORT STOPKEY| | 1 | 100K| 160
(0)| 00:00:02 | 20000 |00:00:00.04 | 253 |
| 3 | INDEX FULL SCAN | IX_TEST | 1 | 100K| 160
(0)| 00:00:02 | 20001 |00:00:00.02 | 253 |
--------------------------------------------------------------------------------------------------------------------
But if we use RANK(), it doesn't stop and fetches all rows from index:
SELECT/*+ gather_plan_statistics rank */ DT, RID FROM (SELECT/*+
index(t (dt)) */ DT , ROWID RID , RANK()OVER(ORDER BY DT) RN FROM
XT_TEST T ) WHERE RN<=20000
Plan hash value: 1892911073
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)|
E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 160 (100)|
| 20000 |00:00:00.04 | 465 |
|* 1 | VIEW | | 1 | 100K| 160 (0)|
00:00:02 | 20000 |00:00:00.04 | 465 |
|* 2 | WINDOW NOSORT STOPKEY| | 1 | 100K| 160 (0)|
00:00:02 | 100K|00:00:00.14 | 465 |
| 3 | INDEX FULL SCAN | IX_TEST | 1 | 100K| 160 (0)|
00:00:02 | 100K|00:00:00.04 | 465 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"<=20000)
2 - filter(RANK() OVER ( ORDER BY "DT")<=20000)
--
Best regards,
Sayan Malakshinov
http://orasql.org