Post reply

Rebuilding Index/Index Partition

  • September 21, 2008 at 9:35 pm

    #212905

    Hi,

    Can anyone explain for me whether can i perform all the operations (operations we perform using ALTER INDEX statement on a non-partitioned index) on a single partition of any index?

    For example

    1. Re-organizing index partition (would other partitions not be affected?)

    2. Re-building a single index partition (would other partitions not be affected?)

    3. Can we use all available options available for indexes, while working on partitions for example allow_row_lock, online, and other rebuild Or set options.

    Is there any difference between the above two other than, effecting on a single partition and effecting whole index.

    Please correct me if i am wrong "Table is not available during OFFLINE index rebuild" Neither for DMLs nor fro SELECT.

    Last question is about implications of ONLINE index rebuild. What are the draw backs and what should we keep in our mind during online rebuild. Is data 100% availble during ONLINE rebuild

    Thanks in advance

    Salman

  • September 22, 2008 at 9:03 am

    #875048

    Can anyone explain for me whether can i perform all the operations (operations we perform using ALTER INDEX statement on a non-partitioned index) on a single partition of any index?

    Yes, you can.

    ONLINE is completely online

    and offline reindexing does make the table and underlying objects unavailable.

    Cheers!

    ~Craig

    Craig Outcalt

    MCITP, MCDBA

    Craig Outcalt


    Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
    My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

  • September 22, 2008 at 6:28 pm

    #875350

    Hi,

    Thanks for your reply. My main question was, can we do same to a single partition of an index? Is rebuilding a single partition same as rebuilding a whole nonpartitioned index?

    What about other three questions?:)

    Thanks

    Salman

  • September 23, 2008 at 7:02 am

    #875612

    yes to all.

    I thought your other questions were example questions.

    It's the same thing as a regular reindex, only when you specify a partition, only that partition is reindexed.

    Oh, and specifying a partition when none exists will cause ALTER INDEX to throw an error.

    ~Craig

    Craig Outcalt


    Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
    My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply