|Oracle Text Application Developer's Guide
Part Number A90122-01
Query Tuning, 2 of 5
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 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.
By default, Oracle uses the cost-based optimizer to determine the best execution plan for a query. To allow the optimizer to better estimate costs, you can calculate the statistics on the table you query. To do so, issue the following statement:
Alternatively, you can estimate the statistics on a sample of the table as follows:
These statements collect 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. To re-collect the statistics on a table, you can issue the ANALYZE command as many times as necessary or use the DBMS_STATS package
By collecting statistics on the Text domain index, the Oracle cost-based optimizer is able to do the following:
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 cost-based optimizer can better decide whether to use the domain index to evaluate CONTAINS or to apply the CONTAINS predicate as a post filter.
Consider the following structured query:
author column is of type VARCHAR2 and the
year column is of type NUMBER. Assume that there is a b-tree index on the
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.
After synchronizing your index, you can re-collect statistics on a single index to update the cost estimates. To do so, you can issue 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;
You can delete the statistics associated with a table by issuing:
You can delete statistics on one index by issuing the following statement: