RE: RANK+WINDOW NOSORT STOPKEY=> stopkey doesn't work
Date: Mon, 29 Feb 2016 14:12:17 +0000
Message-ID: <DUB408-EAS19821086E6C992D8F52D8ADA1BA0_at_phx.gbl>
There are 10000 rows at each rank #.
DENSE_RANK deals in consecutive ranks so 10000 in #1, 10000 at #2, etc RANK needs to know how many rows are in the current rank before it knows the next starting rank so 10000 at #1, 10000 at #10001, etc
I was trying to think why that would make a difference and I don't think it should.
Ignoring DENSE_RANK for a mo...
With RANK if you do RN <= 1, you get 10000 rows with a shortcut.
If you do RN <= 10000, you get 10000 rows without a shortcut.
It doesn't seem to be cost-based as both above have the same plan with STOPKEY & same cost.
So... Bug?
Sent from my Windows Phone
From: Dominic Brooks<mailto:dombrooks_at_hotmail.com> Sent: 29/02/2016 13:00
To: Dominic Brooks<mailto:dombrooks_at_hotmail.com>; xt.and.r_at_gmail.com<mailto:xt.and.r_at_gmail.com>; ORACLE-L<mailto:oracle-l_at_freelists.org> Subject: RE: RANK+WINDOW NOSORT STOPKEY=> stopkey doesn't work
Oh yes... I was messing with the value of the RN limit and that makes a difference.
Sent from my Windows Phone
From: Dominic Brooks<mailto:dombrooks_at_hotmail.com> Sent: 29/02/2016 12:49
To: Dominic Brooks<mailto:dombrooks_at_hotmail.com>; xt.and.r_at_gmail.com<mailto:xt.and.r_at_gmail.com>; ORACLE-L<mailto:oracle-l_at_freelists.org> Subject: RE: RANK+WINDOW NOSORT STOPKEY=> stopkey doesn't work
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_at_hotmail.com> Sent: 29/02/2016 12:44
To: xt.and.r_at_gmail.com<mailto:xt.and.r_at_gmail.com>; ORACLE-L<mailto:oracle-l_at_freelists.org> 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_at_gmail.com> Sent: 29/02/2016 11:22
To: ORACLE-L<mailto:oracle-l_at_freelists.org> 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 -- http://www.freelists.org/webpage/oracle-lReceived on Mon Feb 29 2016 - 15:12:17 CET