Re: Oracle 8.0: select statement with order by runs too long

From: Tom Best <tom.best_at_bentley.com>
Date: 2000/07/26
Message-ID: <8ln11c$4kb$1@news.bentley.com>#1/1

Right... a common misconception is that Oracle should use an index in order to satisfy an ORDER BY.

Frank <fbortel_at_hotmail.com> wrote in message news:397ED7E0.4C1859C9_at_hotmail.com...
> 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
>
> sort_area_size? db_file_multiblock_read_count?
>
> Why would you like to use the index? you are retrieving *ALL* records,
> Index scan followed by access by rowid is slower that a full table scan.
> Grtz, Frank
Received on Wed Jul 26 2000 - 00:00:00 CDT