oracle自动索引,在哪些情况下,Oracle会自动创建索引?

首先,当我们创建主键或唯一键时,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>