In which cases will Oracle create indexes automatically?

Question

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?

Answer 1

Yes, that's the complete list. Oracle automatically creates an index for each UNIQUE or PRIMARY KEY declaration.

Answer 2

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>    

Answer 3

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

Answer 4

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.

Answer 5

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.