Re: Clustered Indices

In article jql@ixnews3.ix.netcom.com, tcummins@ix.netcom.com (Tim Cummins) writes: >In <3p6toh$ojl@cssun.mathcs.emory.edu> phr@fmsc.com.au (Peter Harris) >writes: >> >>I have RTFM but I am still a tad mystified, when do you use clustered >>indices and when do you avoid them? what benefits do they bring, what >>costs do they impose? > >According to my manual, clustering an index will place the records in >physical order based on the index. This is a very good thing if the >table is a stable one. It means the table can be searched more quickly >along that index. It is not a good idea to cluster if the table >undergoes frequent changes. When a is record inserted or deleted, every >record after the inserted/deleted record (based on the clustered index) >must be rewritten to maintain the physical order. This could add a lot >of overhead to the system. Sorry but this is wrong. After clustering, the data will be in physical key order. Any changes to the data will _not_ be clustered and the physical ordering will not be maintained. There is no shuffling of data so no perfomance hit, but a highly volatile table will quickly become unordered, reducing the benefit of clustering. Static tables will benefit the most. Cosmo