ORDER BY and performances

From: Alexandre Guillien <aguillien_at_tetraconcept.com>
Date: Tue, 17 Oct 2000 18:09:54 +0200
Message-ID: <39EC79D2.F7762323@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 Received on Tue Oct 17 2000 - 11:09:54 CDT