How does ROWNUM really work ?
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 ?