Re: Clustered Indices

Peter Harris writes: -> ->On Mon, 15 May 1995, Cathy Kipp wrote: -> ->> The manual may be misleading (I haven't looked at this section recently), ->> but half of the above is incorrect. When you cluster an index the rows in ->> your table are physically ordered on disk by that index. If you should add ->> new rows, change rows, or delete rows, the clustering will not be maintained ->> (because of the performance implications). So basically you have a good ->> starting point. Part of the maintenance of creating a clustered table is to ->> recluster it periodically so that all rows will once again be physically ->> ordered on disk. -> ->So what happens when a clustered index is created on a dynamic table at ->the moment of its creation and never again reclustered - which is quite ->likely if the application developer is not the same person as the DBA? The only thing that will happen is that you will likely see your performance deteriorate as the table becomes less clustered as more more changes are made. ->Is there a performance penalty for using a clustered index in this ->circumstance? Clustered index taking up more disk space? Query optimiser ->making the wrong guess because rows are not in the expected order? No penalty. They only penalty I know of is that you have to have enough room in the dbspace (if you're using OnLine, if SE, just enough disk space) to hold two copies of the table in question since it is completely rewritten on disk. There is no performance penalty, although your performance should improve if you are able to recluster periodically. I'm pretty sure the optimizer doesn't do anything special with clustered indexes. The clustered index itself won't take any additional space, except at the time it is being created. Regards, - Cathy -------------------------------------------------------------------------------- Cathy Kipp e-mail: ckipp@vth1.vth.colostate.edu Phone: (970) 491-1294 Colorado State University Veterinary Teaching Hospital Fax: (970) 491-1205