10.5 Performance Tuning with CDI

Because you can map a FILTER BY column to MDATA, you can optimize query performances for equality searches by restricting the supported functionality of RANGE and LIKE. However, Oracle does not recommend mapping a FILTER BY column to MDATA if the FILTER BY column contains sequential values or has very high cardinality. Doing so can result in a very long and narrow $I table and reduced $X performance. One example of such a sequential column might be one that uses the DATE stamp. For such sequential columns, mapping to SDATA is recommended.

Use the following hints to push or not push the SORT and FILTER BY predicates into the CDI:

  • DOMAIN_INDEX_SORT: The query optimizer tries to push the applicable sorting criteria into the specified CDI.

  • DOMAIN_INDEX_NO_SORT: The query optimizer tries not to push sorting criteria into the specified CDI.

  • DOMAIN_INDEX_FILTER(table name index name): The query optimizer tries to push the applicable FILTER BY predicates into the specified CDI.

  • DOMAIN_INDEX_NO_FILTER(table name index name): The query optimizer does not try to push the applicable FILTER BY predicate(s) into the specified CDI.

Note:

The domain_index_filter hint does not force the query optimizer to use CDI. Instead, if the CBO chooses to use the CDI, then it should also push the filter predicate into the index. To force the query optimizer to choose the CDI index, you additionally need to use the INDEX hint.

Example 10-1 Performance Tuning an Oracle Text Query with CDI Hints

The following example performs an optimized query on the books table. 

SELECT bookid, pub_date, source FROM
  (SELECT /*+ domain_index_sort domain_index_filter(books books_ctxcdi) */ bookid, pub_date, source
      FROM books
      WHERE CONTAINS(text, 'aaa',1)>0 AND bookid >= 80
      ORDER BY PUB_DATE desc nulls last, SOURCE asc  nulls last, score(1) desc)
 WHERE rownum < 20;