Improved Response Time with Local Partitioned Index for Order by Score

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

You can work around this by using an inline view when you use the DOMAIN_INDEX_SORT hint. Specifically, you can use the DOMAIN_INDEX_SORT hint to improve query performance on a local partitioned index under the following conditions:

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

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

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

For example, if you have the following text query and local text index created on a partitioned table doc_tab:

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

and you are only interested in fetching top 20 rows, you can rewrite the query to

     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;