Optimizing Queries with Statistics

Query optimization with statistics uses the collected statistics on the tables and indexes in a query to select an execution plan that can process the query in the most efficient manner. The optimizer attempts to choose the best execution plan based on the following parameters:

• the selectivity on the CONTAINS predicate

• the selectivity of other predicates in the query

• the CPU and I/O costs of processing the CONTAINS predicates

The following sections describe how to use statistics with the extensible query optimizer. Optimizing with statistics allows for a more accurate estimation of the selectivity and costs of the CONTAINS predicate and thus a better execution plan.

Collecting Statistics

By default, the extensible query optimizer is enabled. To use the extensible optimizer, you must calculate the statistics on the table you query. To do so, issue the following statement:

ANALYZE TABLE <table_name> COMPUTE STATISTICS; 

Alternatively, you can estimate the statistics on a sample of the table as follows:

ANALYZE TABLE <table_name> ESTIMATE STATISTICS 1000 ROWS;

or

ANALYZE TABLE <table_name> ESTIMATE STATISTICS 50 PERCENT;

These statement collects statistics on all the objects associated with table_name including the table columns and any indexes (b-tree, bitmap or Text domain) associated with the table. You can issue the above ANALYZE command as many times as necessary to re-collect the statistics on a table.

By collecting statistics on the Text domain index, the extensible query optimizer is able to do the following:

• estimate the selectivity of the CONTAINS predicate

• estimate the I/O and CPU costs of using the Text index, that is, the cost of processing the CONTAINS using the domain index

• estimate the I/O and CPU costs of each invocation of CONTAINS() function

Knowing the selectivity of a CONTAINS predicate is useful for queries that contain more than one predicate, such as in structured queries. This way the extensible query optimizer can better decide whether to use the domain index to evaluate CONTAINS or to apply the CONTAINS predicate as a post filter.

Example

Consider the following structured query:

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

Assume the author column is of type VARCHAR2 and the year column is of type NUMBER. Assume that there is a b-tree index on the author column.

Also assume that 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 versus 1000 and 1500 rows respectively.

In this situation, Oracle can execute this query more efficiently by first doing a b-tree index range scan on the structured predicate (author = 'King'), followed by a table access by rowid, and then applying the other two predicates to the rows returned from the b-tree table access.

Without associating a statistics type with indextype context, the extensible query optimizer will always choose to process the CONTAINS() predicate using the text domain index.

Note: When the statistics are not collected for a Text index, the behavior is the same as not enabling the extensible query optimizer.

Re-Collecting Statistics

You can re-collect statistics on a single index by issuing any of the following statements:

ANALYZE INDEX <index_name> COMPUTE STATISTICS;
or 

ANALYZE INDEX <index_name> ESTIMATE STATISTICS SAMPLE 1000 ROWS;

or

ANALYZE INDEX <index_name> ESTIMATE STATISTICS SAMPLE 50 PERCENT;

Deleting Statistics

You can delete the statistics associated with a table by issuing:

ANALYZE TABLE <table_name> DELETE STATISTICS;

You can delete statistics on one index by issuing the following statement:

ANALYZE INDEX <index_name> DELETE STATISTICS;

Disabling and Enabling the Extensible Query Optimizer

By default the extensible query optimizer is enabled. To disable the extensible query optimizer, issue the following statements:

DISASSOCIATE STATISTICS FROM INDEXTYPES ConText;
DISASSOCIATE STATISTICS FROM PACKAGES ctx_contains;

After disabling the extensible query optimizer, you can re-enable it. To do so, issue the following SQL statements as CTXSYS:

ASSOCIATE STATISTICS WITH INDEXTYPES ConText USING textoptstats;
ASSOCIATE STATISTICS WITH PACKAGES ctx_contains USING textoptstats;