|
|
Posted by Gert-Jan Strik on 10/02/57 11:45
Alexander Kuznetsov wrote:
>
> Erland Sommarskog wrote:
> > That explains why I haven't heard of it. SQL Server performs no such
> > deliberations, as far as I know.
>
> Well if on the leaf level of a non clustered index you've got an RID, a
> structure looking like (extent#, page#, row_on_page#), and you know
> that matching rows are likely to be stored on adjacent pages, you can
> traverse a range of the index, sort RIDs of matching rows, and read
> every page with matching rows only once. This is why clustering factor
> is a very useful piece of statistics for DB2/Oracle.
>
> If on the leaf level of a non clustered index you've got a bookmark,
> then you just know absolutely nothing about the physical location of
> the rows you need to retrieve. I don't see any way SQL Server could
> utilize clustering factor as long as bookmarks are used to access rows
> from non-clustered indexes (unless it's a heap table, which is not the
> best practice). Please correct me if I'm wrong.
The bookmark does give (some) information about the physical location!
In some situations, sorting on the clustered index key could be of use.
But more information would be need to be used. The rows are physically
stored in the order of the clustered index key. So statistics of the
clustered index can determine the likelyhood that two consecutive
clustered keys (found in the nonclustered index) can be found on the
same page. This approach would guarantee a maximum of one read per page.
Without such a mechanism each bookmark lookup could lead to a physical
I/O, even if the average numbers of rows per page is much higher. This
could occur if there is not enough memory to hold the data pages in
cache, and the bookmark lookups are done out of (clustered index) order.
But even then, it is just an estimate. The index statistics do not give
information about the density of individual pages, and two consecutive
rows could still be on two different pages. So there is a large margin
of error which makes the tradeoff between 'random' bookmark lookups,
sorted bookmark lookups and clustered index scan (with the advantage of
sequential I/O over random I/O) difficult.
Gert-Jan
Navigation:
[Reply to this message]
|