RE: Indexing SYS tables

I'm not so sure. I created a DESC index, but the optimizer refused to use it in a simple query containing an ORDER BY DESC on the indexed DATE column without hinting, even after I analyzed it (8.1.7.4 and CBO).

Without spending too much time on it, I created a good ol' fashioned b-tree and it works like a charm. I just need to address the issue of "Should I?"

Rich

Rich Jesse                           System/Database Administrator
Rich.Jesse_at_qtiworld.com              Quad/Tech International, Sussex, WI USA

> -----Original Message-----
> From: Connor McDonald [mailto:hamcdc_at_yahoo.co.uk]
> Sent: Thursday, October 03, 2002 4:29 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Indexing SYS tables
>
>
> Depends on the version I think. Oracle has always
> allowed DESC indexes, but only in 9(?) is the keyword
> actually used in the index build.
>
> hth
> connor
>
> --- Chaim.Katz_at_Completions.Bombardier.com wrote: >
> > but there is a "create index ... desc"?
> >
> > ASC | DESC
> > specifies whether the index should be created in
> > ascending or descending
> > order.
> > Oracle treats descending indexes as if they were
> > function-based indexes.
> > You do not need the QUERY REWRITE or GLOBAL QUERY
> > REWRITE privileges to
> > create them, as you do with other function-based
> > indexes.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).