Re: Why a Select with Order By takes such a long time to complete on a complex view?

On Wed, 30 Aug 2000 06:42:37 -0400, "Kevin Bass" <akil1_at_mindspring.com> wrote:

>When using the ORDER BY clause, the database performs a sort on the data
>that you are retrieving and also performs a full table scan.
>
>Kevin
>
>
><dorutanasa_at_my-deja.com> wrote in message
>news:8oggq0$9eo$1_at_nnrp1.deja.com...
>> Hi everybody,
>>
>> First of all, I just a beginner so please excuse any mistake that I
>> would
>> probably make.
>>
>> I needed to create in my database a view that use aprox. 12 tables. My
>> view
>> has over 30 columns, but using indexes in the base tables a select
>> doesn't
>> take so long (max 2-3 sec).
>> Now, I want to use the ORDER BY clause in the SELECT statement, but
>> can't do
>> it because it takes too loooong time to be executed.
>> Any idea why this happend... would be sincerely appreciated.
>>
>> Thanks,
>> Doru.
>>
>>
>>
>> Sent via Deja.com http://www.deja.com/
>> Before you buy.
>

the database performs a sort on the data
>that you are retrieving and also performs a full table scan.

Sorry to say so but this is simply *NOT TRUE*. an order by is a separate operation, usually the last stage of the process which operates on the resultset only. If there are no appropiate where clauses in the select this would result in a full table scan, but this has nothing at all to do with the order by. He should check his sort_area_size parameter, or issue alter session set sort_area_size=<at least 262144> and verify the settings for his temporary tablespace.

Regards,

Sybrand Bakker, Oracle DBA Received on Wed Aug 30 2000 - 06:28:14 CDT