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

  • From: Sayan Malakshinov <xt.and.r@xxxxxxxxx>
  • To: Dominic Brooks <dombrooks@xxxxxxxxxxx>
  • Date: Mon, 29 Feb 2016 18:13:53 +0300

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@SEL$1
   2 - SEL$2
   3 - SEL$2 / T@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@xxxxxxxxxxx>
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@xxxxxxxxxxx>
Sent: ‎29/‎02/‎2016 13:00

To: Dominic Brooks <dombrooks@xxxxxxxxxxx>; xt.and.r@xxxxxxxxx; ORACLE-L
<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 <dombrooks@xxxxxxxxxxx>
Sent: ‎29/‎02/‎2016 12:49
To: Dominic Brooks <dombrooks@xxxxxxxxxxx>; xt.and.r@xxxxxxxxx; ORACLE-L
<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 <dombrooks@xxxxxxxxxxx>
Sent: ‎29/‎02/‎2016 12:44
To: xt.and.r@xxxxxxxxx; ORACLE-L <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 <xt.and.r@xxxxxxxxx>
Sent: ‎29/‎02/‎2016 11:22
To: ORACLE-L <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




-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

Other related posts: