如何在主键上创建降序索引。 [英] How to create a descending index on the primary key.

问题描述

当我尝试执行

后，Oracle会给出错误ORA-01418;


创建唯一TABLE1上的索引t1_pk（EntryId DESC）;


如果我尝试使用上面的索引t1_pk

添加主键Contraint，如下所示：

ALTER TABLE TABLE1 ADD CONSTRAINT TABLE1_PK PRIMARY KEY（EntryId）

使用INDEX t1_pk;


出现以下错误：ORA-01418：指定索引不存在

但是索引t1_pk存在。


我的要求是在EntryId上创建一个降序索引

申请所有查询。


谢谢

Tuhin

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

推荐答案

Oracle将desc索引视为函数库索引。


首先尝试创建一个普通索引（没有desc），看看oracle如何回应
。我生气地不知道为什么oracle会抛出那个错误

..检查表和索引是否在同一架构中。

以下是一个替代解决方案。

我想你正在尝试使用desc索引进行查询使用


order by column_name desc


优化器只使用函数库索引在
查询中使用确切的函数。


你可以用普通索引创建主键然后创建一个desc

索引然后在会话中设置参数query_rewrite_enabled = true

或系统级别并查看解释计划，看是否使用了desc索引

。

那里如果你使用list_name desc命令那么好的机会是

优化器将使用desc索引。


Faheem

Faheem

tk****@ipolicynet.com （Tuhin Kumar）在留言中写道：< e4 * *************************@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>...

当我尝试执行
后，Oracle会给出错误ORA-01418;

创建唯一索引t1_pk在TABLE1上（EntryId DESC）;

如果我尝试使用上面的索引t1_pk
添加主键Contraint，如下所示：
ALTER TABLE TABLE1 ADD CONSTRAINT TABLE1_PK PRIMARY KEY（EntryId ）
使用索引t1_pk;

出现以下错误：ORA-01418：指定的索引不存在
但索引t1_pk存在。
我的要求是在EntryId上创建一个降序索引，它将被应用于所有查询。

感谢
Tuhin

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

Oracle将desc索引视为函数库索引。


首先尝试创建一个普通索引（没有desc）并看看

oracle回应。我生气地不知道为什么oracle会抛出那个错误

..检查表和索引是否在同一架构中。

以下是一个替代解决方案。

我想你正在尝试使用desc索引进行查询使用


order by column_name desc


优化器只使用函数库索引在
查询中使用确切的函数。


你可以用普通索引创建主键然后创建一个desc

索引然后在会话中设置参数query_rewrite_enabled = true

或系统级别并查看解释计划，看是否使用了desc索引

。

那里如果你使用list_name desc命令那么好的机会是

优化器将使用desc索引。


Faheem

Faheem

tk****@ipolicynet.com （Tuhin Kumar）在留言中写道：< e4 * *************************@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>...

当我尝试执行
后，Oracle会给出错误ORA-01418;

创建唯一索引t1_pk在TABLE1上（EntryId DESC）;

如果我尝试使用上面的索引t1_pk
添加主键Contraint，如下所示：
ALTER TABLE TABLE1 ADD CONSTRAINT TABLE1_PK PRIMARY KEY（EntryId ）
使用索引t1_pk;

出现以下错误：ORA-01418：指定的索引不存在
但索引t1_pk存在。
我的要求是在EntryId上创建一个降序索引，它将被应用于所有查询。

感谢
Tuhin

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

嗨Farheem，


感谢您的回复。我尝试了

ALTER SESSION设置QUERY_REWRITE_ENABLED = TRUE;

但是解释计划仍然选择了主键索引。如果我从表中删除

主键约束，它会执行全表骗局但不使用

索引t1_pk。


谢谢，

Tuhin

fa **** ***@yahoo.com （FaheemRao）在留言中写道：< 43 ************************** @ posted 。谷歌。 com> ...

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将desc索引视为函数库索引。

首先尝试创建一个普通索引（没有desc），看看如何
oracle回应。我生气地不知道为什么oracle会抛出那个错误
。检查表和索引是否在同一模式中。
以下是一个替代方案。
我猜你试图使用desc索引进行查询使用

order by column_name desc 查询中使用精确函数时，优化器才使用函数库索引。

您可以使用普通索引创建主键，然后创建一个desc
索引然后在会话
或系统级别设置参数query_rewrite_enabled = true并查看解释计划以查看是否正在使用desc索引。
如果您使用column_name desc命令，则很有可能那个
优化器将使用desc索引。

Faheem

Faheem

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

这篇关于如何在主键上创建降序索引。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！