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. 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 ?

  2. I have to thank you for the efforts you have put in penning this
    site. I am hoping to see the same high-grade blog posts from you later on as well.
    In truth, your creative writing abilities has encouraged me to get my own, personal
    site now ;)