Performance Tuning with CDI

Support for mapping a FILTER BY column to MDATA enables query performance to be optimized for equality searches by restricting supported functionality of RANGE and LIKE. However, mapping a FILTER BY column to MDATA is not recommended 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 DATE stamp. For such sequential columns, mapping to SDATA is recommended.

The following hints can be used to push or not push the SORT and FILTER BY predicates into the CDI:

DOMAIN_INDEX_SORT. The query optimizer will try to push the applicable sorting criteria into the specified composite domain index.
DOMAIN_INDEX_NO_SORT. The query optimizer will try NOT to push sorting criteria into the specified composite domain index.
DOMAIN_INDEX_FILTER(table name index name). The query optimizer will try to push the applicable FILTER BY predicate(s) into the specified composite domain index.
DOMAIN_INDEX_NO_FILTER(table name index name). The query optimizer will not try to push the applicable FILTER BY predicate(s) into the specified composite domain index.

Note:

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

Example 7-1 Performance Tuning a Text Query with CDI Hints

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

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;