Query Optimization with Statistics Example

The following structured query provides an example for optimizing statistics:

select score(1) from tab where contains(txt, 'freedom', 1)  > 0 and author = 'King' and year > 1960;

Assume the following:

• The author column is of type VARCHAR2 and the year column is of type NUMBER.

• A b-tree index on the author column.

• The structured author predicate is highly selective with respect to the CONTAINS predicate and the year predicate. That is, the structured predicate (author = ‘King’) returns a much smaller number of rows with respect to the year and CONTAINS predicates individually, say 5 rows returned versus 1000 and 1500 rows, respectively.

In this situation, Oracle Text can execute this query more efficiently by first scanning a b-tree index range on the structured predicate (author = ‘King’), then accessing a table by rowid, and then applying the other two predicates to the rows returned from the b-tree table access. Note: When statistics are not collected for a Oracle Text index, the CBO assumes low selectivity and index costs for the CONTAINS predicate.