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

From: Dominic Brooks <dombrooks_at_hotmail.com>
Date: Mon, 29 Feb 2016 12:48:06 +0000
Message-ID: <DUB408-EAS390AF2CA3E93F94D2A72BBFA1BA0_at_phx.gbl>

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-l

Received on Mon Feb 29 2016 - 13:48:06 CET