Oracle: Analyze Table or Index

Analyze command

The ANALYZE command is to obtain faster and better statistics use the procedures supplied.
The analyze table can be used to create statistics for 1 table, index or cluster.

ANALYZE table tableName {compute|estimate|delete) statistics options
ANALYZE table indexName {compute|estimate|delete) statistics options
ANALYZE cluster clusterName {compute|estimate|delete) statistics options

Code examples
ANALYZE table scott compute statistics;
ANALYZE table scott estimate statistics sample 25 percent;
ANALYZE table scott estimate statistics sample 1000 rows;
analyze index sc_idx compute statistics;
analyze index sc_idx validate structure;

Related Posts:
- DBMS_STATS Gather Statistics of Schema, Tables, Indexes
- Query Optimization Tips for Oracle
- DBMS_PROFILER: Overview and How to Install
- Oracle : TKPROF simple steps
- SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
- Doubt: Truncate Command and Table Statistics

1 comment:

  1. Great Article but it would have been better if you please add the contents as to when to use the above statements.

    Why do we actually analyze table/ index/ cluster ?

    What happends in the background when the above runs ? What is actually collected etc.

    This is for which version ?