SQL - Row Numbers and Limits

A common occurrence that most programmers face are numbering rows in Oracle. There are several misconceptions about how to do this and what are the costs of such methods. I personally use the row_number() analytic function, available since version 9 to accomplish this.

1. select my_table.*, row_number() over (order by id desc)  
2. from my_table;  

Note in the above code the syntax of the call, you call the analytic function and then tell Oracle how you want the data to be ordered. If you used "rownum" to accomplish this you would get some interesting result when changing the order by or introducing a where clause.

So obviously row numbering is pretty easily accomplished, however there are times when I only need a certain amount of rows. For instance, I need 10 rows at a time or 20. Having written my share of web applications, I can say that feature is needed a lot. So how can we get say the first 5 results returned, its accomplished by the following:

1. select *  
2. from  
3. (  
4. select my_table.*, row_number() over (order by id desc) rn  
5. from my_table  
6. )  
7. where rn between 1 and 5;  
8. ;  

Okay, now we have limited the number of results returned by our query. In addition, we can now accomplish things like pagination through a result set of data. As with most things, there is a cost associated. Notice my inner query has no where clause, thereby inducing a full table scan. You may say, 'Now wait, didn't we just limit the amount of results returned?'. And yes, we did, however Oracle has to run through the full set of results of the inner query to correctly row_number() the rows and then it can parse out the first five or the last five or whatever combination we need.

All of that being said, this is the most efficient way to row number and limit are result set of data based purely on the number of results returned in Oracle.