Optimizing for Response Time

A query optimized for response time provides a fast solution for when you need the highest scoring documents from a hitlist.

The example below returns the first twenty hits to standard out. This example uses the FIRST_ROWS hint and a cursor.

declare 
cursor c is  
  select /*+ FIRST_ROWS */ title, score(1) score 
    from news  
   where contains(txt_col, 'dog', 1) > 0  
   order by score(1) desc; 
begin 
  for c1 in c 
  loop 
    dbms_output.put_line(c1.score||':'||substr(c1.title,1,50)); 
    exit when c%rowcount = 20; 
  end loop; 
end; 
/

See Also: Chapter 5, "Query Tuning"

Retrieving a Range of Documents

A query optimized for response time provides a fast solution for when you need a range of documents from a hitlist sorted by score.

The solution uses the FIRST_ROWS hint in a cursor. The code loops through the cursor to process only the hits in the required range. The example below returns the sorted documents 11 to 20.

declare 
cursor c is  
  select /*+ FIRST_ROWS */ title, score(1) score 
    from news  
   where contains(txt_col, 'dog', 1) > 0  
   order by score(1) desc; 
begin 
  for c1 in c 
  loop 
    if (c%rowcount > 10) then 
      dbms_output.put_line(c1.score||':'||substr(c1.title,1,50)); 
    end if; 
    exit when c%rowcount = 20; 
  end loop; 
end; 
/

See Also: Chapter 5, "Query Tuning"