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.




      (on Oracle