Re: Is this really the best execution plan SQL2K can find?
From: Jacco Schalkwijk (jacco.please.reply_at_to.newsgroups.mvps.org.invalid)
Date: 09/07/04
- Next message: Hugo Kornelis: "Re: TRIGGER (AFTER INSERT) and Store Procedure ... insert failed !"
- Previous message: Roji. P. Thomas: "Re: Query Performance"
- In reply to: Laura T.: "Re: Is this really the best execution plan SQL2K can find?"
- Next in thread: Wayne Snyder: "Re: Is this really the best execution plan SQL2K can find?"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 7 Sep 2004 14:55:29 +0100
Laura,
The clustered index key consists of the columns Field1, Field2, Field3. The
leaf (lowest) level of the non-clustered index consists of the non-clustered
index itself (Field3) plus a pointer to the row in the table. If the table
does not have a clustered index this will be physical disk address
(file:page:rownumber), if the table has a clustered index this will be the
clustered index key. So the leaf level consists of the columns Field3,
Field1, Field2, Field3 and the column field2, that you want to select is
already in the non-clustered index. That is why an additional logical read
of the clustered index is not necessary.
--
Jacco Schalkwijk
SQL Server MVP
"Laura T." <laurat68@excite.dot.com> wrote in message
news:%23VfzVzNlEHA.2948@TK2MSFTNGP11.phx.gbl...
> Jacco,
>
> couldn't the NC index seek still incure one logical read more than using
> clustered?
> But still, I do not understand why the field list in the select statement
> changes
> the execution plan this much. I thought that select all the values that
> are
> stored in the clustered index would help and get them all directly from
> the
> cluster?
>
> thanks,
>
> Laura
>
> "Jacco Schalkwijk" <jacco.please.reply@to.newsgroups.mvps.org.invalid>
> wrote
> in message news:OkBMwYNlEHA.3428@TK2MSFTNGP11.phx.gbl...
>> Depending on the size of the columns in the table and the distribution of
>> data in the column, using the non-clustered index can be quicker than
> using
>> the clustered index. A non-clustered index uses the clustered index as a
>> pointer to the rows in the table, so at the leaf level of the
> non-clustered
>> index, the clustered index is already included. That means that you only
>> have to read the non-clustered index to get the results you want, if the
>> column you select is part of the clustered index.
>>
>> As the index on Field3 is unique, I can't imagine it takes more time than
>> using the clustered index, because there can only ever be one row for
> which
>> the condition Field3 = 3 is true. So only one row has to be read from the
>> non-clustered index, the same as it would from the clustered index.
>>
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>>
>> "Laura T." <laurat68@excite.dot.com> wrote in message
>> news:uAAh2BNlEHA.3544@TK2MSFTNGP15.phx.gbl...
>> > Can someone explain me the behavior of the case2 below?
>> > It defies my logic and my understanding
>> > what is a clustered index. The system uses SQL2kSp3a.
>> >
>> > It seems that whenever I have in the SELECT field list ANY field
>> > from the clustered index, the clustered index will not be considered
>> > for the execution plan, regardless of the where clause.
>> >
>> > Example:
>> >
>> > Table { Field1, Field2, Field3, Field4,Field5,Field6,Filed7... }
>> > Primary key clustered PK (Field1+Field2+Field3)
>> > Unique index I1( Field3)
>> >
>> > Case 1:
>> > select * from Table where Field1=1 and Field2=2 and Field3=3
>> >
>> > Uses the clustered PK. Optimal.
>> >
>> > *Case 2:*
>> > select Field2 from Table where Field1=1 and Field2=2 and Field3=3
>> >
>> > Does NOT use clustered PK but the unique index I1.
>> > This is suboptimal and takes much more time, when executed.
>> >
>> > Case 3:
>> > select Field5 from Table where Field1=1 and Field2=2 and Field3=3
>> >
>> > Uses clustered PK.
>> >
>> >
>> >
>> > TIA,
>> >
>> > Laura
>> >
>> >
>>
>>
>
>
- Next message: Hugo Kornelis: "Re: TRIGGER (AFTER INSERT) and Store Procedure ... insert failed !"
- Previous message: Roji. P. Thomas: "Re: Query Performance"
- In reply to: Laura T.: "Re: Is this really the best execution plan SQL2K can find?"
- Next in thread: Wayne Snyder: "Re: Is this really the best execution plan SQL2K can find?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
