Rebuilding Index/Index Partition

  • 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

  • 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

  • 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

  • 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

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

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