Re: Sort by indexed field in ORDER BY. Why so slow? (Oracle8)

Is your database running with optimizer_mode=first_rows, or choose, all_rows, or rule ?

first_rows would probably do the trick for you.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Christopher Latta wrote in message
<7ejtnq$8b$1_at_reader1.reader.news.ozemail.net>...
>In Delphi 4 C/S, I have a simple form with a TQuery, a TDataSource, and a
>TDbGrid. The SQL reads like:
>
>Select * from MyTable order by MyIndexedField
>
>However, when the form comes up, the hard disk on the server goes wild, and
>it takes around 20 seconds to return a result set to the DBGrid. This is on
>a table of around 10,000 rows. It appears to be resorting the table before
>sending data back. This time delay is unacceptable..
>
>In my Oracle book, it states that indices are used in WHERE clauses, and
not
>in ORDER BY which seems utterly bizarre to me. Why else would you use an
>ORDER BY on an indexed field if not to return a result set quickly without
>having to resort an already sorted table?. Anyway, if I use the following
>SQL:
>
>Select * from MyTable where MyIndexedField > ' ' order by
>MyIndexedField
>
>the result set comes back instaneously. Can I get Oracle to use my index in
>an ORDER BY without having to use a dummy where clause?
>
>I don't really want to have to use an index hint for this, I just want
>Oracle to return the results quickly from my index.
>
>Thanks,
>Christopher Latta
>
>
>
>
>
>
Received on Fri Apr 09 1999 - 01:39:13 CDT