Re: index rebuilding...

From: Frank <F.Preikschat_at_gmx.de>
Date: Thu, 30 Jan 2003 06:42:23 +0100
Message-ID: <b1advu$sbh$1@news1.ewetel.de>

Think about fragmentation of your tablespace, if you change only the storage params and not the tablespace, you will fagment your tablespace. On this way you can move the INDEX into an other Tablespace (why not local-managed).
It is better to create three local-managed tablespaces (index_large, index_middle and index_small) with large, middle and small extent size.

Frank (Germany)

Glen A Stromquist <glen_stromquist_at_no.spam.yahoo.com> schrieb in im Newsbeitrag: FSYZ9.61355$c41.1436929_at_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 - 23:42:23 CST