Skip navigation.

XTended Oracle SQL

Syndicate content
XTended Oracle SQL
Updated: 1 hour 28 min ago

WINDOW NOSORT STOPKEY + RANK()

Fri, 2016-03-11 18:23

Recently I found that WINDOW NOSORT STOPKEY with RANK()OVER() works very inefficiently: http://www.freelists.org/post/oracle-l/RANKWINDOW-NOSORT-STOPKEY-stopkey-doesnt-work
The root cause of this behaviour is that Oracle optimizes WINDOW NOSORT STOPKEY with RANK the same way as with DENSE_RANK:

rnk1

create table test(n not null) as 
  with gen as (select level n from dual connect by level<=100)
  select g2.n as n
  from gen g1, gen g2
  where g1.n<=10
/
create index ix_test on test(n)
/
exec dbms_stats.gather_table_stats('','TEST');
select/*+ gather_plan_statistics */ n
from (select rank()over(order by n) rnk
            ,n
      from test)
where rnk<=3
/
select * from table(dbms_xplan.display_cursor('','','allstats last'));
drop table test purge;

[collapse]

Output

         N
----------
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1

10 rows selected.

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
SQL_ID  8tbq95dpw0gw7, child number 0
-------------------------------------
select/*+ gather_plan_statistics */ n from (select rank()over(order by
n) rnk             ,n       from test) where rnk<=3

Plan hash value: 1892911073

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |      1 |        |     10 |00:00:00.01 |       3 |       |       |          |
|*  1 |  VIEW                  |         |      1 |   1000 |     10 |00:00:00.01 |       3 |       |       |          |
|*  2 |   WINDOW NOSORT STOPKEY|         |      1 |   1000 |     30 |00:00:00.01 |       3 | 73728 | 73728 |          |
|   3 |    INDEX FULL SCAN     | IX_TEST |      1 |   1000 |     31 |00:00:00.01 |       3 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------

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

   1 - filter("RNK"<=3)
   2 - filter(RANK() OVER ( ORDER BY "N")<=3)

[collapse]
As you can see, A-Rows in plan step 2 = 30 – ie, that is the number of rows where DENSE_RANK<=3, but not RANK<=3. The more effective way will be to stop after first 10 rows, because 11th row already has RANK more than 3!

But we can create own STOPKEY version with PL/SQL:

PL/SQL STOPKEY version

create or replace type rowids_table is table of varchar2(18);
/
create or replace function get_rowids_by_rank(
      n          int
     ,max_rank   int
   ) 
   return rowids_table pipelined
as
begin
   for r in (
      select/*+ index_rs_asc(t (n))  */ rowidtochar(rowid) chr_rowid, rank()over(order by n) rnk
      from test t
      where t.n > get_rowids_by_rank.n
      order by n
   )
   loop
      if r.rnk <= max_rank then
         pipe row (r.chr_rowid);
      else
         exit;
      end if;
   end loop;
   return;
end;
/
select/*+ leading(r t) use_nl(t) */
   t.*
from table(get_rowids_by_rank(1, 3)) r
    ,test t
where t.rowid = chartorowid(r.column_value)
/

[collapse] In that case the fetch from a table will stop when rnk will be larger than max_rank

Categories: Development