13 Replies Latest reply on Jul 13, 2015 7:51 AM by ranit B

    Reg: Index - Gathering statistics v/s Rebuild -

    ranit B

      Hi Experts,

       

      I have a conceptual doubt on Index statistics - when to gather the stats, and when to rebuild it?

       

      Scenario -

      We have say 10 (big) tables which we archive it to our archive tables, in a period of every 4-5 months. We archive 70% of the data keeping only latest (dated) 30% in it.

       

      The technique we use is using - use CTAS for temp tables, truncate the actual table, reinsert the data back and drop the temp table.

      After this I am doing gathering the table statistics.

       

      Now, the doubt is, shall I gather the index stats (manually), or, rebuild the index?

       

      Could you please give me some pointer to understand when the index rebuild should be performed? What will be the pro/con ?

       

      p.s. I've never done index rebuild before.

       

      Thanks,

      -Ranit

      (on Oracle 10.2.0.4.0)