CODEWITHSUNDEEP
sqloracle-database
iwan
iwan

Reputation: 7569

ORACLE SQL ROWNUM execution order

in Oracle SQL, there is a possible criteria called rownum. Can i confirm that rownum will be executed at last as just a limit for number of records return? or could it be executed first, before other WHERE SQL criteria (let's if we put rownum prior to the others)?

Upvotes: 5

Views: 2758

Answers (2)

rfusca
rfusca

Reputation: 7705

It's not the equivalent of LIMIT in other languages. If you plan on limiting the number of records with rownum, you'll need to subquery the ORDER BY on the inside and use rownum in the outer query. Order of elements in your WHERE clause does not matter. See this excellent article by Tom Kyte.

Upvotes: 5

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

Yes, within a WHERE clause, ROWNUM is always evaluated last, after all other predicates have been evaluated, regardless of their order.

It is evaluated before any GROUP BY, or ORDER BY clauses, however.

Upvotes: 4

Related Questions