July 3, 2008 at 4:46 am
Hello.
I can't be alone on this one..
I'm looking at implementing partitioning on our fact tables, all our fact tables have a DateKey and 95% of queries use date criteria. So this is the Column i have chosen to partition on.
However this key is a meaningless key, a join to dimDate must be made to filter rows by date attributes.
Because of this the optimizer doesn't apear to be eliminating partitions correctly. I did suspect this might happen because the documentation does say that the actual partitioing column must be used in the query for the optimizer to elimiate partitions.
People must have come across this before? How have other people got round this?
Thanks
Bob.
July 18, 2008 at 2:37 am
What indexes do you have on the fact table?
I typically have a composite primary key consisting of the keys from the dimension tables and I cluster this index with the date as the first column of the clustered index. The data is then physically sorted by date. Any query using date (which is almost all of them) will be directed to the right portion of the table mimicing what you are trying to do with partitions.
Jez
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply