CODEWITHSUNDEEP
databaseoracle-databaseindexing
Tadeusz Kopec for Ukraine
Tadeusz Kopec for Ukraine

Reputation: 12403

In which cases will Oracle create indexes automatically?

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?

Upvotes: 26

Views: 32156

Answers (5)

Tadeusz Kopec for Ukraine
Tadeusz Kopec for Ukraine

Reputation: 12403

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.

Upvotes: 27

Gary Myers
Gary Myers

Reputation: 35401

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.

Upvotes: 3

Jim Hudson
Jim Hudson

Reputation: 8069

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

Upvotes: 4

APC
APC

Reputation: 146199

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>

Upvotes: 19

b.roth
b.roth

Reputation: 9532

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

Upvotes: 0

Related Questions