Generally, we don’t limit query results. However, when we only care about the first few rows or to implement table pagination, limiting query results is just what we need. Database vendors provide us with such functionality; most of them in their own distinct way.ExampleLet’s take a look at the 2014 Sochi Olympics Men’s Normal Hill Individual ski jumping results in theskijump_resultstable. There is no index on theskijump_resultstable. The following queries are examples of a Top-N and pagination query, which I will use in the following parts of the article.
If you were to implement a Top-N or pagination query in an Oracle database, you wouldn’t find any dedicated clause to limit the query result like TOP, LIMIT or FETCH FIRST. For each row returned by the query, Oracle provides a ROWNUM pseudocolumn that returns a number indicating the order in which the database selects the row from a table or set of joined views.ExampleLet’s take a look at the