Re: Why the difference in plans ?

Dave Cramer wrote:
> I have two almost identical queries.  Strangely enough the one
> that uses the index is slower ???

The index scan is being used so that it can retrieve the rows in the name order.
It expects that if it was to retrieve every row via the index, it would get about 1010 rows that matched the filter,
andit knows it can stop after 250, so assuming the matching rows are evenly distributed it thinks it can stop after
havingread only a quarter of the rows. 

However only 129 rows matched. Consequently it had to read every row in the table anyway, seeking a fair bit as the
readorder was specified by the index rather than in sequential order, and it also had to read the index. These extra
costswere much larger than reading the lot sequentially, and sorting 129 resulting rows. 

The first query picked a sequential scan as it thought it was only going to get 11 results, so was expecting that the
limitwasn't going to come into play, and that every row would have to be read anyway. 

Regards,
Stephen Denne.

Disclaimer:
At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any
attachmentsis confidential and may be subject to legal privilege.  If it is not intended for you please advise by reply
immediately,destroy it and do not copy, disclose or use it in any way. 

__________________________________________________________________
  This email has been scanned by the DMZGlobal Business Quality
              Electronic Messaging Suite.
Please see http://www.dmzglobal.com/services/bqem.htm for details.
__________________________________________________________________