Improved Response Time with FIRST_ROWS(n) Hint for ORDER BY Queries

When you need the first rows of an ORDER BY query, Oracle recommends that you use the cost-based FIRST_ROWS(n) hint.


As the FIRST_ROWS(n) hint is cost-based, Oracle recommends that you collect statistics on your tables before you use this hint.

You use the FIRST_ROWS(n) hint in cases where you want the first number (n) of rows in the shortest possible time. For example, consider the following PL/SQL block that uses a cursor to retrieve the first 10 hits of a query and uses the FIRST_ROWS(n) hint to optimize the response time:

cursor c is 

select /*+ FIRST_ROWS(10) */ article_id from articles_tab
   where contains(article, 'Omophagia')>0 order by pub_date desc;

for i in c
insert into t_s values(, i.col);
exit when c%rowcount > 11;
end loop;

The cursor c is a SELECT statement that returns the rowids that contain the word omophagia in sorted order. The code loops through the cursor to extract the first 10 rows. These rows are stored in the temporary table t_s.

With the FIRST_ROWS(n) hint, the optimizer instructs the Text index to return rowids in score-sorted order when the cost of returning the top-N hits is lower.

Without the hint, Oracle Database sorts the rowids after the Text index has returned all the rows in unsorted order that satisfy the CONTAINS predicate. Retrieving the entire result set this way takes time.

Because only the first 10 hits are needed in this query, using the hint results in better performance.


Use the FIRST_ROWS(n) hint when you need only the first few hits of a query. When you need the entire result set, do not use this hint as it might result in poor performance.