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

我将尝试整合给出的答案,使之成为社区维基。
因此,Oracle会自动为此类情况创建索引:

1. APC :对于主键和唯一键,除非已经存在此类索引。
2. 空气污染指数 :用于lob存储和xmltype。
3. Gary :对于具有嵌套表的表。
4. Jim Hudson :对于物化视图。

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

不,我们越来越近了,但这还不是一个完整的清单。

当您创建物化视图时,也会自动创建一个索引,因为Oracle需要在执行快速刷新时快速识别行。对于基于rowid的物化视图,它使用i_snap$_tablename。对于主键物化视图,它使用原始的pk名称,并根据需要进行修改以使其唯一。

create materialized view testmv 
refresh force with rowid
as select * from dual;

select index_name from user_indexes where table_name = 'TESTMV';

Index Name
--------------
I_SNAP$_TESTMV

另一个是,如果使用嵌套表创建表,则会自动创建索引。基于对象的存储通常可以这样做,因为可以创建隐藏表。

我认为基于模式的xmltypes也可以做到这一点。

是的,这是完整的清单。Oracle自动为每个唯一或主键声明创建一个索引。