this question is for performance issue, For example, if I would add a unique constraint such as:
ALTER TABLE Staffs ADD CONSTRAINT test UNIQUE (Company_Name, Staff_ID); should I add a unique index for performance issue?
CREATE UNIQUE INDEX test2 ON Staffs (Company_Name, Staff_ID); For Primary key, I can see there must be a corresponding index in dba_indexes system table, but I have not seen the equivalent for the case unique constraint
"I have not seen the equivalent for the case unique constraint"
Hmmmm, are you 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 from user_indexes 3 where table_name='T23' 4 / INDEX_NAME UNIQUENES ------------------------------ --------- T23_UK UNIQUE SQL> Note that we can use an existing index, and it doesn't have to be unique. But this means the index name might not match the constraint name (this would also work for primary keys):
SQL> alter table t23 drop constraint t23_uk; Table altered. SQL> select index_name, uniqueness 2 from user_indexes 3 where table_name='T23' 4 / no rows selected SQL> create index t23_idx on t23(id) 2 / Index created. SQL> select index_name, uniqueness 2 from user_indexes 3 where 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 the non-unique index enforce the unique constraint? Yes it does:
SQL> insert into t23 values (1, sysdate) 2 / 1 row created. SQL> r 1* insert into t23 values (1, sysdate) insert into t23 values (1, sysdate) * ERROR at line 1: ORA-00001: unique constraint (APC.T23_UK) violated SQL> drop index t23_idx 2 / drop index t23_idx * ERROR at line 1: ORA-02429: cannot drop index used for enforcement of unique/primary key SQL> We can check the data dictionary to see which index is associated with a constraint:
SQL> select constraint_name, constraint_type, index_name 2 from user_constraints 3 where table_name = 'T23' 4 / CONSTRAINT_NAME C INDEX_NAME ------------------------------ - ------------------------------ T23_UK U T23_IDX SQL> 
