Re: index rebuilding...

From: Glen A Stromquist <glen_stromquist_at_no.spam.yahoo.com>
Date: Wed, 29 Jan 2003 22:48:05 GMT
Message-ID: <FSYZ9.61355$c41.1436929@news2.telusplanet.net>

Ryan wrote:

> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> news:v3ghe8qj8vstf4_at_corp.supernews.com...
> 

>>"Glen A Stromquist" <glen_stromquist_at_no.spam.yahoo.com> wrote in message
>>news:o%WZ9.61302$c41.1415158_at_news2.telusplanet.net...
>>
>>>I want to rebuild the indexes on one of our databases, this particular
>>>one has some indexes that have what seems to be some really out-to-lunch
>>>storage settings, so I want to set new ones in the rebuild.
>>>
>>>I want to write a dynamic script to build the rebuild statement with the
>>> optimum storage params for each index, either that or find a script
>>>that does such a thing, but my initial searches have proven fruitless
>>>for what I want to do. Which dd tables/views should I be hitting for
>>>this query to build the rebuild statement?
>>>
>>>suggestions?
>>>
>>>TIA
>>>
>>
>>alter index rebuild can rebuild the index without knowing it's column
>>definition.
>>That said you would only need dba_indexes and dba_segments (the

> 
> segment_name
> 

>>equals the index_name)
>>I saw a script today, but unfortunately I don't remember the author.
>>The commercial version of Toad has a separate module which allows you to
>>point and shoot at the problem.
>>
>>Hth
>>
>>
>>--
>>Sybrand Bakker
>>Senior Oracle DBA
>>
>>to reply remove '-verwijderdit' from my e-mail address
>>
>>

> 
> 
> I could have swarn I read something either in Tom Kyte's book or on his site
> stating that rebuilding indexes is a bad idea? I saw something about how an
> index will eventually get to a state of equilibrium and you may experience
> slowdowns while it reestablishes that equilibrium.
> 
> you know any documentation on this? about when to rebuild indices and such?
> 
> 

well, I also discovered a few indexes that were also in the data tablespace, so I ran the script that this script generated:

select 'alter index <OWNER>.'||segment_name|| ' rebuild tablespace INDEX storage(initial '||round(bytes/1024)|| 'K next '||round(next_extent/1024)||'K pctincrease 0);' from dba_segments
where owner = '<OWNER>'
and tablespace_name not like '%INDEX%'
and segment_type = 'INDEX'

This moved the index to the correct tablespace, and set the initial extent to the size of the index in K, and next_extent to what it already was set at, next I will find all of the indexes that have a pctincrease  > 0 and adjust accordingly, and I think I should be able to use this same script to modify the storage for all the indexes with out-to-lunch storage settings by modifying the next_extent clause to a standard size of 1M or 5M.

Still like to hear opinions if there is a better way to do this....

TIA Received on Wed Jan 29 2003 - 16:48:05 CST