|
|
Posted by Erland Sommarskog on 06/29/05 00:48
paul (nomail@nomail.com) writes:
> Hi, i have a table like this
> CREATE TABLE dbo.test
> (
> num int NOT NULL,
> ename char(80),
> eadress char(200),
> archived char(1)
> PRIMARY KEY CLUSTERED (num)
> )
>
> create index i_archived on dbo.test(archived)
>
> the are 500000 rows in this table, and the archived field contain 15000
> 'Y' and 485000 'N'
>
>
> When i issue a select * from test where archived='Y',
> the path choosed is the index scan clustered and not the index i_archived
>
> the stats are updated every day.
>
> did i miss something ?
The fine print that a non-clustered index is not always useful.
The row size is 286 bytes (columns + null bit mask). That makes for 28
28 rows per page when full. For 500000 rows that's 17854 pages to
scan. For the non-clustered index, there's 15000 bookmark lookups + the
index to scan, but that is not likely be more than some 100 pages.
Really why the optimizer goes for the clustered index, I don't know,
but such things like read-ahead reads being possible may be part of
it.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|