Re: Never rebuild Indexes?

From: HansF <Fuzzy.Greybeard_at_gmail.com>
Date: Mon, 05 Feb 2007 00:06:15 GMT
Message-Id: <pan.2007.02.05.00.06.13.526133@gmail.com>

On Sun, 04 Feb 2007 20:12:05 +0000, Oradba Linux wrote:

> HansF wrote:

>> On Sun, 04 Feb 2007 14:11:16 +0000, Oradba Linux wrote:
>>

>>> Frank van Bortel wrote:
>>>> Oradba Linux schreef:
>>>>> We are running a 4 node RAC on RHEL 3.0. We have a table with 300mil
>>>>> rows that has an pk index upto 12Gb and we rebuilt it it came down to
>>>>> 6g. The data into this table was loaded with concurrent inserts from
>>>>> different sessions. This table is on ASSM tablespace. Same thing with
>>>>> many other indexes.
>>>> Just wait a couple of weeks...
>>>>
>>> oh boy... You sound like a mystery novel.
>>>
>>> Do you feel it will bloat back up to 12G again. I am sure it will 
>>> increase but by how much is the question. I will not have any deletes to 
>>> this table. Many concurrent inserts from data loads.

>>
>> As Frank says - wait a couple of weeks, then re-evaluate.
>>
>> If it bloats back to the 12G, you run the typical "if it's Friday we
>> rebuild indexes" syndrome.
>>
>> Perhaps you may want to look at alternates to answer why it is
>> bloating. Block size may be a factor, as may be other items - check
>> the "Indexes" chapter in Tom Kytes books for ideas.
>>

> Are you referring to Expert one on one book

Yes. As one of the reference books. I usually go to http://www.apress.com and look under the Oracle section for ISBNs for Tom's books. I personally prefer the discussions in his latest (Expert ... Architecture)

The point [I think] Frank is trying to make is: an index may have a natural size - for a specific %used/%free setting, blocksize and transaction pattern. Rebuilding may result in a significant savings in size, but if the settings/blocksize/transaction pattern are maintained, the index will eventually return to that natural size.

Better to understand what is driving that natural size than blindly rebuilding.

-- 
Hans Forbrich   (mailto: Fuzzy.GreyBeard_at_gmail.com)   
*** Feel free to correct me when I'm wrong!
*** Top posting [replies] guarantees I won't respond.

Received on Sun Feb 04 2007 - 18:06:15 CST