Re: Speed up "order by"

In article <45fq16$2vi_at_data.interserv.net>, Will Kooiman says...
>> rz86988_at_mcvax2.d48.lilly.com writes:
>> Does anybody know how to speed up the "order by" clause?
>>
>> The select statement retrieves about 50,000 records and it runs OK,
>> however
>> when I add the "order by", it runs over 10 minutes.
>> What I can do to improve this other than changing the Sort_area_size?
>>
>> Di Wang
>> diw_at_lilly.com
>
>Without knowing all the details of your table/query/indexes, it's hard to
> say.
>If Oracle has to sort 50,000 rows, it will be slow. But... if you create an
>index on the sorted columns, the Oracle optimizer "should" recognize this,
>and it "should" use the index to retrieve the items in sorted order. This
>is, of course, assuming a lot.
>Will.

FWIW, I've a PRO*C program which fetches every record from a 100,000+ row table into memory (about 4 Mbytes), to use for reference. There are no joinsIt uses an array fetch to get 50 rows at a time. Fetching the rows IN ORDER from the database took approximately 200 seconds. Fetching the rows at random from the database, and then sorting them in memory (qsort) took aprox. 100 seconds.

But then, I'm using 6 columns for the sort, and three of them can be null...

You pays your money, and takes your chances I guess.

-- 
 _________________________   __________________________________________
/  Tommy Wareing          \ /  I've been looking for an original sin,  \
|  p0070621_at_brookes.ac.uk  X   One with a twist and a bit of a spin    |
\  0865-483389            / \     -- Pandora's Box, Jim Steinman       /
 ~~~~~~~~~~~~~~~~~~~~~~~~~   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~