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 Forums!
  • 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.

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

Share

dba_ind_columns - how to determine if column asc or desc

dba_ind_columns - how to determine if column asc or desc

(OP)
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?
  

RE: dba_ind_columns - how to determine if column asc or desc

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."

RE: dba_ind_columns - how to determine if column asc or desc

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

RE: dba_ind_columns - how to determine if column asc or desc

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.."

RE: dba_ind_columns - how to determine if column asc or desc

(OP)
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.

RE: dba_ind_columns - how to determine if column asc or desc

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!

Resources

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