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

[FaheemRao]

Oracle treats desc indexes as function bases indexes .

First of all try to create a normal index (without desc) and see how
oracle responds. I ecatly dont know why oracle is throwing that error
.. Check that table and index are in same schema .
Following is an alternae solution.
I guess you are trying to use desc indexes for query using

order by column_name desc

Optimizer uses function bases index only if you use exact function in
query.

you can create primary key with normal index and then create a desc
index and then set parameter query_rewrite_enabled = true in session
or system level and see explain plan to see if desc index is being
used or not.
There are good chances if you use order by column_name desc that
optimizer going to use desc index.

Faheem
Faheem

tk****@ipolicynet.com (Tuhin Kumar) wrote in message news:<e4**************************@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

[FaheemRao]

Oracle treats desc indexes as function bases indexes .

First of all try to create a normal index (without desc) and see how
oracle responds. I ecatly dont know why oracle is throwing that error
.. Check that table and index are in same schema .
Following is an alternae solution.
I guess you are trying to use desc indexes for query using

order by column_name desc

Optimizer uses function bases index only if you use exact function in
query.

you can create primary key with normal index and then create a desc
index and then set parameter query_rewrite_enabled = true in session
or system level and see explain plan to see if desc index is being
used or not.
There are good chances if you use order by column_name desc that
optimizer going to use desc index.

Faheem
Faheem

tk****@ipolicynet.com (Tuhin Kumar) wrote in message news:<e4**************************@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

[Tuhin Kumar]

Hi Farheem,

Thanks for the response. I tried with
ALTER SESSION set QUERY_REWRITE_ENABLED=TRUE;
but the explaun plan still picked up the primary key index. If I drop the
primary key constraint from the table, the it do full table scam but doesn't use
the index t1_pk.

Thanks,
Tuhin

fa*******@yahoo.com (FaheemRao) wrote in message news:<43**************************@posting.google. com>...

Oracle treats desc indexes as function bases indexes .

First of all try to create a normal index (without desc) and see how
oracle responds. I ecatly dont know why oracle is throwing that error
. Check that table and index are in same schema .
Following is an alternae solution.
I guess you are trying to use desc indexes for query using

order by column_name desc

Optimizer uses function bases index only if you use exact function in
query.

you can create primary key with normal index and then create a desc
index and then set parameter query_rewrite_enabled = true in session
or system level and see explain plan to see if desc index is being
used or not.
There are good chances if you use order by column_name desc that
optimizer going to use desc index.

Faheem
Faheem

[Tuhin Kumar]

Hi Farheem,

Thanks for the response. I tried with
ALTER SESSION set QUERY_REWRITE_ENABLED=TRUE;
but the explaun plan still picked up the primary key index. If I drop the
primary key constraint from the table, the it do full table scam but doesn't use
the index t1_pk.

Thanks,
Tuhin

fa*******@yahoo.com (FaheemRao) wrote in message news:<43**************************@posting.google. com>...

Oracle treats desc indexes as function bases indexes .

First of all try to create a normal index (without desc) and see how
oracle responds. I ecatly dont know why oracle is throwing that error
. Check that table and index are in same schema .
Following is an alternae solution.
I guess you are trying to use desc indexes for query using

order by column_name desc

Optimizer uses function bases index only if you use exact function in
query.

you can create primary key with normal index and then create a desc
index and then set parameter query_rewrite_enabled = true in session
or system level and see explain plan to see if desc index is being
used or not.
There are good chances if you use order by column_name desc that
optimizer going to use desc index.

Faheem
Faheem

[Tuhin Kumar]

Hi Faheem,

Thanks for the response.
I tried using ALTER SESSION set QUERY_REWRITE_ENABLED=TRUE; but still
the primary key index was picked up. When I dropped the primary key constraint
from the table, the explain plan showed doing the full table scan.

Thanks,
Tuhin

fa*******@yahoo.com (FaheemRao) wrote in message news:<43**************************@posting.google. com>...

Oracle treats desc indexes as function bases indexes .

