解决方法
首先，当我们创建主键或唯一键时，Oracle并不总是创建一个索引。如果该列上已经有一个索引，它会改为使用它…
sql> create table t23 (id number not null) 2 / Table created. sql> create index my_manual_idx on t23 ( id ) 2 / Index created. sql> select index_name from user_indexes 2 where table_name = 'T23' 3 / INDEX_NAME ------------------------------ MY_MANUAL_IDX sql>
…注意MY_MANUAL_IDX不是唯一的索引;没关系
sql> alter table t23 2 add constraint t23_pk primary key (id) using index 3 / Table altered. sql> select index_name from user_indexes 2 where table_name = 'T23' 3 / INDEX_NAME ------------------------------ MY_MANUAL_IDX sql> drop index my_manual_idx 2 / drop index my_manual_idx * ERROR at line 1: ORA-02429: cannot drop index used for enforcement of unique/primary key sql>
还有另一种情况，Oracle会自动创建索引：LOB存储….
sql> alter table t23 2 add txt clob 3 lob (txt) store as basicfile t23_txt (tablespace users) 4 / Table altered. sql> select index_name from user_indexes 2 where table_name = 'T23' 3 / INDEX_NAME ------------------------------ MY_MANUAL_IDX SYS_IL0000556081C00002$$ sql>
编辑
数据库将XMLType与其他LOB相同…
sql> alter table t23 2 add xmldoc xmltype 3 / Table altered. sql> select index_name from user_indexes 2 where table_name = 'T23' 3 / INDEX_NAME ------------------------------ MY_MANUAL_IDX SYS_IL0000556081C00002$$ SYS_IL0000556081C00004$$ sql>
