performance - In Oracle, does the unique constraint include an index implicitly? -

this question performance issue, example, if add unique constraint such as:

alter table staffs add constraint test unique (company_name, staff_id); 

should add unique index performance issue?

create unique index test2 on staffs (company_name, staff_id);  

for primary key, can see there must corresponding index in dba_indexes system table, have not seen equivalent case unique constraint

"i have not seen equivalent case unique constraint"

hmmmm, sure?

sql> create table t23   2  (id number   3   , col1 date)   4  /  table created.  sql> alter table t23   2  add constraint t23_uk unique (id)   3  /  table altered.  sql> select index_name, uniqueness   2  user_indexes   3  table_name='t23'   4  /  index_name                     uniquenes ------------------------------ --------- t23_uk                         unique  sql>  

---

note can use existing index, , doesn't have unique. means index name might not match constraint name (this work primary keys):

sql> alter table t23 drop constraint t23_uk;  table altered.  sql> select index_name, uniqueness   2  user_indexes   3  table_name='t23'   4  /  no rows selected  sql> create index t23_idx on t23(id)   2  /  index created.  sql> select index_name, uniqueness   2  user_indexes   3   table_name='t23'   4  /  index_name                     uniquenes ------------------------------ --------- t23_idx                        nonunique  sql> alter table t23   2  add constraint t23_uk unique (id)   3  /  table altered.  sql> 

does non-unique index enforce unique constraint? yes does:

sql> insert t23 values (1, sysdate)   2  /  1 row created.  sql> r   1* insert t23 values (1, sysdate) insert t23 values (1, sysdate) * error @ line 1: ora-00001: unique constraint (apc.t23_uk) violated  sql> drop index t23_idx    2  / drop index t23_idx            * error @ line 1: ora-02429: cannot drop index used enforcement of unique/primary key   sql>  

we can check data dictionary see index associated constraint:

sql> select constraint_name, constraint_type, index_name   2  user_constraints   3  table_name = 't23'   4  /  constraint_name                c index_name ------------------------------ - ------------------------------ t23_uk                         u t23_idx  sql>