|Oracle Text Application Developer's Guide
Part Number A90122-01
Query Tuning, 3 of 5
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:
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
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.
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.
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; /
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.