Re: rebuild index

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 05 Oct 2006 11:05:18 -0700
Message-ID: <1160071517.371683@bubbleator.drizzle.com>

Andrea wrote:
> frank.van.bortel_at_gmail.com wrote:

>> Andrea schreef:
>>
>>> Hi,
>>>
>>> someone can tell me if there are some warning if i want to rebuild all
>>> indexes of my database (oracle 10g), can i do it without any problem?
>>>
>>> thanks very much
>>>
>>> bye
>>> andrew
>> There have been numerous discussion about the unnecessity of rebuilding
>> indexes - especially the "normal" (B-tree) indexes.
>>
>> What type of indexes do you want to rebuild, and -more importantly-
>> why?

>
> in effect i would like rebuild indexes for only increase the
> performance and for maintenance...
> my DB is for DML type of statements.
> So, may enough to run coalesce for all indexes .. right?
>
> thanks for your support

Likely a complete waste of time and CPU. Rebuilding indexes is something you should only do when you have metrics supporting the fact that there is a need to do so.

One way to determine whether an index requires rebuilding is to use the built in SYS_OP_LBID function. I have a demo of its usage in Morgan's Library under Undocumented Oracle and there is some documentation by Jonathan Lewis you can find if you google for the function by name.

-- 
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group

Received on Thu Oct 05 2006 - 13:05:18 CDT