Rebuilding Index post dropping partition
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Rebuilding Index post dropping partition

  1. #1
    Join Date
    Sep 2008
    Posts
    10

    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.

    Ques# 2: Does the activity requiresrebulilding unusable local index?

    Rgds

  2. #2
    Join Date
    Dec 2008
    Location
    Easton,MA
    Posts
    13
    Hi,

    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.

    Regards,

    Shaji.
    Shaji
    DBA(oracle,sqlserver,hyperion essbase,peoplesoft).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width