At 03:13 PM 4/6/00 -0700, David Berg wrote:

>Clustered indexes are still useful when retrieving data sorted on the index
>(assuming the optimizer is smart enough to use the Index for the Order By).
>

Using an index to order a retrieval is ALWAYS a loss unless the metric
is time to first record. It is always faster, much faster, to fetch
the records in physical order then sort them than to rattle the disk
fetching records in index order. You really have to keep in mind that
disk speeds are measured in milliseconds and instruction times in
nanoseconds -- that a difference of six orders of magnitude. A
quick sort is cheap to set up and cheap to execute. A long merge
phase is more expensive, but if a merge is necessary and index walking
retrieval won't ever finish anyway.

The rule of thumb used to be that a good database design saturated
the disk channel while using 100% of the CPU. CPUs are now too fast
to saturation (assuming decent coding, of course). Now the goal
is to use all available memory.



Jim Starkey