Re: When should one rebuild an index?

From: raja <dextersunil_at_gmail.com>
Date: Wed, 14 Jan 2009 03:37:58 -0800 (PST)
Message-ID: <cfb76cdc-125c-4e81-bf77-63caccd1dc3c_at_g1g2000pra.googlegroups.com>

Hi,

Thanks for your immediate response.

I have a Oracle 10gR1 database used for datawarehousing. I already have the database partitions for every month... Now i have to extend the partition. ( In these partitions some have data in max partition and some dont have data in max partition ) to extend the partition, we have to disable the constraints and enable after partitioning and similarly we have to drop the indexes and create the indexes after partitioning.
Instead of dropping and creating the indexes again, i though of rebuilding the indexes. i felt that this would save time. so to rebuild the indexes on which partition was done ( now the indexes should be in UNUSABLE state ), i felt that i should check whether the following scenarios would work out properly (i.e, i wanted to be sure that rebuilding the indexes should be a problem under the scenarios that i have mentioned you already ).

1. first i want to know, how to check whether there is data in the max partition ?
2. second, how to find whether an index is a local index or a global index ?

I would like to tel the scenarios again :

1. rebuilding indexes on a max partition with data
2. rebuilding indexes on a max partition with data on local indexes
3. rebuilding indexes on a max partition with data on global indexes
4. rebuilding indexes on a max partition without data
5. rebuilding indexes on a max partition without data on local

indexes
6. rebuilding indexes on a max partition without data on global indexes

Table partition details are as follows :

---

partitioning_type subpartitioning_type

list                         none
range                        hash

Index partition details are as follows :

---

partitioning_type subpartitioning_type

range                        hash

index type details are as follows :

---

NORMAL
IOT - TOP Please explain me the above doubts on index rebuilding...

With Regards,
Raja. Received on Wed Jan 14 2009 - 05:37:58 CST