Rebuilding Index post dropping partition
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.
Ques# 2: Does the activity requiresrebulilding unusable local index?
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.
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.
Click Here to Expand Forum to Full Width