November 20, 2008 at 6:28 am
I've inherited a data warehouse and I've noticed a table with 200 million rows in it that is partitioned on a date column using a partition function. As usual there's no design documentation (or any documentation for that matter) and I'm left wondering why the decision to not add a clustered index was made. Or if it was even made at all - maybe someone forgot to add it.
I've not had much opportunity to work with partitioned tables before so I'm not entirely sure if the rules for partitoned tables are any different regarding indexes (although I couldn't find anything to suggest this is the case).
Is there any reason why you wouldn't put a clustered index on a partitioned table? And I mean any good reason. Saving on disk space is not a good enough reason by the way (you'd be surprised how often I've heard that excuse before) 😉
Thanks,
November 20, 2008 at 3:23 pm
There is no reason to avoid clustered index. You should ahead and create one on the same partition function, so that you have a aligned clustered index. But remember there will be huge IO cost involved in creating this index for obvious reasons.
November 20, 2008 at 3:35 pm
what columns does the table have?
which column(s) were you thinking of creating the index on?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 20, 2008 at 3:46 pm
Perry Whittle (11/20/2008)
what columns does the table have?which column(s) were you thinking of creating the index on?
I can't remember off the top of my head the list of all the columns but it's a fairly wide table. The specific column I was looking at was the date column in the table that is used for the partition function.
There's a foreign key column (integer) that is used quite often in queries to join on another table but seeing as the table is partitioned by date and that date is almost always used to filter queries on this table, the date column seems like a more natural choice to me.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply