Re: Clustered Indices

tcummins@ix.netcom.com (Tim Cummins) wrote: >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? >> >>TIA >> >> >>Pete Harris >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. >Tim Cummins > Not quite right Tim but close. The index is not a dynamic clustered index, it does not re-write the data rows. Once the rows are clustered and a new row is inserted etc. The data becomes unclustered ie; the row could be added to the end of the table. A cluster index should only be used on static tables where you want the benifit of making use of your read ahead data buffers. Or an index can be altered to cluster to reclaim space in a table that has been fragmented by deleting rows. Down side: if you have a 100 Mbyte table you need 100 Mbytes of space to rebuild the table. gordonh@acslink.net.au RIPLEY, Queensland, Australia