在哪些情况下,Oracle会自动创建索引?[英] In which cases will Oracle create indexes automatically?

本文是小编为大家收集整理的关于在哪些情况下,Oracle会自动创建索引?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

据我所知( )Oracle会自动为每个唯一或主键声明创建索引.这是在Oracle自动创建索引的完整列表吗?

推荐答案

我将尝试合并给定的答案并使之成为社区Wiki.
因此,对于这种情况,Oracle会自动创建索引:

  1. apc apc apc apc 除非已经存在,否则唯一的密钥.
  2. apc apc apc apc for LOB存储和xmltype.
  3. garary garary 嵌套表.
  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与其他群相同...

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

本文地址:https://www.itbaoku.cn/post/597590.html

问题描述

As far as I know (this page) Oracle automatically creates an index for each UNIQUE or PRIMARY KEY declaration. Is this a complete list of cases when indexes are created automatically in Oracle?

推荐答案

I'll try to consolidate given answers and make it community wiki.
So indexes are automatically created by Oracle for such cases:

  1. APC: For primary key and unique key unless such indexes already exist.
  2. APC: For LOB storage and XMLType.
  3. Gary: For table with a nested table.
  4. Jim Hudson: For materialized view.

其他推荐答案

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...

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_IDX is not a unique index; it doesn't matter ...

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....

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...

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>    

其他推荐答案

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.

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