Indexes - Rebuilds and other considerations

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:

1. 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 Tue Jul 07 1998 - 00:15:44 CDT