As I'm waiting for a CLUSTER operation to finish, it occurs to me that in a lot of cases, the performance benefits to
havingone's data stored on disk in index order can outweigh the overhead involved in inserting data on-disk in index
order....Just an idea I thought I'd throw out. :)
Also, the CLUSTER operation is about as straight forward as one can get. It basically reads each row, one-by-one, in
theindex order over to the new table, reindexes, then renames the new table to preserve references. I've been thinking
abouthow to speed up the copy process. Perhaps taking contiguous blocks of data and moving them into place would save
someI/O time. Locking the table is another problem. Would it be impossible to perform the CLUSTER within the context of
aREAD COMMITTED transaction, and then pick up the leftover CRUD rows and put them at the end of the file. The existing
codemakes some assumptions that the table was not altered. There would be no more assumptions.
I'm sure I'm not the first person to scratch his head thinking about CLUSTER. Maybe I just don't really understand the
limitationsthat are out there preventing these things from being created. But, what else is there to do at 1AM on a
Sundaynight waiting for a 500MB table to CLUSTER? :)
CG