When you need the first rows of an
ORDER BY query, Oracle recommends that you use the cost-based
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:
declare cursor c is select /*+ FIRST_ROWS(10) */ article_id from articles_tab where contains(article, 'Omophagia')>0 order by pub_date desc; begin
for i in c loop insert into t_s values(i.pk, i.col); exit when c%rowcount > 11; end loop;
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
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.