How to create a descending index on the primary key.

[Tuhin Kumar]

Hi,
Oracle give the error ORA-01418 when I try to do the
following;

Create unique index t1_pk on TABLE1(EntryId DESC) ;

If the I try to add primary key Contraint using the above index t1_pk
as below:
ALTER TABLE TABLE1 ADD CONSTRAINT TABLE1_PK PRIMARY KEY (EntryId)
USING INDEX t1_pk;

the following error comes "ORA-01418: specified index does not exist"
but the index t1_pk exists.

My requirement is to create a descending index on EntryId which gets
applied for all the queries.

Thanks
Tuhin

[Daniel Roy]

Re: How to create a descending index on the primary key.

Guys,
I was able on my 9R2 version to reproduce this ORA-01418 error. It
looks like Oracle doesn't "see" the index when creating the PK
constraint, if this index was created with the DESC option. I think
that a more meaningful error message in that scenario would be that
Oracle can't use a function-based index to enforce a PK constraint.
Note also that as far as I know, the parameter query-rewrite_enabled
applies to MV's, which are not in the picture in this case. The CBO
should use the DESC index if appropriate, no matter what the
query_rewrite_e nabled parameter is set at (sorry, I don't have any
Oracle to test that at the moment). Could the OP please post the
query, create a DESC index, and then post the execution plan, and we
could start from there in trying to tune it.

Daniel

[FaheemRao]

Re: How to create a descending index on the primary key.

Tuhin,

Here is the init parameter I was talking about
OPTIMIZER_INDEX _COST_ADJ
default value is 100 .

optimal value is different for every database.

you can find out the value by query this


select 100*(sum(decode (event,'db file sequential read',
average_wait,0) )/
sum(decode(even t,'db file scattered read', average_wait,0) )) as
optimizer_index _cost_adj
from V$SYSTEM_EVENT
where event like 'db file s%';

VALUE is based on average wait time to perform an I/O , do not
measure its value immediately after db startup give it some time.
I used that in dataware house so I can tell you what mine valus was
mine was 1.
If it did not work with your calculated value put "1" for last resort.
If this parameter does not work then you might wanna consider playing
with parameter
DB_FILE_MULTIBL OCK_READ_COUNT.



I hope this help.


Faheem





tkumar@ipolicyn et.com (Tuhin Kumar) wrote in message news:<e4ad76f5. 0312130556.6357 0b37@posting.go ogle.com>...

Hi,
Oracle give the error ORA-01418 when I try to do the
following;
>
Create unique index t1_pk on TABLE1(EntryId DESC) ;
>
If the I try to add primary key Contraint using the above index t1_pk
as below:
ALTER TABLE TABLE1 ADD CONSTRAINT TABLE1_PK PRIMARY KEY (EntryId)
USING INDEX t1_pk;
>
the following error comes "ORA-01418: specified index does not exist"
but the index t1_pk exists.
>
My requirement is to create a descending index on EntryId which gets
applied for all the queries.
>
Thanks
Tuhin