Re: Performance when using ORDER BY

Hi Steve,

Yes.

Create indexes on the columns you are specifying in your order by clause. You can either create two single column indexes or a composite index naming both columns. If you choose to create a composite index make sure that the first column in the create index statement is the one you name first in the select statement. (Oracle will only use an index if the first column in the index is in a WHERE clause in the query.) If there are no other indexes on the table you will be able to omit the Order By clause since Oracle will retrieve the data in index order. By the way after you create the index run an analyze command on the table and its indexes.

Regards

Jerry

Steve Rafferty wrote:
>
> Hello all --
>
> I'm new to Oracle (and to databases in general). I've created a
> web based application which searches a database of 90,000 records
> and displays the results. A basic query executes very quickly --
> records begin displaying in approx 3 seconds; however, I want
> to sort the output on two columns. When I add
>
> ORDER BY COLUMN1 ASC, COLUMN2 DESC
>
> the first results of a query appear 30 - 60 seconds after
> the query is submitted.
>
> I understand that without the ORDER BY clause the results of
> the query are being displayed while the query is still being
> executed. My question is: is there a way to achieve the
> output sort I want without the devastating performance loss?
>
> Thanks,
>
> Steve
 

-- 
Jerry Gitomer         	ICT Group
jgitomer_at_ictgroup.com	Langhorne PA
jgitomer_at_p3.net 	Opinions are mine not those of ICT Group