CONTEXT Index Example: Query Processing with FILTER BY and ORDER BY

To enable more efficient query processing and better response time for mixed queries, you can use FILTER BY and ORDER BY clauses as shown in the following example.

CREATE INDEX myindex on docs(text) INDEXTYPE is CTXSYS.CONTEXT
FILTER BY category, publisher, pub_date
ORDER BY pub_date desc;

In this example, by specifying the clause FILTER BY category, publisher, pub_date at query time, Oracle Text will also consider pushing any relational predicate on any of these columns into the Text index row source for more efficient query processing.

Also, when the query has matching ORDER BY criteria, by specifying ORDER BY pub_date desc, Oracle Text will determine whether to push the SORT into the Text index row source for better response time.