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(event,'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_MULTIBLOCK_READ_COUNT.   I hope this help.

Faheem

tkumar_at_ipolicynet.com (Tuhin Kumar) wrote in message news:<e4ad76f5.0312130556.63570b37_at_posting.google.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
Received on Wed Dec 17 2003 - 05:24:21 CET