Re: RANK+WINDOW NOSORT STOPKEY=> stopkey doesn't work
From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Mon, 29 Feb 2016 18:13:53 +0300
Message-ID: <CAOVevU5-ycR1__FFu8dXMAJkky6Fem1Liu4=7_sg6NMi+4UwmQ_at_mail.gmail.com>
Especially intersting that sometimes it works fine: all next 3 test cases show that it can work sometimes:
)
loop
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<=150
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
Date: Mon, 29 Feb 2016 18:13:53 +0300
Message-ID: <CAOVevU5-ycR1__FFu8dXMAJkky6Fem1Liu4=7_sg6NMi+4UwmQ_at_mail.gmail.com>
Especially intersting that sometimes it works fine: all next 3 test cases show that it can work sometimes:
http://orasql.org/scripts/stopkey/rank1.sql http://orasql.org/scripts/stopkey/rank2.sql http://orasql.org/scripts/stopkey/rank3.sql
For example rank3.sql:
drop table xt_test purge;
create table xt_test(dt not null) as
with n10000 as (select level n from dual connect by level<=10000)
,x as (select level n from dual connect by level<=10)
select date'2016-01-01'+n10000.n dt
from n10000, x
/
create index ix_test on xt_test(dt);
begin dbms_stats.gather_table_stats('','XT_TEST'); end;
/
begin
for r in (
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<=150
)
loop
null;
end loop;
end;
/
select p.*
from
v$sql s
,table(dbms_xplan.display_cursor(s.sql_id,s.CHILD_NUMBER,'all allstats
last')) p
where s.sql_text like 'SELECT/*+ gather_plan_statistics rank */ DT, RID%'
/
Output:
SQL_ID 2qy2xc8ccvrb1, child number 0
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<=150
Plan hash value: 1892911073
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
| 0 | SELECT STATEMENT | | 1 | | | 160 (100)| | 150 |00:00:00.01 | 6 | | | | |* 1 | VIEW | | 1 | 100K| 3320K| 160 (0)| 00:00:02 | 150 |00:00:00.01 | 6 | | | | |* 2 | WINDOW NOSORT STOPKEY| | 1 | 100K| 781K| 160 (0)| 00:00:02 | 1500 |00:00:00.01 | 6 | 878K| 519K| | | 3 | INDEX FULL SCAN | IX_TEST | 1 | 100K| 781K| 160 (0)| 00:00:02 | 1501 |00:00:00.01 | 6 | | | | -------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
1 - SEL$2 / from$_subquery$_001_at_SEL$1 2 - SEL$2 3 - SEL$2 / T_at_SEL$2
Predicate Information (identified by operation id):
1 - filter("RN"<=150)
2 - filter(RANK() OVER ( ORDER BY "DT")<=150)
On Mon, Feb 29, 2016 at 5:12 PM, Dominic Brooks <dombrooks_at_hotmail.com> wrote:
> 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 <dombrooks_at_hotmail.com> > Sent: 29/02/2016 13:00 > > To: Dominic Brooks <dombrooks_at_hotmail.com>; xt.and.r_at_gmail.com; ORACLE-L > <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 <dombrooks_at_hotmail.com> > Sent: 29/02/2016 12:49 > To: Dominic Brooks <dombrooks_at_hotmail.com>; xt.and.r_at_gmail.com; ORACLE-L > <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 <dombrooks_at_hotmail.com> > Sent: 29/02/2016 12:44 > To: xt.and.r_at_gmail.com; ORACLE-L <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 <xt.and.r_at_gmail.com> > Sent: 29/02/2016 11:22 > To: ORACLE-L <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 >
-- Best regards, Sayan Malakshinov Oracle performance tuning engineer Oracle ACE Associate http://orasql.org -- http://www.freelists.org/webpage/oracle-lReceived on Mon Feb 29 2016 - 16:13:53 CET