Post reply

Indexing for Partitioned tables.

  • February 21, 2013 at 3:26 am

    #289890

    Hi All,

    I'm doing some R&D on Partitioning,

    If we create index on a partitioned table the index will be partitioned by default if we never mentioned "on File Group".

    I found one useful URL

    indexing-for-partitioned-tables

    From the above I'm with the point that "Indexes on partitioned table will reduce the data retrieval but insertion will be very fast compared un-partitioned tables"

    Here are my questions.

    How the index will be organized in indexes if the table is partitioned? Why the retrieval is slow compared to the unpartitioned indexes?

    🙂

  • February 21, 2013 at 3:46 am

    #1589871

    SQL* (2/21/2013)

    How the index will be organized in indexes if the table is partitioned?

    i didnt get this question ? please clarify

    SQL* (2/21/2013)

    Why the retrieval is slow compared to the unpartitioned indexes?

    If your query is written in such a way that it can read only the partitions it needs the data from then you will get partition elimination and therefore an equivalent performance improvement. If your query does not join or filter on the partition key then there will be no improvement in performance over an unpartitioned table i.e. no partition elimination. In fact, a query that hits a partitioned table has the potential to be even slower than than an unpartitioned table even if both tables have the same index defined. This is due to the fact that each partition in a partitioned table is actually its own b-tree which means that a partitioned index seek will need to do one seek per partition as opposed to one seek per table for an unpartitioned index seek.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 2 posts - 1 through 1 (of 1 total)

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