Top-N rows in Oracle

Top-N queries are queries that limit the result to a specific number of rows. These are often queries for the most recent or the “best” entries of a result set. For efficient execution, the ranking must be done with a pipelined order by.

The simplest way to fetch only the first rows of a query is fetching the required rows and then closing the statement. Unfortunately, the optimizer cannot foresee that when preparing the execution plan. To select the best execution plan, the optimizer has to know if the application will ultimately fetch all rows. In that case, a full table scan with explicit sort operation might perform best, although a pipelined order by could be better when fetching only ten rows—even if the database has to fetch each row individually. That means that the optimizer has to know if you are going to abort the statement before fetching all rows so it can select the best execution plan.

Inform the database whenever you don’t need all rows.

The SQL standard excluded this requirement for a long time. The corresponding extension (fetch first) was finally introduced with SQL:2008 and is currently available in IBM DB2, PostgreSQL, SQL Server 2012 and Oracle 12c. On the one hand, this is because the feature is a non-core extension, and on the other hand it’s because each database has been offering its own proprietary solution for many years.

The following examples show the use of these well-known extensions by querying the ten most recent sales. The basis is always the same: fetching all sales, beginning with the most recent one. The respective top-N syntax just aborts the execution after fetching ten rows.

All of the above shown SQL queries are special because the databases recognize them as top-N queries.

The database can only optimize a query for a partial result if it knows this from the beginning.

If the optimizer is aware of the fact that we only need ten rows, it will prefer to use a pipelined order by if applicable:

The Oracle execution plan indicates the planned termination with the COUNT STOPKEY operation. That means the database recognized the top-N syntax.

A pipelined top-N query doesn’t need to read and sort the entire result set.

If there is no suitable index on SALE_DATE for a pipelined order by, the database must read and sort the entire table. The first row is only delivered after reading the last row from the table.

This execution plan has no pipelined order by and is almost as slow as aborting the execution from the client side. Using the top-N syntax is still better because the database does not need to materialize the full result but only the ten most recent rows. This requires considerably less memory. The Oracle execution plan indicates this optimization with the STOPKEY modifier on the SORT ORDER BY operation.

The advantages of a pipelined top-N query include not only immediate performance gains but also improved scalability. Without using pipelined execution, the response time of this top-N query grows with the table size. The response time using a pipelined execution, however, only grows with the number of selected rows. In other words, the response time of a pipelined top-N query is always the same; this is almost independent of the table size. Only when the B-tree depth grows does the query become a little bit slower.

Figure 7.1 shows the scalability for both variants over a growing volume of data. The linear response time growth for an execution without a pipelined order by is clearly visible. The response time for the pipelined execution remains constant.

Although the response time of a pipelined top-N query does not depend on the table size, it still grows with the number of selected rows. The response time will therefore double when selecting twice as many rows. This is particularly significant for “paging” queries that load additional results because these queries often start at the first entry again; they will read the rows already shown on the previous page and discard them before finally reaching the results for the second page. Nevertheless, there is a solution for this problem as well as we will see in the next section.

Hey, Dev! Are you looking for a forum full of active developers to help you?

So if you want to:

Then this place is just for you and it is 100% FREE.

Here are a few wrong and correct ways to do it.

Oracle, however, did not have a similar clause until 12c, what I found, imho, quite a nuisance.

12c, finally, comes with the row limiting clause which makes it acceptably easy to query the first n records.

First, we need to create a table with test data:

Github respository oracle-patterns, path: /SQL/select/top_n/data.sql

The select first n rows only selects the first n rows.

In the following example, there's an ambiguity: the third row might be where the num 3 is translated to german (drei) or where it is english (three).

With offset m rows fetch next n rows only, the n records, starting at the mth record, can be fetched:

With fetch first n percent rows only, it can be stated how many percent of the result set should be returned:

Github respository oracle-patterns, path: /SQL/select/top_n/12c-30-pct.sql

In Oracle 11g, the rownum pseudo column was needed.

The following approach is (most probably) wrong (and returns something different than was intended) because Oracle first evaluates the where clause, then adds the pseudo column rownum and then applies the order by. (See also: SQL: Order of select operations).

The following approach is better. With the inner query, Oracle is forced to first evaluate the order by clause. The result set is then passed to the outer query where the where rownum condition is applied.

Unfortunately, the approach with the inner query does not allow for paging (selecting rows n through m):

This query returns nothing!

This is because rownum never reaches 4 because rownum only increases when a row is returned.

Paging, however, is possible with analytical functions, such as row_number(), rank() or dense_rank().

Question:  I need to write a query that performs a top-10 SQL, and I hear that top-n queries are tricky in Oracle.  What are the methods to get top-n rows back in Oracle SQL?

Answer:  Oracle top-n SQL is tricky!  Yes, being declarative, there are many ways to get top-n queries in Oracle SQL.  Oracle top-n query can be satisfied in several ways:

• Using rownum outside an in-line view
• Using rank with over
• Using row_number with over

Here is a review of the top-n SQL methods in Oracle:

select   emp_name,   salaryfrom   emporder by    salary desc

fetch first 10 rows only;

• Top-n SQL method - Use a subselect with ROWNUM.  You can use an inline view with ROWNUM to get the top-10 rows for any SQL query, and the rownum can be used to materialize an in-line view.  Beware, this is not always a good thing, as adding the where rownum WITH clause. 
• Top-n method 1
- Use the SQL dense_rank and SQL ranking functions.  Oracle SQL includes ranking functions that provide support for common OLAP rankings, such as the top 10, bottom 10, top 10 percent, and bottom 10 percent.
• Top-n SQL method 2 - Use the row_number function:

select
   *
from
   (select empno, sal row_number()
    over
       (order by sal desc) rnk from emp)
where rnk <= 5;