Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

dba_ind_columns - how to determine if column asc or desc

ddiamond (Programmer)
29 Jul 09 11:02
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 (TechnicalUser)
29 Jul 09 11:48
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>
 

santaMufasa
(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 (Programmer)
29 Jul 09 13:16
Unfortunately, I'm still using oracle 8.0, and it does support asc / desc indexes.   
Turkbear (TechnicalUser)
29 Jul 09 13:46
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 )

Quote (tahiti/tahiti.tabbed?section=73943):


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.
 

profile

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

ddiamond (Programmer)
29 Jul 09 13:57
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:

Quote:

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 (TechnicalUser)
29 Jul 09 15:21
Hi,
Yes, based on this part of the docs:

Quote:


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.

profile

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

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close