Oracle Text Application Developer's Guide Release 9.0.1 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:
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 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
See Also:
Oracle9i SQL Reference and Oracle9i Database Performance Guide and Reference for more information about the ANALYZE command. |
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:
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.
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:
ANALYZE TABLE <table_name> DELETE STATISTICS;
You can delete statistics on one index by issuing the following statement:
ANALYZE INDEX <index_name> DELETE STATISTICS;
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|