Re: Why the difference in plans ?

On 6-Mar-08, at 5:10 PM, Stephen Denne wrote:

> 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.
>
The strange thing of course is that the data is exactly the same for
both runs, the tables have not been changed between runs, and I did
them right after another. Even more strange is that the seq scan is
faster than the index scan.

Dave
> 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.
> __________________________________________________________________
>
>