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

If I understand Oracle's optimizer, it chooses to use or not use a index to try to optimize access of data from the table to memory. Sorting is done after access and the selected data by then is either in memory or in the temporary tablespace if the query returned a large result set. Without a where clause, the full table scan is performed first - with records returned (most likely) in the physical order that they are stored. Once the data is in memory, then the index is no longer pertinent or usable (index contains only map between indexed column values and pointers to table storage(rowid)).

Without testing, I am guessing that Oracle will ignore an index hint without a where reference to a index column that can use the index. At least the hint would be self documenting - where a work around where clause would require a comment.

I am not how sure large tables are retrieved in total without sorting.

This is area that could be improved - threre is no reason the optimizer could not be made to consider the cost of sorting and effect of index use - if no index usable where clause predicates are present.

Sorry this is not what you wanted to hear. Maybe someone else has an idea.

Mark

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 Sun Apr 18 1999 - 09:37:45 CDT