Partitioning

Question

Post reply

Partitioning

Lexa

Hall of Fame

Points: 3314
More actions
October 8, 2011 at 6:16 am

#260130

Hi,
I've an Items table partitioned on item_id, which is also a primary key and clustered index. The partitioning scheme puts 500K of item_ids per partition. For reretrieval of information from Items tables based on items_id, would it be beneficial to add partition id to Items table as part of clustered index? I was thinking about creating a function that returns partition id based on items_id, then use both partition id and items_id when joining to Items table. Would it improve performance to have partition id in the table? Or having to make an additional call to a function to determine partition id would just slow things down in an OLTP system?
Thanks

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

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

vince_sql Right there with Babe Points: 722 More actions · Answer 1

You can return the partition ID this way:

$partition.partitionFunctionName(partitionColumn)

http://sqlvince.blogspot.com/[/url]

SpringTownDBA SSCertifiable Points: 6917 More actions · Answer 2

Joining to a table on a single int clustered primary key is as about as efficient as it gets. ( clustered index seek)

Adding partition _id will bloat your non clustered indexes and also effectively preclude merge joins based on item_id.

Do you have a particular query that isn't performing?

Otherwise, i'd leave it alone.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

Lexa (10/8/2011)
I've an Items table partitioned on item_id, which is also a primary key and clustered index. The partitioning scheme puts 500K of item_ids per partition. For reretrieval of information from Items tables based on items_id, would it be beneficial to add partition id to Items table as part of clustered index?

Optimizer knows which specific partitions to touch therefore - since query is going to access data by PK - it doesn't make sense to manually point to a specific one.

If in doubt, you may want to implement in development database and compare execution plans.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.