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, calculate the statistics on the table you queried table:

ANALYZE TABLE <table_name> COMPUTE STATISTICS;

Alternatively, estimate the statistics on a sample of the table:

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 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 CBO in Oracle Database can perform the following tasks:

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 CBO can better decide whether to use the domain index to evaluate CONTAINS or to apply the CONTAINS predicate as a post filter.

See Also: