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. As a general rule, Oracle recommends that you collect statistics on your base table if you are interested in improving your query performance. Optimizing with statistics enables a more accurate estimation of the selectivity and costs of the CONTAINS predicate and thus a better execution plan.

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 topics discuss how to use statistics with the extensible query optimizer:

• Collecting Statistics

• Query Optimization with Statistics Example

• Re-Collecting Statistics

• Deleting Statistics Note: Importing and exporting statistics on domain indexes, including Oracle Text indexes, is not supported with the DBMS_STATS package. For more information on importing and exporting statistics, see the Oracle Database PL/SQL Packages and Types Reference.

See Also: Oracle Text Reference for information on the CONTAINS query operator