oracle 升降序,关于Oracle降序索引的定意及回溯-数据库专栏,ORACLE

降序索引本质上是fbi，其具体定义可以通过 user_ind_expressions 或dba_ind_expressions 查询。

那么同样的，降序索引只有在cbo下才能被使用。

connected to oracle9i enterprise edition release 9.2.0.4.0

connected as eygle

sql> create table t as select * from dba_users;

table created

sql> create index idx_username_desc on t(username desc);

index created

sql> select index_name,table_name,index_type from user_indexes where table_name=t;

index_name                     table_name                     index_type

—————————— —————————— —————————

idx_username_desc              t                              function-based normal

sql> select column_name,column_position,descend from user_ind_columns

2  where table_name=t;

column_name                    column_position descend

—————————— ————— ——-

sys_nc00013$                                 1 desc

sql>

sql> select * from user_ind_expressions where table_name=t;

index_name                     table_name                     column_expression              column_position

—————————— —————————— —————————— —————

idx_username_desc              t                              “username”                                   1