CODEWITHSUNDEEP
oracle-databaseindexing
Dreamer
Dreamer

Reputation: 7549

What is the pros and cons to rebuild index regularly

Is it reliable and healthy to run a script and rebuild all index under current schema regularly (like every week or every day)? Is there any chance that it causing data issue or making redundancy to the system? can we trust index rebuild query?

alter index SYS_C0078275 rebuild;                  
alter index SHOPPER_ID rebuild;

Upvotes: 2

Views: 2854

Answers (1)

Justin Cave
Justin Cave

Reputation: 231681

Generally, indexes do not need to be rebuilt in Oracle. Generally, what you're doing is causing extra work for the database and (unless you've got the enterprise edition and are doing an online rebuild) extra downtime for your application(s). For indexes on columns with a lot of random inserts (names for example), you may taking an index that is at a nice steady state, doing a lot of work to compact it, only for Oracle to do a lot of work over the next week to split a bunch of blocks to get back to that steady state. Plus, it's one more job that has to be monitored, that can fail, etc. As a general matter, there is very little upside (queries that involve doing a full scan on the index may be a bit faster for a while) and lots of potential downsides so it's not something I would recommend doing.

That said, plenty of sites do index rebuilds regularly. If you already have a downtime window to do the rebuild, the performance downsides may not be terribly problematic. It's possible that you have one of the rare systems that has one or two indexes that actually benefits from a periodic rebuild (though it would be more likely that you'd want to fix the underlying issue that causes the index to need to be rebuilt) in which case rebuilding everything might be beneficial even if it's overkill.

The canonical discussion on index rebuilding is Richard Foote's Rebuilding the Truth. If you want a more detailed discussion, that's where you want to go.

Upvotes: 3

Related Questions