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

Sybrand Bakker wrote:
>
> 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

Isn't part of the issue 'perception'? When you do a simple select (depending on your environment), you usually get your results relatively (compared to the order by) quickly, but only because you are seeing the 'first few rows' from the query; it hasn't necessarily completed the retrieval of the data. But, when you do an 'order by', the query (and retrieval) of the data must go to completion in order to sort it as the final step.

Yours,

Geoff Houck
systems hk
hksys_at_teleport.com
http://www.teleport.com/~hksys Received on Thu Aug 31 2000 - 00:14:27 CDT