Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Sort by indexed field in ORDER BY. Why so slow? (Oracle8)
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 Thu Apr 08 1999 - 22:54:44 CDT