RE: RANK+WINDOW NOSORT STOPKEY=> stopkey doesn't work

  • From: Dominic Brooks <dombrooks@xxxxxxxxxxx>
  • To: Dominic Brooks <dombrooks@xxxxxxxxxxx>, <xt.and.r@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 29 Feb 2016 14:12:17 +0000

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@xxxxxxxxxxx>
Sent: ‎29/‎02/‎2016 13:00
To: Dominic Brooks<mailto:dombrooks@xxxxxxxxxxx>; 
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

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@xxxxxxxxxxx>
Sent: ‎29/‎02/‎2016 12:49
To: Dominic Brooks<mailto:dombrooks@xxxxxxxxxxx>; 
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

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

Other related posts: