Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from puck1183.startdedicated.com (localhost [127.0.0.1]) by puck1183.startdedicated.com (Postfix) with ESMTP id E819A19605AB for ; Mon, 29 Feb 2016 15:14:14 +0100 (CET) Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by puck1183.startdedicated.com (Postfix) with ESMTPS for ; Mon, 29 Feb 2016 15:14:14 +0100 (CET) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D8CE23350E; Mon, 29 Feb 2016 09:13:58 -0500 (EST) X-Virus-Scanned: Debian amavisd-new at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id EeDK0sJ5oN3R; Mon, 29 Feb 2016 09:13:58 -0500 (EST) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 39E4631609; Mon, 29 Feb 2016 09:13:18 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 29 Feb 2016 09:12:37 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 237242F846 for ; Mon, 29 Feb 2016 09:12:37 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id kIFsbp8w6ZUr for ; Mon, 29 Feb 2016 09:12:37 -0500 (EST) Received: from DUB004-OMC1S25.hotmail.com (dub004-omc1s25.hotmail.com [157.55.0.224]) (using TLSv1 with cipher AES256-SHA (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 7DF2E2F6DA for ; Mon, 29 Feb 2016 09:12:36 -0500 (EST) Received: from DUB408-EAS198 ([157.55.0.239]) by DUB004-OMC1S25.hotmail.com over TLS secured channel with Microsoft SMTPSVC(7.5.7601.23008); Mon, 29 Feb 2016 06:12:35 -0800 X-TMN: [iuwicj09DFizbJtkjG787CDB/+m1r2go] X-Originating-Email: [dombrooks@hotmail.com] Message-ID: Content-Type: multipart/alternative; boundary="_79a87ab2-2245-4657-b3b0-644715a63609_" MIME-Version: 1.0 To: Dominic Brooks , , ORACLE-L From: Dominic Brooks Subject: RE: RANK+WINDOW NOSORT STOPKEY=> stopkey doesn't work Date: Mon, 29 Feb 2016 14:12:17 +0000 X-OriginalArrivalTime: 29 Feb 2016 14:12:35.0213 (UTC) FILETIME=[3CC18BD0:01D172FB] X-archive-position: 63708 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: dombrooks@hotmail.com Precedence: normal Reply-To: dombrooks@hotmail.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l --_79a87ab2-2245-4657-b3b0-644715a63609_ Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset="utf-8" There are 10000 rows at each rank #. DENSE_RANK deals in consecutive ranks so 10000 in #1=2C 10000 at #2=2C etc RANK needs to know how many rows are in the current rank before it knows th= e next starting rank so 10000 at #1=2C 10000 at #10001=2C 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 <=3D 1=2C you get 10000 rows with a shortcut. If you do RN <=3D 10000=2C you get 10000 rows without a shortcut. It doesn't seem to be cost-based as both above have the same plan with STOP= KEY & same cost. So... Bug? Sent from my Windows Phone ________________________________ From: Dominic Brooks Sent: =E2=80=8E29/=E2=80=8E02/=E2=80=8E2016 13:00 To: Dominic Brooks=3B xt.and.r@gmail.com=3B ORACLE-L Subject: RE: RANK+WINDOW NOSORT STOPKEY=3D> stopkey doesn't work Oh yes... I was messing with the value of the RN limit and that makes a dif= ference. Sent from my Windows Phone ________________________________ From: Dominic Brooks Sent: =E2=80=8E29/=E2=80=8E02/=E2=80=8E2016 12:49 To: Dominic Brooks=3B xt.and.r@gmail.com=3B ORACLE-L Subject: RE: RANK+WINDOW NOSORT STOPKEY=3D> stopkey doesn't work Ignore... I must have done something stupid. Now I get results consistent w= ith yours in all 3. Sent from my Windows Phone ________________________________ From: Dominic Brooks Sent: =E2=80=8E29/=E2=80=8E02/=E2=80=8E2016 12:44 To: xt.and.r@gmail.com=3B ORACLE-L Subject: RE: RANK+WINDOW NOSORT STOPKEY=3D> 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 Sent: =E2=80=8E29/=E2=80=8E02/=E2=80=8E2016 11:22 To: ORACLE-L Subject: RANK+WINDOW NOSORT STOPKEY=3D> stopkey doesn't work Hi list=2C Anybody knows why STOPKEY doesn't work with RANK()over()=2C 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<=3D10000) =2Cn10 as (select level n from dual connect by level<=3D10) select date'2016-01-01'+n10.n dt from n10000=2C n10 / create index ix_test on xt_test(dt)=3B And if we use DENSE_RANK=2C we can see that stopkey works fine and query executions stops when DENSE_RANK is bigger than needed: SELECT/*+ gather_plan_statistics denserank */ DT=2C RID FROM (SELECT/*+ index(t (dt)) */ DT =2C ROWID RID =2C DENSE_RANK()OVER(ORDER BY DT) RN FROM XT_TEST T ) WHERE RN<=3D2 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()=2C it doesn't stop and fetches all rows from index: SELECT/*+ gather_plan_statistics rank */ DT=2C RID FROM (SELECT/*+ index(t (dt)) */ DT =2C ROWID RID =2C RANK()OVER(ORDER BY DT) RN FROM XT_TEST T ) WHERE RN<=3D20000 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"<=3D20000) 2 - filter(RANK() OVER ( ORDER BY "DT")<=3D20000) -- Best regards=2C Sayan Malakshinov http://orasql.org --_79a87ab2-2245-4657-b3b0-644715a63609_ Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="utf-8"
Ther= e are 10000 rows at each rank #.
DENSE_RANK deals in consecutive ranks so 10000 in #1=2C 10000 at #2=2C etc<= br> RANK needs to know how many rows are in the current rank before it knows th= e next starting rank so 10000 at #1=2C 10000 at #10001=2C 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 <=3B=3D 1=2C =3B you get 10000 rows with a sho= rtcut.
If you do RN <=3B=3D 10000=2C you get 10000 rows without a shortcut.

It doesn't seem to be cost-based as both above have the same plan with STOP= KEY &=3B same cost.

So... Bug?


Sent from my Windows Phone
From: Dominic Brooks
Sent: =E2=80=8E29/=E2=80=8E02/=E2=80=8E2016 13:00
To: Dominic Brooks=3B xt.and.r@gmail.com=3B ORACLE-L
Subject: RE: RANK+=3BWINDOW NOSORT STOPKEY=3D>=3B stopkey doesn't work

Oh yes...= I was messing with the value of the RN limit and that makes a difference.<= br>
Sent from my Windows Phone
From: <= a href=3D"mailto:dombrooks@hotmail.com">Dominic Brooks
Sent: = =E2=80=8E29/=E2=80=8E02/=E2=80=8E2016 12:49
To: <= a href=3D"mailto:dombrooks@hotmail.com">Dominic Brooks=3B xt.and.r@gmail.com=3B ORACLE-L
Subject: R= E: RANK+=3BWINDOW NOSORT STOPKEY=3D>=3B 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: <= a href=3D"mailto:dombrooks@hotmail.com">Dominic Brooks
Sent: = =E2=80=8E29/=E2=80=8E02/=E2=80=8E2016 12:44
To: <= a href=3D"mailto:xt.and.r@gmail.com">xt.and.r@gmail.com=3B ORACLE-L
Subject: R= E: RANK+=3BWINDOW NOSORT STOPKEY=3D>=3B 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: <= a href=3D"mailto:xt.and.r@gmail.com">Sayan Malakshinov
Sent: = =E2=80=8E29/=E2=80=8E02/=E2=80=8E2016 11:22
To: <= a href=3D"mailto:oracle-l@freelists.org">ORACLE-L
Subject: R= ANK+=3BWINDOW NOSORT STOPKEY=3D>=3B stopkey doesn't work

Hi list=2C

Anybody knows why STOPKEY doesn't work with RANK()over()=2C though it = works fine with row_number() and dense_rank()?

I have simple test case:
http://ora= sql.org/scripts/stopkey/denserank.sql

I've created a table with index:
create table xt_test(dt not null) as=20
  with n10000 as (select level n from dual connect by level<=3B=3D10000)
      =2Cn10    as (select level n from dual connect by level<=3B=3D10)
  select date'2016-01-01'+=3Bn10.n dt
  from n10000=2C n10
/
create index ix_test on xt_test(dt)=3B

And if we use DENSE_RANK=2C we can see that stopkey works fine and=
 query executions stops when DENSE_RANK is bigger than needed:
SELECT/*+=3B gather_plan_statistics denserank */ D=
T=2C RID FROM (SELECT/*+=3B
index(t (dt)) */ DT =2C ROWID RID =2C DENSE_RANK()OVER(ORDER BY DT) RN FROM
XT_TEST T ) WHERE RN<=3B=3D2

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)| 0=
0:00:02 |  20000 |00:00:00.06 |     253 |
|*  2 |   WINDOW NOSORT STOPKEY|         |      1 |    100K|   160   (0)| 0=
0:00:02 |  20000 |00:00:00.04 |     253 |
|   3 |    INDEX FULL SCAN     | IX_TEST |      1 |    100K|   160   (0)| 0=
0:00:02 |  20001 |00:00:00.02 |     253 |
---------------------------------------------------------------------------=
-----------------------------------------
But if we use RANK()=2C it doesn't stop and fetches all rows from inde= x:

SELECT/*+=3B gathe= r_plan_statistics rank */ DT=2C RID FROM (SELECT/*+=3B
index(t (dt)) */ DT = =2C ROWID RID =2C RANK()OVER(ORDER BY DT) RN FROM
XT_TEST T ) WHERE RN&= lt=3B=3D20000

Plan hash value: 1892= 911073

---------------------= ---------------------------------------------------------------------------= --------------------
| Id  =3B| Operat= ion  =3B  =3B  =3B  =3B  =3B  =3B  =3B| Name &n= bsp=3B  =3B| Starts | E-Rows | Cost (%CPU)| E-Time  =3B | A-Rows | =  =3B A-Time  =3B | Buffers |
---------------------= ---------------------------------------------------------------------------= --------------------
|  =3B 0 | SELECT= STATEMENT  =3B  =3B  =3B |  =3B  =3B  =3B  =3B= |  =3B  =3B  =3B1 |  =3B  =3B  =3B  =3B|  = =3B 160 (100)|  =3B  =3B  =3B  =3B  =3B|  =3B20000 = |00:00:00.04 |  =3B  =3B 465 |
|*  =3B1 |  = =3BVIEW  =3B  =3B  =3B  =3B  =3B  =3B  =3B &nbs= p=3B  =3B|  =3B  =3B  =3B  =3B |  =3B  =3B &nbs= p=3B1 |  =3B  =3B100K|  =3B 160  =3B (0)| 00:00:02 |  = =3B20000 |00:00:00.04 |  =3B  =3B 465 |
|*  =3B2 |  = =3B WINDOW NOSORT STOPKEY|  =3B  =3B  =3B  =3B |  =3B &= nbsp=3B  =3B1 |  =3B  =3B100K|  =3B 160  =3B (0)| 00:00= :02 |  =3B  =3B100K|00:00:00.14 |  =3B  =3B 465 |
|  =3B 3 |  = =3B  =3BINDEX FULL SCAN  =3B  =3B | IX_TEST |  =3B  =3B=  =3B1 |  =3B  =3B100K|  =3B 160  =3B (0)| 00:00:02 | &= nbsp=3B  =3B100K|00:00:00.04 |  =3B  =3B 465 |
---------------------= ---------------------------------------------------------------------------= --------------------

Predicate Information= (identified by operation id):
---------------------= ------------------------------

 =3B  =3B1 - = filter("=3BRN"=3B<=3B=3D20000)
 =3B  =3B2 - = filter(RANK() OVER ( ORDER BY "=3BDT"=3B)<=3B=3D20000)


--
Best regards=2C
Sayan Malakshinov
--_79a87ab2-2245-4657-b3b0-644715a63609_-- -- http://www.freelists.org/webpage/oracle-l