Menu
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?
777
Oracle enforces a UNIQUE key or PRIMARY KEY integrity constraint by creating a unique index on the unique key or primary key. This index is automatically created by Oracle when the constraint is enabled; no action is required by the issuer of the CREATE TABLE or ALTER TABLE statement to create the index.
A primary index is automatically created for the primary key and ensures that the primary key is unique. You can use the primary index to retrieve and access objects from the database. The unique index is a column, or an ordered collection of columns, for which each value identifies a unique row.
Oracle Database automatically creates an index to enforce a UNIQUE or PRIMARY KEY integrity constraint. In general, it is better to create such constraints to enforce uniqueness, instead of using the obsolete CREATE UNIQUE INDEX syntax.
I'll try to consolidate given answers and make it community wiki.
So indexes are automatically created by Oracle for such cases:
106
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> If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
