Optimizing for Response Time

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

The following example returns the first twenty hits as output. This example uses the FIRST_ROWS(n) hint and a cursor.

declare
cursor c is
  select /*+ FIRST_ROWS(20) */ 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 = 21;
  end loop;
end;
/

The following factors can also influence query response time:

Collection of table statistics
Memory allocation
Sorting
Presence of large object columns in your base table
Partitioning
Parallelism
Number of term expansions in your query