Optimizing Queries for Response Time

By default, Oracle optimizes queries for throughput. This results in queries returning all rows in shortest time possible.

However, in many cases, especially in a web-application scenario, queries must be optimized for response time, when you are only interested in obtaining the first few hits of a potentially large hitlist in the shortest time possible.

The following sections describe how to optimize Text queries for response time. You can do so in two ways:

• using FIRST_ROWS hint

• using CHOOSE and DOMAIN_INDEX_SORT hints

  Note: Although both methods optimize for response time, the execution plans of the two methods obtained with EXPLAIN PLAN might be different for a given query.

Better Response Time with FIRST_ROWS

You can change the default query optimizer mode to optimize for response time using the FIRST_ROWS hint. When queries are optimized for response time, Oracle returns the first rows in the shortest time possible.

For example, consider the following PL/SQL block that uses a cursor to retrieve the first 20 hits of a query and uses the FIRST_ROWS hint to optimize the response time:

declare
cursor c is 
select /*+ FIRST_ROWS */ pk, score(1), col from ctx_tab 
            where contains(txt_col, 'test', 1) > 0 order by score(1) desc;
begin
for i in c
loop
insert into t_s values(i.pk, i.col);
exit when c%rowcount > 21;
end loop;
end;
/

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

With the FIRST_ROWS hint, Oracle instructs the Text index to return rowids in score-sorted order, if possible.

Without the hint, Oracle 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 as such takes time.

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

Note: Use the FIRST_ROWS 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.

Other Behavior with FIRST_ROWS

Besides instructing the Text index to return hits in score-sorted order, the FIRST_ROWS hint also tries to avoid blocking operations when optimizing queries for response time. Blocking operations include merge joins, hash joins, and bitmap operations.

As a result, using the FIRST_ROWS hint to optimize for response time might result in a different execution plan than using CHOOSE with DOMAIN_INDEX_SORT, which also optimizes for response time.

You can examine query execution plans using the EXPLAIN PLAN command in SQL.

See Also: Oracle9i Database Performance Guide and Reference for more information about the query optimizer and using hints such as FIRST_ROWS and CHOOSE. For more information about the EXPLAIN PLAN command, Oracle9i Database Performance Guide and Reference and Oracle9i SQL Reference.

Better Response Time with CHOOSE

When you use the CHOOSE or ALL_ROWS optimizer hints, the query is optimized for throughput. This is the default optimizer mode. In this mode, Oracle does not instruct the Text domain index to return score-sorted rows, choosing instead to sort all the rows fetched from the Text index.

To optimize for fast response time under CHOOSE or ALL_ROWS modes, you can use the DOMAIN_INDEX_SORT hint as follows:

declare
cursor c is 
select /*+ CHOOSE DOMAIN_INDEX_SORT */ pk, score(1), col from ctx_tab 
            where contains(txt_col, 'test', 1) > 0 order by score(1) desc;
begin
for i in c
loop
insert into t_s values(i.pk, i.col);
exit when c%rowcount > 21;
end loop;
end;
/

Note: Although you can optimize for response time with this method as well as with FIRST _ROWS by itself, the actual execution plans of the two methods obtained with EXPLAIN PLAN might be different for a given query.

See Also: Oracle9i Database Performance Guide and Reference for more information about the query optimizer and using hints such as FIRST_ROWS and CHOOSE. For more information about the EXPLAIN PLAN command, Oracle9i Database Performance Guide and Reference and Oracle9i SQL Reference.