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 a sample: …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:

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

 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


© Copyright SS64.com 1999-2014
Some rights reserved