Re: Clustered Indices

Peter Harris (phr@fmsc.com.au) wrote: : 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? - Cost: only when a 'alter index indexName to cluster' is executed (Similar to the update statistics command) - Usage: daily(nightly), weekly, monthly, etc... depending on the table's volatility and the need to be physically clustered. - Target: Statistical data. Data which is read in groups (e.g. order items/lines) (based on the clustered index, usually the primary one). - Benefits: faster disk access when a range of records is selected, since rows may be in the same logical/physical disk block or an adjacent one. - Avoiding them: when they don't bring any benefits :-) - Example: Non-Clustered Table Clustered Table Block 0 R1 R9 R4 Block 0 R1 R2 R3 Block 1 R7 R2 R6 Block 1 R4 R5 R6 Block 2 R5 R8 R3 Block 2 R7 R8 R9 Statement: select ... from table where keyField between 2 and 7 Read Block 1 R2 Read Block 0 R2 Read Block 2 R3 same Block 0 R3 Read Block 0 R4 Read Block 1 R4 Read Block 2 R5 same Block 1 R5 Read Block 1 R6 same Block 1 R6 same Block 1 R7 Read Block 2 R7 ------------------- ------------------- Total Blocks Read 5 Total Blocks Read 3 - Optimizer: I don't know if it checks whether your index is clustered or not. Catalin. -- =========================================================================== Catalin Badea _/_/_/ _/_/_/ _/_/_/ _/_/_/ _/ _/ _/_/_/ _/ _/ _/ _/ _/_/ _/ Senior Technical Architect _/ _/_/ _/ _/_/_/ _/ _/_/_/ _/ _/ _/ _/ _/ _/ Tecsys Inc. _/ _/_/_/ _/_/_/ _/_/_/ _/ _/_/_/ ====== cat@tecsys.com =========== include system "disclaimer.4gh" ======