Re: ORDER BY and performances

In our last gripping episode "Chris Boyle" <cboyle_at_no.spam.hargray.com> wrote:
> Something else to consider is how many indexes actually are on the
 table. I
> 'seem' to remember that having too many indexes actually would slow
 down a
> query.
> Alexandre Guillien <aguillien_at_tetraconcept.com> wrote in message
> news:39EC79D2.F7762323_at_tetraconcept.com...
> > Hello,
> >
> > I am asking myself questions whether I am stupid or Oracle has a
 very
> > weak point.
> >
> > I use Oracle 8.1.6. I have a table : Articles with several fields,
> > including ArticleCode and a unique index on ArticleCode. There are
 also
> > several other indexes for other purposes.
> > The table has currently 30.000 records.
> >
> > When I do : SELECT * FROM Articles ORDER BY ArticleCode, the query
 is
> > more than 60 seconds long.
> >
> > I did some Analyze Table and Analyze Index but it didn't change
> > anything. The Explain Plan always sends me a most stupid plan ...
 and
> > the query is still very slow ...
> >
> > From now on, the only solution I found was to force it to scan with
 the
> > best index (option /*+ INDEX_ASC (Articles IndexArticleCode) */).
 It is
> > exactly what I need, but it doesn't suits me (there are problems
 with
> > this option in Oracle 8.0.5 on queries with several joins).
> >
> > Could anybody here tell me if Oracle KNOWS how to use indexes in
 ORDER
> > BY operations ? And, if Yes, how to make it doing so ?
> >
> > Thanks,
> >
> > Alexandre Guillien
> >
>
>

The "problem", as I see it, is in the query itself:

SELECT * FROM Articles ORDER BY ArticleCode

This query is selecting every column of every record in the table -- 30,000 of them -- so no index is being used. It is NOT the ORDER BY that is causing the "problem".

The suggestion that the sort_area_size be examined is a good one since memory sorts are much faster than disk sorts. In this case the number of indexes on the table shouldn't influence the query speed. 30,000 records being sorted can take quite a bit of resource and if the sort_area_size is small much of the sorting activity will be disk-based.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.