If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Rebuilding Index post dropping partition

Hi,

we are exchanging the trailing partition to a non partitioned table. In the code I saw that

1) Copy some records (20%) from the trailing partition to a temporary table
1) then it is exchanging the traling partition with another table (non partitioned)
2) Then it is rebuilding the unusable local index on trailing partition
3) then it is dropping the traling partition

Ques #1) since we are dropping the traling partition, to me the step 2 is not reuired. Is that correct?

but with that note I would also like to say that, once the traling partition has been droped the code copy above 20% records from the temporary table to the NEW trailing partition.

Dont know why you are transferring 20% of the data back to the trailing partition after the first 4 steps. Didnt understand the purpose of the first 4 steps itself. Also missing is the point: How is the NEW trailing partition getting created ? Want to know how the 20% of the data from the OLD trailing partition is fitting into the NEW trailing partition without redefining the partition range of the NEW trailing partition.

Bottom line:
========

If the partition which you are exchanging back/forth has data, then you have to rebuild the unusable index to make it Usable/Available. If all the steps that you have listed leaves an local/global index in a unusable state, you have to rebuild to fix it. Instead of blindly rebuilding it, you can always check the current state of the index partition and rebuild it only if its unusable.