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.

Syntax:
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:
- Oracle: DBMS_UTILITY.ANALYZE_SCHEMA
- 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

2 comments:

  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 ?

    ReplyDelete
  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 ;)

    ReplyDelete