Indexing for Partitioned tables.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Indexing for Partitioned tables.

Indexing for Partitioned tables.

Rate Topic

Display Mode

Topic Options

Author

Message

SQL*

Posted Thursday, February 21, 2013 3:26 AM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 12:54 AM
Points: 414, Visits: 1,455

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?

Smile

Post #1422475

Bhuvnesh

Posted Thursday, February 21, 2013 3:46 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1422482

« Prev Topic | Next Topic »

Permissions