Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Index by Date Descending: Ignored

Re: Index by Date Descending: Ignored

From: <billmil_at_my-deja.com>
Date: Fri, 13 Oct 2000 17:47:30 GMT
Message-ID: <8s7hrg$vhl$1@nnrp1.deja.com>

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)

  WHERE ROWNUM <= 3

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US