January 18, 2005 at 2:41 pm
Data is imported to and exported from database only during nightly batch processing (millions of records daily). Table with largest volume of data was partitioned horizontally, each partition on it's own filegroup spread evenly accross several drives. All partitions read-only except 1 (new data is imported to this partition). All selects performed on partition view, 90% of selects are performed using partition key, the other 10% use an alternate column, this is my concern.
Do I:
A. Create a composite clustered index on partition key and alternate column.
B. Create a clustered index on partition key and non-clustered index on alternate column.
Which is going to be better for performance?
January 20, 2005 at 4:38 pm
we have a similar set up and found it necessary to test the scenarios on a smaller subset. where indexes are place may partially have to do with the qrys running the rest of the time too.
Having said that, I would look at building your composite index on the clustered index. If you do this the space used will be smaller than doing a nonclustered index. A nonclusted index size is the clustered index key + the columns you are indexing on.
It also depends on what your partition key is. If it is date and there is only one per table then including it in an index is pretty usless. It might also be useless if nothing else uses it (such as an identity with no other value then to serve as primary key)
Hope that helps.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply