Re: ORDER BY and impact on query performance

Jeremy <newspostings_at_hazelweb.co.uk> wrote:
> Oracle 8i & 9i on Solaris 2.6 and 9 respectively. Sun SPARC hardware.
>

...
>
> Now this query will run in approx 1 second - this is good (as it was
> well over a minute before). However, if I add ORDER BY (anything) as the
> last line, the query time goes up to 9 seconds.

By any chance, are you timing the query by how long it takes to return the first row (rather than all rows)? If so, keep in mind that the "order by" query can't return the first row until it has located the last row, whereas the non-"order by" query can return the first row almost as soon as it locates it.

> The result set was 257 rows. I am not sure when the sorting is done but
> an 8 second overhead for the sort of 257 rows is clearly not too good!
>
> I guess there must be a trick to make the ORDER BY not make such an
> impact - any clues?
>
> I have also pasted the explain plans below should they relevant (which I
> guess they are) - the fist is without the ORDER BY, second is ORDER BY
> 1.

To my quick glance, they look identical except for the sort operation.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB