Optimizing Queries for Throughput

When you optimize a query for throughput, the default behavior returns all hits in the shortest time possible.

Here is how you can explicitly optimize queries for throughput:

• CHOOSE and ALL ROWS Modes: By default, you optimize queries with the CHOOSE and ALL_ROWS modes. Oracle Text returns all rows in the shortest time possible.

• FIRST_ROWS(n) Mode: In FIRST_ROWS(n) mode, the optimizer inOracle AI Database optimizes for fast response time by having the Text domain index return score-sorted rows, if possible. This is the default behavior when you use the FIRST_ROWS(n) hint.

  If you want to optimize throughput with FIRST_ROWS(n), then use the DOMAIN_INDEX_NO_SORT hint. Better throughput means that you are interested in getting all query rows in the shortest time possible.

  The following example achieves better throughput by not using the Text domain index to return score-sorted rows. Instead, Oracle Text sorts the rows after all rows that satisfy the CONTAINS predicate are retrieved from the index:

  select /*+ FIRST_ROWS(10) DOMAIN_INDEX_NO_SORT */ pk, score(1), col from ctx_tab
  
              where contains(txt_col, 'test', 1) > 0 order by score(1) desc;

See Also: Oracle Database SQL Tuning Guide for more information about the query optimizer and using hints such as FIRST_ROWS(n) and CHOOSE