February 21, 2013 at 3:26 am
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
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