3 Replies Latest reply: Apr 4, 2013 8:48 AM by riedelme RSS

    Difference of Alter Index Rebuild  and  Analyze Index Validate structuture

    Smile
      Hello Team ,

      After the big search in Google ,
      I couldn't understand the difference between
              ALTER INDEX <nm> REBUILD ONLINE 
             
      AND
             ANALYZE INDEX <nm> VALIDATE STRUCTURE 
             
      Could you please give me brief information .

      Thanks,
        • 1. Re: Difference of Alter Index Rebuild  and  Analyze Index Validate structuture
          sb92075
          Smile wrote:
          Hello Team ,

          After the big search in Google ,
          I couldn't understand the difference between
          ALTER INDEX <nm> REBUILD ONLINE 
          AND
          ANALYZE INDEX <nm> VALIDATE STRUCTURE 
          Could you please give me brief information .

          Thanks,
          ALTER INDEX changes structure while ANALYZE leaves index structure unchanged.
          • 2. Re: Difference of Alter Index Rebuild  and  Analyze Index Validate structuture
            asahide
            Hi,

            Validate structure is only checking of index.
            Did you mention about Coalesce?

            If so, check follow links.
            <<http://www.erpgreat.com/oracle-database/oracle-coalesce-vs-index-rebuild.htm>>
            <<http://richardfoote.wordpress.com/2008/02/08/index-rebuild-vs-coalesce-vs-shrink-space-pigs-3-different-ones/>>

            Regards,
            • 3. Re: Difference of Alter Index Rebuild  and  Analyze Index Validate structuture
              riedelme
              Smile wrote:
              Hello Team ,

              After the big search in Google ,
              I couldn't understand the difference between
              ALTER INDEX <nm> REBUILD ONLINE 
              AND
              ANALYZE INDEX <nm> VALIDATE STRUCTURE 
              Could you please give me brief information .
              ALTER INDEX whatever REBUILD ONLINE physically changes the index

              ANALYZE INDEX whatever VALIDATE STRUCTURE is used to populate the data dictionary - a session-specific data dictionary view called INDEX_STATS with useful information about the index such as height, deleted leaf rows, and so forth. This is the only reason to use ANALYZE INDEX since its use for generaly index analysis is no longer supported. DBMS_STATS analysis does not populate INDEX STATS so ANALYZE INDEX VALIDATE STRUCTURE is the only way to populate this view (unless Oracle added something when I was not looking - a quick internet search just now did not suggest this). The information in INDEX_STATS when populated can help decide if an index rebuild is a good idea