The Composite Domain Index feature of the Extensibility Framework in Oracle Database enables structured columns to be indexed by Oracle Text. Therefore, both text and one or more structured criteria can be satisfied by one single Oracle Text index row source. Performance for the following types of query are improved:
Text query with structured criteria in the SQL WHERE clause.
Text query with structured ORDER BY criteria.
Combination of both of the previous two query types.
As with concatenated b-tree indexes or bitmap indexes, applications will experience slow-down in DML performance as the number of FILTER BY and ORDER BY columns increases. Where SCORE-sort push-down is optimized for response time, the structured sort or combination of SCORE and structured sort push-down are also optimized for response time, and not for throughput. However, using DOMAIN_INDEX_SORT or FIRST_ROWS(n) hints to force the sort to be pushed into CDI while fetching the entire hitlist may result in poor query response time.