CODEWITHSUNDEEP
oracle-databaseoracle11grow-numberrownum
Ionuț U
Ionuț U

Reputation: 45

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

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

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

Is this behavior by design or is just a coincidence?

Upvotes: 1

Views: 971

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

I would not recommend it. There is no guarantee that the order by null is going to respect the outer order by, although in practice the optimizer might do this.

Instead, you can safely do:

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

You can check the query execution plans, but I suspect that the row_number() might add a slight bit of overhead.

Upvotes: 1

Related Questions