A table has a PK. Every PK compbination has average 75 rows for every month. Total number of rows around 50 million. So, it is calling for partitioning.
Making the partitions based on the date in the row seems most logical. The data in the table gets processed for a "month".
My question is, if I partition based on range in PK column and distribute the partiotions across datafiles (and hence disks) will I be distributing I/O ? So, data for a month is distributed across disks and the processing can be faster ?
Is this a correct view to partitioning ? Will it have considerable performance advantage over partitioning based on date range ? Obviously, the date based partitioning will be easier to maintain.
So, which is good ?