Re: Indexes - Rebuilds and other considerations

If you drop your primary key constraints the associated index will also be dropped. You can then recreate the constraint and specify new storage parameters for the index:

ALTER TABLE tab DROP PRIMARY KEY;

ALTER TABLE tab ADD PRIMARY KEY (column) USING INDEX TABLESPACE ts STORAGE (INITIAL nn NEXT nn);

Chris Kempster wrote:

> Dear all,
>
> I have around 350Mb (i know, nothing really) of indexes over 3 data files
> (for one tablespace called wes_index). My question is:
>
> a) Best method of re-organisation?
>
> The problem I have with re-orgs of indexes is that the PK constraints that
> create associated indexes can be a real problem. All my indexes are in the
> one tablespace, wes_index, I can not simply drop ALL the indexes in the
> tablespace, as the PK ones are linked to the constraints. This is a problem
> as I still have a reasonably fragmented database after rebuild the non-PK
> indexes.
>
> In the end, I simply dropped the schema owner and rebuilt the entire schema
> via a compressed export so my tablespaces are totally re-orged. Of course,
> this took some time.
>
> I suppose, with the correct storage settings fragmentation is less of an
> issue and can be addressed more proactively from index to index.
>
> Any ideas as to the recommended setup of a tablespace with large indexes,
> some that are on tables that have heavy transactions usage, ie. 100,000
> records dropped/added each day, and methods used to manage their
> re-organisation?
>
> I know of one site that has two large tablespaces, ind_1 and ind_2, where
> they periodically rebuild all indexes from one to another via the 'alter
> index rebuild command'. Any thoughts on this?
>
> b) Can an extent, extend over multiple data files?
>
> Its an old one but I cant quite remember. I would like to consolidate my
> data files into one, although i believe it may be necessary to divide my PK
> (primary key) indexes and perhaps my indexes used for large downloads into
> separate index tablespaces. Again, any ideas/thoughts on this area would be
> great.
>
> Thanks
>
> Chris.
Received on Wed Jul 08 1998 - 02:49:40 CDT