Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Index by Date Descending: Ignored
Michael,
Thanks for the feedback. We actually use the default SORT_AREA_SIZE, though we have a large (40M) shared_pool. This may cause the CBO to try to sort it on the fly.
> Look for that field in ALL_TAB_COLUMNS - one of it's LONG columns
> containd the original field name and DESC keyword.
You probably mean ALL_IND_COLUMNS. For the record, ALL_IND_EXPRESSIONS lists the actual function or "column expression".
Even when I use a hint, Oracle still ignores the index.
SELECT * FROM USERS /*+ index_desc(user_create_date_i) */ ORDER BY record_create_date DESC
And it's not like this SQL statement executes very quickly. It still takes 4-5 seconds to execute.
Using a rownum constraint helps speed up the query: SELECT * FROM
(SELECT * FROM USERS /*+ index_desc(user_create_date_i) */ ORDER BY record_create_date DESC)
hmmmmm....This seems very basic.
bill milbratz
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Fri Oct 13 2000 - 12:47:30 CDT