Oracle Database Administration: The Essential Refe

ESTIMATE versus COMPUTE

The ANALYZE command allows you to specify how many rows are to be sampled when creating the statistics used by the cost-based optimizer.

You can tell Oracle to compute the statistics by looking at all rows:

ANALYZE TABLE scott.emp COMPUTE STATISTICS FOR TABLE;

You can tell Oracle to use a sample of 1064 rows:

ANALYZE TABLE scott.emp ESTIMATE STATISTICS FOR TABLE;

You can specify the number of rows to be used in the sample:

ANALYZE TABLE scott.emp ESTIMATE STATISTICS FOR TABLE SAMPLE 10000 ROWS;

You can specify a percentage of the rows to be used in the sample:

ANALYZE TABLE scott.emp ESTIMATE STATISTICS FOR TABLE SAMPLE 10 PERCENT;

Oracle claims that for sufficiently large tables, a 5 percent sampling is sufficient. This is consistent with current statistical modeling theory. The problem is determining that your table is sufficiently large.

Even when you estimate statistics, some information stored in the data dictionary is computed exactly. For tables, this is the number of blocks below and above the high water mark. For indexes, this is the depth of the B-tree.

Our recommendations are as follows:

1. If you have sufficient time and computer resources, use COMPUTE STATISTICS to get the best analysis.

2. If not, step down to ESTIMATE ... SAMPLE 40 PERCENT.

3. If this is still too much, step down to ESTIMATE ... SAMPLE 20 PERCENT.

4. Finally, if this is still too much, step down to ESTIMATE ... SAMPLE 5 PERCENT for tables with at least one million rows.