SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Reg: Index - Gathering statistics v/s Rebuild -

915396Jun 29 2015 — edited Jul 13 2015

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)

Locked Post

New comments cannot be posted to this locked post.

Locked on Aug 10 2015

Added on Jun 29 2015

13 comments

15,791 views