Re: Oracle 8.0: select * with order by lasts too long

That's not right. Whether Oracle uses an index is based on what you put in the where clause (sometimes in addition to what you say in the select list, e.g. in IOT). Bernd's query runs slow purely because of the sort. I believe a lot of sort is done on disk. Increasing SORT_AREA_SIZE should help.

Since he doesn't have a where clause, he's doing a full table scan either way.

Yong

holders <holders_at_demon.co.uk> wrote in message news:397EAB35.5220A3CF_at_demon.co.uk...
> because you're doing a 'SELECT *' it will read the whole table and not
> use the index. Without the ORDER BY it will not need to do a sort, it
> simply reads to sends it.
>
> SH
>
> Bernd Rosenau wrote:
>
> > Hello,
> >
> > the following statement needs 20 seconds for execution when run on a
> > 65000 rows table:
> >
> > select * from folders order by selekt_nr1
> >
> > Selekt_nr1 is an index-field but Oracle does not seem to use the
> > index.
> >
> > Without the ORDER BY i get an almost instant response.
> >
> > Any ideas ?
> >
> > Many thanks in advance for your help,
> >
> > Regards, Bernd
>
Received on Wed Jul 26 2000 - 00:00:00 CDT