Re: Global Index vs Nonprefix Local Index in Oracle 8.

Rohit

The general rule of thumb is that you should never use global indexes on partitioned tables (until 9i). The reason for this is that any DDL against the partitioned table will cause the entire global index to be unusable - it will therefore need to be rebuilt - a costly and largely unacceptable price to pay in a production system.

Now I'm assuming that if you have a huge SALES table, that you will regularly be phasing out old data and adding new data - which implies a lot of partitioned table maintenance (DDL). Use a local partitioned index - these are what you should be using. They are self maintaining (i.e. if you drop a partition, the corresponding local index partition is dropped or if you add a new partition, a new index partitioned is created and so on). Also, local index partitions can be re-built individually if needs be (though I've never had to do it ever).

I'm sure that most Oracle guys would agree that global partitioned indexes are very useful for huge heap tables but were never intended to be used against partitioned tables as such.

Hope this helps.

Regards

Adrian Received on Fri Mar 22 2002 - 02:22:25 CST