database Oracle 在哪些情况下会自动创建索引？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2154091/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
In which cases will Oracle create indexes automatically?
提问by Tadeusz Kopec
回答by Tadeusz Kopec
I'll try to consolidate given answers and make it community wiki.
So indexes are automatically created by Oracle for such cases:
我会尝试整合给定的答案并使其成为社区维基。
因此，对于这种情况，Oracle 会自动创建索引：
- APC: For primary key and unique key unless such indexes already exist.
- APC: For LOB storage and XMLType.
- Gary: For table with a nested table.
- Jim Hudson: For materialized view.
- APC：用于主键和唯一键，除非此类索引已经存在。
- APC：用于 LOB 存储和 XMLType。
- Gary：用于带有嵌套表的表。
- Jim Hudson：对于物化视图。
回答by APC
Firstly, Oracle does not always create an index when we create a primary or unique key. If there is already an index on that column it will use it instead...
首先，当我们创建主键或唯一键时，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>
... note that MY_MANUAL_IDXis not a unique index; it doesn't matter ...
... 请注意，这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>
There is another case when Oracle will automatically create an index: LOB storage....
还有一种情况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>
edit
编辑
The database treats XMLType same as other LOBs...
数据库将 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>
回答by Jim Hudson
No, we're getting closer but that's not quite a complete list yet.
不，我们越来越近了，但这还不是一个完整的清单。
There will also be an index automatically created when you create materialized view since Oracle needs to be able to quickly identify the rows when doing a fast refresh. For rowid based materialized views, it uses I_SNAP$_tablename. For primary key materialized views, it uses the original PK name, modified as necessary to make it unique.
创建物化视图时还会自动创建一个索引，因为 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
回答by Gary Myers
And another one, if you create a table with a nested table you get an index created automatically. Object based storage in general can do this as there can be hidden tables created.
还有一个，如果你创建一个带有嵌套表的表，你会自动创建一个索引。由于可以创建隐藏表，因此基于对象的存储通常可以做到这一点。
I think schema-based XMLTypes will also do it.
我认为基于模式的 XMLTypes 也能做到。
回答by b.roth
Yes, that's the complete list. Oracle automatically creates an index for each UNIQUE or PRIMARY KEY declaration.
是的，这是完整的清单。Oracle 自动为每个 UNIQUE 或 PRIMARY KEY 声明创建一个索引。
