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, and it knows it can stop after 250, so assuming the matching rows are evenly distributed it thinks it can stop after having read 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 read order was specified by the index rather than in sequential order, and it also had to read the index. These extra costs were 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 limit wasn'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 attachments is 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.
__________________________________________________________________