The ANALYZE statement allows you to validate and compute statistics for an index, table, or cluster. These statistics are used by the cost-based optimizer when it calculates the most efficient plan for retrieval. In addition to its role in statement optimization, ANALYZE also helps in validating object structures and in managing space in your system. You can choose the following operations: COMPUTER, ESTIMATE, and DELETE. Early version of Oracle7 produced unpredicatable results when the ESTIMATE operation was used. It is best to compute

your statistics.

EX:

select OWNER,

sum(decode(nvl(NUM_ROWS,9999), 9999,0,1)) analyzed,

sum(decode(nvl(NUM_ROWS,9999), 9999,1,0)) not_analyzed,

count(TABLE_NAME) total

from dba_tables

where OWNER not in ('SYS', 'SYSTEM')

group by OWNER