January 29, 2013

Difference to between COMPUTE STATISTICS and VALIDATE STRUCTURE for ANALYZE INDEX

TWO WAYS to analyze index:
ANALYZE INDEX index_name COMPUTE STATISTICS;
ANALYZE INDEX index_name VALIDATE STRUCTURE;


COMPUTE STATISTICS:
  • populates statistics for cost base optimizer mode
  • using all the target data to compute index
  • create histogram based statistics
  • It is for making better explain plan for optimizer

  • VALIDATE STRUCTURE:
  • just a diagnostic tool -- not for statistics gathering!
  • detect index physical/logical corruption of segment
  • populates some statistics in index_stats for session level
  • check index integrity/fragmentation

  • PRACTICE:
    index_stats only stores a single line of data (in the same session, new value replaces the old one)
    SQL> analyze index pk_emp validate structure;
    
    Index analyzed.
    
    SQL> select name from index_stats;
    
    NAME
    ------------------------------
    PK_EMP
    
    SQL> analyze index pk_dept validate structure;
    
    Index analyzed.
    
    SQL> select name from index_stats;
    
    NAME
    ------------------------------
    PK_DEPT
    
    SQL> disconnect;
    Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
    With the Partitioning, OLAP and Data Mining options
    SQL> conn scott/tiger
    Connected.
    SQL> analyze index pk_emp validate structure;
    
    Index analyzed.
    
    SQL> analyze index pk_dept validate structure;
    
    Index analyzed.
    
    SQL> select name from index_stats;
    
    NAME
    ------------------------------
    PK_DEPT
    
    

    online/offline option Test
    SQL> conn scott/tiger
    Connected.
    SQL> analyze index pk_emp validate structure online;
    
    Index analyzed.
    
    SQL> select count(1) from index_stats;
    
      COUNT(1)
    ----------
             0
    
    SQL> analyze index pk_emp validate structure offline;
    
    Index analyzed.
    
    SQL> select count(1) from index_stats;
    
      COUNT(1)
    ----------
             1
    

    No comments:

    Post a Comment