Rebuild indexes

[farrokhp]

Is there any disadvantage to rebuild all user indexes lets say everynight ( Off peak time ) ???

[pipo]

well, it's not needed ... I mean Oracle manages it's indexes alone, rebuild is more used to unfragment or to move indexes, but except the fact that it consumes time and CPU resources, there is no problem in doing that ... if you have no query running of course

[jgmagnus]

Why would you want to if they are not defragmented?
If you do not have activity overnight, then I suppose there is no problem doing it if you want.

- Magnus

[moff]

Hi,

I'm not aware of any as long as the rebuilds are complete before the workload on the database increases (so not to impede performance)

What version of Oracle are you using? You may be able to generate your statistics on the rebuilt index online during the rebuild.

Cheers
Moff

[jmodic]

Sure there are negative implications. For example, all the cached blocks of the frequently used index will be flushed out of the buffer cache, resulting in decreased performance immediately after the index rebuild.

[Zamial]

Should you analyze them weekly?

[farrokhp]

We use 8.1.6. Do you have any scripts which first find out which indexes needs to be rebuild and then issue rebuild commands. Sounds like this is a better approach. Please post the script.

Also what happen if several queries issues in the middle of rebuild ?? Chances of Halt or Hang ??

[sambavan]

Not much possibility for hang, it would only lead to a slow down. "Virtual" hang.

Sam