Rebuild Oracle Database Indexes

We are running Oracle database RAC 12c.  Is it still a good practice to rebuild Oracle database Schema user indexes periodically?  Our previous Oracle DBA had maintenance tasks that he performed once a month, and I believe rebuilding the schema indexes was one item.  Is this necessary, and does this provide any benefit?   After rebuilding indexes do we need to update statistics?
LVL 1
sdrussAsked:
Who is Participating?
 
sdstuberCommented:
In general no, it's not helpful.
But - if you have mass-but-incomplete purging you could get a benefit by reclaiming space.  However, if this is the case, then a coalesce should be sufficient.


A rebuild definitely does not require updating statistics.
1
 
Mark GeerlingsDatabase AdministratorCommented:
I basically agree with sdstuber, especially the first and last lines in his comment.

In addition to the "incomplete purging" that he mentioned (where a process deletes many, but not all records from a table) two other kinds of actions can also result in indexes that benefit from regular rebuilds:
1. indexes on tables that get lots of inserts *AND* the indexes are *NOT* based on a number that keeps getting higher for each insert
2. updates that change the value of indexed columns

These actions are not unusual in many Oracle systems.
0
 
Geert GOracle dbaCommented:
nope, not necessary

it was necessary on DBase IV, Paradox, ... databases, back in the eighties and nineties

Some vendors still believe this is necessary and succeed in convincing a dba to do that.
A good dba knows when to say NO to such vendors.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.