Improved Response Time with the Local Partitioned Index for Order by Score

The DOMAIN_INDEX_SORT hint on a local partitioned index might result in poor performance, especially when you order by score. All hits to the query across all partitions must be obtained before the results can be sorted.

Instead, use an inline view when you use the DOMAIN_INDEX_SORT hint. Specifically, use the DOMAIN_INDEX_SORT hint to improve query performance on a local partitioned index under the following conditions:

• The Oracle Text query itself, including the order by SCORE() clause, is expressed as an in-line view.

• The Oracle Text query inside the in-line view contains the DOMAIN_INDEX_SORT hint.

• The query on the in-line view has a ROWNUM predicate that limits the number of rows to fetch from the view.

For example, the following Oracle Text query and local Oracle Text index are created on a partitioned doc_tab table:

select doc_id, score(1) from doc_tab
   where contains(doc, 'oracle', 1)>0
   order by score(1) desc;

If you are interested in fetching only the top 20 rows, you can rewrite the query as follows:

select * from
     (select /*+ DOMAIN_INDEX_SORT */ doc_id, score(1) from doc_tab
         where contains(doc, 'oracle', 1)>0 order by score(1) desc)
 where rownum < 21;

See Also: Oracle Database SQL Language Reference for more information about the EXPLAIN PLAN statement