At 06:24 PM 2/13/00 +0200, Hannu Krosing wrote:
>Chris wrote:
>>
>> Tom Lane wrote:
>> >
>> > SELECT * FROM table WHERE x > 100 ORDER BY x LIMIT 1;
>>
>> Could it _ever_ be faster to sort the tuples when there is already an
>> index that can provide them in sorted order?
>This has been discussed on this list several times, and it appears that
>select+sort is quite often faster than index scan, mainly due to the fact
>that tables live on disk and disk accesses are expensive, and when doing
>index scans:
>
>1- you have to scan two files (index and data), when they are on the same
> disk it is much more 2 times slower than sacnning a single file even
> when doing it sequentially
>
>2- scans on the both files are random access, so seek and latency times
> come into play and readahead is useless
>
>3- you often read the same data page many times
Hmmm...yet any studly Oracle type knows that despite whatever veracity
this analysis has, in reality Oracle will utilize the index in the
manner suggested by Chris and the difference in execution time is,
well, astonishing. Even without the limit.
We just had a discussion regarding this a few days ago over on
Philip Greenspun's web/db forum, where someone ran into a situation
where Oracle didn't recognize that the index could be used (involving
function calls, where presently Oracle doesn't dig into the parameter
list and to look to see if the referenced columns are indexed when
doing its optimization). After tricking Oracle into using the index
by adding an additional column reference, he got a speedup of well
over an order of magnitude.
Again, with no limit clause (which Oracle doesn't implement anyway).
- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.