SQL Language (MOSC)

MOSC Banner

How does ROWNUM really work ?

edited Apr 24, 2014 10:31AM in SQL Language (MOSC) 4 commentsAnswered ✓

Hi,

If I have a table of 20 million rows and I want a query that returns where rownum < 10, how does Oracle handle this ?

1) Does the query run in full getting all the rows that meet the query, then only return the first 9 ?

or

2) Does the query dynamically return the first 9 it comes across then stops processing the query not actually retrieving the full results as if the rownum < 10 clause did not exist ?

To clarify, does the query look at a block of data or a portion of the result set and if the query returns less than 9 hits, it processes the next block of data and so on until 9 hits are made ?

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center