I have a fact table that grows at a rate of a little less than 1M records
per month, and performance is starting to suffer (we need to support
historical analysis, so purging old data is not a viable option). As part
of our migration from 7.0 to 2000, I would like to move to the use of
partitioned views. What I am trying to do is figure out the most effective
design for setting up this partitioned view. Queries against this fact
table are about an even mix of summary queries and detail record retrieval.
I will also be using indexed views to help with aggregate query performance,
but I would appreciate suggestions as to how to determine how large to make
each table underlying a partitioned view. Candidates for partitioning
criteria include fiscal month, fiscal quarter, and fiscal year.