DESC in primary key

Does anyone know of a way to have a DESC function on a column in a primary key? Below is what I've tried and the results:


create table tab1 (col1 number, col2 number, col3 number);

alter table tab1 add constraint pk1 primary key (col1) using index
(create index indx1 on tab1 (col1 desc));

alter table tab1 add constraint pk1 primary key (col1) using index
*
ERROR at line 1:
ORA-01418: specified index does not exist


I've also tried:

create unique index indx1 on tab1 (col1 desc);
alter table tab1 add constraint pk1 primary key (col1) using index indx1;

with the same results.

Any ideas?

Thanks,
J

SQL> create table tab1
2 (col1 number,
3 col2 number,
4 col3 number);

Table created.

SQL> create index indx1 on tab1 (col1 desc);

Index created.

Thanks. I knew I could do it that way, but I was specifically trying to get a "Primary Key". Technically, there's no difference, but some of our scripts are looking for the primary key constraints (where constraint_type = 'P'), and we were trying to keep from rewriting our scripts.


J

You do not alter the database to conform to your code. The data will be there long after your app has come and gone. This is a perfect example of why most DBA's want to shoot developers!

MH

I would normally agree, but in this case, I'm the DBA and developer. It's not a big deal, but if it was possible I would rather keep the database consistent, and there is only one table out of 300 that I'll have without a true PK.

Well, you should still find a developer to shoot!
Just on priciple.

:D

MH

Works for me! But can it be more than one... ;)

Quote:

---

Originally posted by Mr.Hanky
Well, you should still find a developer to shoot!
Just on priciple.

:D

MH

---

Oh no, I'm condemmed to suicide.

i just want to know
what is advantage of building the index as descending?

-Raja

Cheer up DaPi, at least you can say it is on your terms:D

Has Mr. Hanky always been this way, what with wanting to shoot developers at will...?:D

Rick

Quote:

---

Originally posted by Alchemy
Has Mr. Hanky always been this way, what with wanting to shoot developers at will...?

---

As long as I've known him.

As far as I'm concerned, the difference is that when I joined this forum I had some dev. staff to shoot. Now there's just me: Senior Project Manager and Junior Programmer rolled into one. At least if I'm doing the shooting, I'll know when to duck ;)

Quote:

---

Originally posted by Alchemy

Has Mr. Hanky always been this way, what with wanting to shoot developers at will...?:D

Rick

---

Actually years of therapy have improved my disposition, I used to be angry.

MH

Quote:

---

Originally posted by rajabalachandra
i just want to know
what is advantage of building the index as descending?

-Raja

---

Yes, i was wondering that same thing. sounds like you'd be just asking to get bitten by a bug. Also, there's implications in loading the table with an ascending sequence-generated PK -- wouldn't the index splits be suboptimal?

Enable true descending order indexes

They are treated as a special case of function-based indexes.


--------------------------------------------------------------------------------
Note:
Oracle sorts columns with the DESC keyword in descending order. Such indexes are treated as function-based indexes. Descending indexes cannot be bitmapped or reverse, and cannot be used in bitmapped optimizations. To get the pre-Oracle 8.1 release DESC behavior, remove the DESC keyword from the CREATE INDEX statement.

--------------------------------------------------------------------------------

http://download-west.oracle.com/docs...ndexes.htm#430

MH

Quote:

---

Originally posted by Mr.Hanky
Enable true descending order indexes

They are treated as a special case of function-based indexes.


--------------------------------------------------------------------------------
Note:
Oracle sorts columns with the DESC keyword in descending order. Such indexes are treated as function-based indexes. Descending indexes cannot be bitmapped or reverse, and cannot be used in bitmapped optimizations. To get the pre-Oracle 8.1 release DESC behavior, remove the DESC keyword from the CREATE INDEX statement.

--------------------------------------------------------------------------------

http://download-west.oracle.com/docs...ndexes.htm#430

MH

---

Ypu, that's a descendingindexalright.

But what would be the advantage for JDF in using one of these over a regular index?

Well, at the very least, you would be different.
We can call it the non-conformists index if you like.

MH