Posted by Serge Rielau on 04/17/06 01:33

Alexander Kuznetsov wrote:
> Serge Rielau wrote:
>> 1. A thumb rule for using TBSCAN over (index scan & page fetch) is when
>> the selectivity is worse than, say 5%. Sure any DBSM will pick it's own
>> secret number,
>
> Serge,
>
> I think that threshold selectivity is not a constant - for SQL Server
> it also depends on the width of bookmarks. So, if bookmarks are 4 byte
> integers, the threshold selectivity is lower than if bookmarks are
> 50-byte character fields.
Struggling to research bookmarks I ran into:
http://msdn2.microsoft.com/en-US/library/ms180920(SQL.90).aspx
"Bookmark Lookup is not used in SQL Server 2005. Instead, Clustered
Index Seek and RID Lookup provide bookmark lookup functionality."
Cool, let's bypass that one.

> Similarly, besides selectivity DB2 also considers clustering factor,
> and if an index has a high clustering factor, even low 50% selectivity
> might be good enough for an access via an index, correct?
Certainly clustering will affect the decision. But the point of my note
was not to get into the gory details (see "thumb rule") as to state that
a covering index scan is certainly preferable in the vast majority of
cases over a "index seek and RID lookup" (in SQL Server words, aka
ISCAN/FETCH in DB2 words) and that "index seek and RID lookup" compete
with table scans.
So whenever a query for more columns that it consumes it's at risk of
getting suboptimal plans.

Back to Peter, if you select all rows and all columns in your view, then
what good is it? Are you blindly applying some lesson in some book?
views are used for access control, as shorthands for complex queries and
to hide details of the tables. I don't see how you do any of that...
The simplicity of your views also prevents you from seeing my point that
the optimizer (any optimizer) is fallible and you don't want to find out
about the unused column that wasn't dropped when it's too late.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Back to original message]