question on index used by optimiser

[paul]

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(archiv ed)

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 ?


thx

[Gert-Jan Strik]

Re: question on index used by optimiser

paul wrote:[color=blue]
>
> 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(archiv ed)
>
> 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 ?
>
> thx[/color]

You probably did not miss anything. Does the tables have less than 15000
pages? In that case, SQL-Server probably thinks it is cheaper to scan
the table than it is to lookup 15000 rows based on the index. Which plan
is fastest will in practice depend on the amount of cached data. With a
cold (empty) cache, scanning the table will probably be faster. If you
know you have a hot cache for this query, then you could consider adding
an index hint.

HTH,
Gert-Jan

[Erland Sommarskog]

Re: question on index used by optimiser

paul (nomail@nomail. com) writes:[color=blue]
> 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(archiv ed)
>
> 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 ?[/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp