Oracle Database Administration: The Essential Refe

ANALYZE TABLE

The first way the ANALYZE command is used is to analyze a table. ANALYZE TABLE causes Oracle to determine how many rows are in the table and how storage is allocated. It also calculates the number of chained rows.

The most important pieces of information the optimizer gets from this process are the number of rows and the number of blocks. When joining two or more tables, the optimizer will attempt to use the table with the fewest number of rows or blocks as the driving table. This should reduce the total amount of disk I/O necessary, and thus improve performance.

You should analyze all tables that will ever be used in a join, which probably means that you must analyze every table in your application.

Warning

Oracle specifically advises us not to ANALYZE tables belonging to SYS. The internal access paths used to query the data dictionary have already been optimized in the kernel code. Furthermore, in some releases of Oracle7, analyzing tables owned by SYS can cause an ORA-600 error.

An example of the ANALYZE TABLE command is shown here:

ANALYZE TABLE scott.emp COMPUTE STATISTICS FOR TABLE;

When you analyze a table, Oracle populates the following columns in the DBA_TABLES, ALL_TABLES, and USER_TABLES data dictionary views:

NUM_ROWS

The number of rows in the table.

BLOCKS

The number of data blocks in use.

EMPTY_BLOCKS

The number of data blocks above the highwater mark. Note that BLOCKS + EMPTY_BLOCKS + 1 equals the total number of blocks allocated to the table. The highwater mark ...