Thanks for the question, Ionut.

Asked: March 23, 2018 - 9:55 am UTC

Last updated: March 28, 2018 - 1:45 am UTC

Version: 11.2

Viewed 10K+ times! This question is

You Asked

Hi,

I did some testing and it seems that row_number() over (order by null) could be used as an alternative to oracle's rownum pseudocolumn. Is this behavior by design or is just a coincidence?

I'm trying this because some queries are very slow when paginated with rownum (compared with row_number()) and I can't simply move the order by clause into row_number()

See livesql link for an example.

with LiveSQL Test Case:

https://livesql.oracle.com/apex/f?p=590:41:104647132323502:::41:SHAREKEY:GF22F5O4OS741RIWSLRQCL6MA

and Connor said...

It does in the sense that we don't need to really do a sort, eg

select row_number() over (order by null) as rnum, i.* 
from invoice i 

Statistics
----------------------------------------------------
          1  recursive calls
          0  db block gets
         71  consistent gets
          0  physical reads
          0  redo size
      30365  bytes sent via SQL*Net to client
       1334  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

but I'd like to see some definitive evidence that it is faster than rownum.

SQL>   create table t pctfree 0 as
  2     select rownum x from
  3  ( select 1 from dual connect by level <= 10000 ),
  4  ( select 1 from dual connect by level <= 10000 );

Table created.

SQL> set timing on
SQL> begin
  2  for i in (
  3          select rownum, t1.* from (
  4              select t.*
  5              from t
  6              order by t.x
  7          ) t1
  8  )
  9  loop
 10    null;
 11  end loop;
 12  end;
 13  /

PL/SQL procedure successfully completed.

Elapsed: 00:01:00.70
SQL>
SQL> set timing on
SQL> begin
  2  for i in (
  3              select row_number() over (order by null) as rnum, t.*
  4              from t
  5              order by t.x
  6  )
  7  loop
  8    null;
  9  end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

Elapsed: 00:01:05.19

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, March 26, 2018 - 6:11 am UTC

I will try to give you an example as soon as possible. Until then, It's something that doesn't make sense for me:

An analytic function isn't processed before the query's order by? In my example

select rownum, i.* from ( 
    select row_number() over (order by null) as rnum, i.* 
    from invoice i 
    order by i.dateinvoiced desc 
) i

if I replace "order by i.dateinvoiced desc" with asc or another column row_number() still gives 1, 2, 3...
From this tests It seems that row_number() is processed after order by...

Also, to give a more concise answer to the initial question (if it's safe to replace rownum with row_number() over (order by null)), my personal opinion is that this is just a coincidence (a side effect of an oracle internal algorithm). What do you think?

March 27, 2018 - 5:24 am UTC

"It seems that row_number() is processed after order by... "

I think a more likely hypothesis is that we simply optimize it out, ie, conceptually treat it like rownum.

But it will stress, I can't find anything in the docs that says the two are synonymous...so it might change one day.

The only thing we have in there is:

"the order in which nulls are presented is non-deterministic."

Ionut Ursuleanu, March 26, 2018 - 10:32 am UTC

See https://livesql.oracle.com/apex/livesql/s/gglhjm01cfnm8tpmd8er0nnhf for an example, but test it with 11g (with 18 seems ok)

Function call?

John Keymer, March 27, 2018 - 7:30 am UTC

In your example, you select a function value. Is that what is happening in the real query? Is that function passed a value if so? Could it be that the function is being called for every row and would benefit from Scalar Subquery Caching?

i.e. use (select myFunction() from dual) instead.

I ran both your examples on 12.1 and they both returned instantly as expected given the small data volumes. Were you comparing the plan cost? Even though they are very similar, I'd imagine that you cannot do that because ultimately they are different queries.

March 28, 2018 - 1:45 am UTC

Sorry, I can't reproduce that. This from 11.2.0.4 (Windows)

<code>

SQL> set timing on
SQL> begin
2 for i in (
3 select rownum, t1.* from (
4 select t.*
5 from t
6 order by t.x
7 ) t1
8 )
9 loop
10 null;
11 end loop;
12 end;
13 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.97
SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.08
SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.72
SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.72
SQL>
SQL>
SQL> set timing on
SQL> begin
2 for i in (
3 select row_number() over (order by null) as rnum, t.*
4 from t
5 order by t.x
6 )
7 loop
8 null;
9 end loop;
10 end;
11 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.56
SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.64
SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.63
SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.63
SQL>
SQL>
SQL>
</code

Ionut Ursuleanu, March 27, 2018 - 9:28 am UTC

I also tested the queries with 12.2 and ran below 0.5s. BUT, in 11.2.0.4 the rownum takes 2.7s

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Function call?

More to Explore

PL/SQL demos

PL/SQL docs

Is safe to use row_number() over (order by null) as a rownum alternative?

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Function call?

More to Explore

PL/SQL demos

PL/SQL docs