Forum

Discussions specific to version 8.1
Contributor
Offline
User avatar
Posts: 386
Joined: Thu May 23, 2013 4:48 pm

What attributes and expressions should I partition on?

by markc » Mon Oct 21, 2013 11:28 am

In 8.1 I can create partitioned images, allowing literal filters to eliminate partitions without having to scan through their data. I understand that concept, but how do I decide what to partition my images by?
Reply with quote Top
Contributor
Offline
User avatar
Posts: 386
Joined: Thu May 23, 2013 4:48 pm

Re: What attributes and expressions should I partition on?

by markc » Mon Oct 21, 2013 11:32 am

Generally speaking you want to have a lot more records per partition than partitions, and you want to have of the order of 10s or 100s of
thousands of records per partition, not 1000.

You also want to partition on attributes which will benefit from partition elimination - e.g. if you have 50 million customers and run a query which will hit 100K of them, if you have 10K partitions you will probably hit every partition as the customers of interest are likely to be in random partitions rather than clustered together in a small number of partitions. However, if you partition on date it is likely that your date restrictions will pick contiguous chunks of dates, and hence you will benefit from partition elimination.

Remember that the default limit for partitions per ram store (RS) is 10,000. So you will need to ensure that you do not exceed that.

You can partition on multiple attributes - e.g. for a retail chain you might partition your fact table by date and some expression based on store if you have lots of queries which predicate on date, and/or on store number. In this case the partitioning elimination can eliminate partitions based on a date predicate, or a store predicate, or both. The total number of partitions in the system will be the product of the number of distinct date expressions and the number of distinct store expressions in the partitioning clause.
Reply with quote Top

Who is online

Users browsing this forum: No registered users and 1 guest

cron