First of all try to create a normal index (without desc) and see how
oracle responds. I ecatly dont know why oracle is throwing that error
. Check that table and index are in same schema .
Following is an alternae solution.
I guess you are trying to use desc indexes for query using

order by column_name desc

Optimizer uses function bases index only if you use exact function in
query.

you can create primary key with normal index and then create a desc
index and then set parameter query_rewrite_enabled = true in session
or system level and see explain plan to see if desc index is being
used or not.
There are good chances if you use order by column_name desc that
optimizer going to use desc index.

Faheem
Faheem

tk****@ipolicynet.com (Tuhin Kumar) wrote in message news:<e4**************************@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

[Tuhin Kumar]

Hi Faheem,

Thanks for the response.
I tried using ALTER SESSION set QUERY_REWRITE_ENABLED=TRUE; but still
the primary key index was picked up. When I dropped the primary key constraint
from the table, the explain plan showed doing the full table scan.

Thanks,
Tuhin

fa*******@yahoo.com (FaheemRao) wrote in message news:<43**************************@posting.google. com>...

Oracle treats desc indexes as function bases indexes .

First of all try to create a normal index (without desc) and see how
oracle responds. I ecatly dont know why oracle is throwing that error
. Check that table and index are in same schema .
Following is an alternae solution.
I guess you are trying to use desc indexes for query using

order by column_name desc

Optimizer uses function bases index only if you use exact function in
query.

you can create primary key with normal index and then create a desc
index and then set parameter query_rewrite_enabled = true in session
or system level and see explain plan to see if desc index is being
used or not.
There are good chances if you use order by column_name desc that
optimizer going to use desc index.

Faheem
Faheem

tk****@ipolicynet.com (Tuhin Kumar) wrote in message news:<e4**************************@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

[Daniel Roy]

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_enabled 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

[Daniel Roy]

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_enabled 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]

Daniel,

Thats right query_rewrite_enabled= true is also concerned with
matrialized views , but it also affect optimizer to choose function
based index becuse when you create function based index orale
pre-calclate the value of that function and store in index. Now when
you use exactly same function in SQL optimizer uses that index, but it
has to be that excat same function.

Tuhin my guess is that you are using desc index becasuse you need to
get your sql results in desc order(let me know If I am wrong), for
that you have put

"order by column_name desc" in sql to make optimizer take advantage of
function-based index.

Now sometimes even after setting query_rewrite_enabled= true optimizer
does not use functiobased index.
There is a solution to that there is an other parameter the name of
which is not on top of my head. the default value of that paramet is
100 which means that optimizer think if it going to use function based
index coast going to be 100 which is not the case in real. you have to
set the value of that parameter less than 100.
I will do a search for you to find excat name of that parameter.


Faheem

da*************@hotmail.com (Daniel Roy) wrote in message news:<37************************@posting.google.co m>...

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_enabled 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]

Daniel,

Thats right query_rewrite_enabled= true is also concerned with
matrialized views , but it also affect optimizer to choose function
based index becuse when you create function based index orale
pre-calclate the value of that function and store in index. Now when
you use exactly same function in SQL optimizer uses that index, but it
has to be that excat same function.

Tuhin my guess is that you are using desc index becasuse you need to
get your sql results in desc order(let me know If I am wrong), for
that you have put

"order by column_name desc" in sql to make optimizer take advantage of
function-based index.

Now sometimes even after setting query_rewrite_enabled= true optimizer
does not use functiobased index.
There is a solution to that there is an other parameter the name of
which is not on top of my head. the default value of that paramet is
100 which means that optimizer think if it going to use function based
index coast going to be 100 which is not the case in real. you have to
set the value of that parameter less than 100.
I will do a search for you to find excat name of that parameter.


Faheem

da*************@hotmail.com (Daniel Roy) wrote in message news:<37************************@posting.google.co m>...

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_enabled 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]

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

tk****@ipolicynet.com (Tuhin Kumar) wrote in message news:<e4**************************@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

[FaheemRao]

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

tk****@ipolicynet.com (Tuhin Kumar) wrote in message news:<e4**************************@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