Search code examples
sqloracle-databaseselectoracle11grownum

Clarifying rownum order of operations in Oracle


According to this link, when using rownum in a query, it is called in the following order of operations.

The FROM/WHERE clause goes first.
ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
SELECT is applied.
GROUP BY is applied.
HAVING is applied.
ORDER BY is applied.

I want to know where the AND would be categorized on this list. Would it be evaluated at the same time as the WHERE? What if the WHERE has a rownum and the AND does not?


Solution

  • The AND has no role in this. When result set is being constructed, the rownum is assigned to the results before outermost ordering. Filtering on ROWNUM is a hard stop from feeding results up from deeper in the execution plan. Therefore for example a construct like where rownum > 5 returns no rows.

    Hopefully this helps. If not, please elaborate in your question and/or explain why you are asking. There are alternatives that are sometimes better, such as row_number().