dba_ind_columns - how to determine if column asc or desc

[ddiamond]

The system table dba_ind_columns doesn't specify whether an index column is ascending or descending. Is there any way to determine this using database queries?

 

[SantaMufasa]

DDiamond,

Since you posted this inquiry in the "Oracle 8i and earlier" forum, I presume you are looking for the "ASC"/"DESC" designation for one of those versions. I don't believe that such designator exists until later versions, but I can't test on a version 8i instance since it's been years since Oracle 8i was available to me. <grin>


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ddiamond]

Unfortunately, I'm still using oracle 8.0, and it does support asc / desc indexes.

 

[Turkbear]

Hi,
The docs indicate that Oracle sees an index with ASC or DESC as Function-based - as such, there is probably no dictionary entry specifying the order (The function itself handles this, not a dictionary entry )

ASC | DESC
Use ASC or DESC to indicate whether the index should be created in ascending or descending order. Indexes on character data are created in ascending or descending order of the character values in the database character set.

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. However, as with other function-based indexes, Oracle does not use descending indexes until you first analyze the index and the table on which the index is defined. See the column_expression clause of this statement.

Restriction: You cannot specify either of these clauses for a domain index. You cannot specify DESC for a reverse index. Oracle ignores DESC if index is bitmapped or if the COMPATIBLE initialization parameter is set to a value less than 8.1.0.

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ddiamond]

I think Dave may have been right about oracle 8.0 not supporting desc indexes. I found an except from my oracle 8.0 manual:

ASC / DESC are allowed for DB2 syntax compatibility, although indexes are always created in ascending order.

I was fulled into thinking they existed because oracle 8 will except the desc key word in the syntax, but it will create an ascending index even if you use the desc key word.

 

[Turkbear]

Hi,
Yes, based on this part of the docs:

Restriction: You cannot specify either of these clauses for a domain index. You cannot specify DESC for a reverse index. Oracle ignores DESC if index is bitmapped or if the COMPATIBLE initialization parameter is set to a value less than 8.1.0.

, it looks like it was an 8.1 addition.



To Paraphrase:"The Help you get is proportional to the Help you give.."