ANALYZE Statement

Update statistics.
Now superceeded by GATHER_SCHEMA_STATS

Syntax:

   ANALYZE TABLE tablename COMPUTE|ESTIMATE|DELETE STATISTICS ptnOption options
   ANALYZE INDEX indexname COMPUTE|ESTIMATE|DELETE STATISTICS ptnOption options
   ANALYZE CLUSTER clustername COMPUTE|ESTIMATE|DELETE STATISTICS options

ptnOption
   PARTITION (partion)
   SUBPARTITION (subpartition)

options
   VALIDATE STRUCTURE [CASCADE] [INTO tablename]
   LIST CHAINED ROWS [INTO tablename]
   COMPUTE|ESTIMATE STATISTICS FOR TABLE
   COMPUTE|ESTIMATE STATISTICS FOR ALL COLUMNS
   COMPUTE|ESTIMATE STATISTICS FOR ALL INDEXED COLUMNS
   COMPUTE|ESTIMATE STATISTICS FOR COLUMNS [SIZE int] column [SIZE int]

   When Estimating statistics you can optionally 
    specify
    ... ESTIMATE STATISTICS SAMPLE n ROWS
    ... ESTIMATE STATISTICS SAMPLE n PERCENT

Validate structure will perform an integrity check - and will therefore lock the table/index/cluster while it is running.

If the INTO clause is used to store a list of chained rows in a table - the default tablename is CHAINED_ROWS

UTLCHAIN.sql - creates the chained_rows table

The ANALYZE command is available for all versions of Oracle, however to obtain faster and better statistics use the procedures supplied - in 7.3.4 and 8.0 DBMS_UTILITY.ANALYZE_SCHEMA, and in 8i and above - DBMS_STATS.GATHER_SCHEMA_STATS

"50% of the population has a below average understanding of statistics."- Anon

Related Commands:

DBMS_UTILITY.ANALYZE_SCHEMA
DBMS_STATS.GATHER_SCHEMA_STATS
DBMS_DDL - compile/analyze objects
DBMS_IOT.BUILD_CHAIN_ROWS_TABLE

EXPLAIN PLAN
STATISTICS - ASSOCIATE STATISTICS
STATISTICS - DISASSOCIATE STATISTICS

Related Views:

 INDEX_STATS
 DBA_PART_COL_STATISTICS ALL_PART_COL_STATISTICS USER_PART_COL_STATISTICS 
 DBA_TAB_COL_STATISTICS  ALL_TAB_COL_STATISTICS  USER_TAB_COL_STATISTICS 
 DBA_USTATS              ALL_USTATS              USER_USTATS 
 V$BUFFER_POOL_STATISTICS

Equivalent SQL Server command:

CREATE STATISTICS