Collecting Statistics

By default, Oracle Text uses the cost-based optimizer (CBO) to determine the best execution plan for a query.

To enable the optimizer to better estimate costs, you can calculate the statistics on the table you query as follows:

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;

You can also collect statistics in parallel with the DBMS_STATS.GATHER_TABLE_STATS procedure:

begin
 
DBMS_STATS.GATHER_TABLE_STATS('owner', 'table_name',
                                       estimate_percent=>50,
                                       block_sample=>TRUE,
                                       degree=>4) ;
 
end  ;

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, enter the ANALYZE statement as many times as necessary or use the DBMS_STATS package.

By collecting statistics on the Text domain index, the cost-based optimizer in Oracle Database is able to perform the following tasks:

  • 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 predicate using the domain index

  • Estimate the I/O and CPU costs of each invocation of CONTAINS

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.

See Also: