14.1.9 Using Nonunique Indexes to Enforce Uniqueness
You can use an existing nonunique index on a table to enforce uniqueness, either for UNIQUE constraints or the unique aspect of a PRIMARY KEY constraint. The advantage of this approach is that the index remains available and valid when the constraint is disabled. Therefore, enabling a disabled UNIQUE or PRIMARY KEY constraint does not require rebuilding the unique index associated with the constraint. This can yield significant time savings on enable operations for large tables.
Using a nonunique index to enforce uniqueness also lets you eliminate redundant indexes. You do not need a unique index on a primary key column if that column is included as the prefix of a composite index. You can use the existing index to enable and enforce the constraint. You also save significant space by not duplicating the index. However, if the existing index is partitioned, then the partitioning key of the index must also be a subset of the UNIQUE key; otherwise, Oracle Database creates an additional unique index to enforce the constraint.
But i still think using unique indexes to support uniqueness and primary key constraint is a better choice. And uniqueness is affecting oracle optimizer.
SQL> select constraint_name,status,index_name from user_constraints;
CONSTRAINT_NAME STATUS INDEX_NAME
------------------------------ -------- ------------------------------
SYS_C009770 ENABLED
SYS_C009771 ENABLED
SYS_C009772 ENABLED
SYS_C009773 ENABLED
EMPLOYEES_PK ENABLED EMPLOYEE_N1
SQL> select index_name,uniqueness from user_indexes;
INDEX_NAME UNIQUENES
------------------------------ ---------
EMPLOYEE_N1 NONUNIQUE
SQL> set autotrace traceonly explain statistics
SQL> select * from employees where employee_id=107;
Execution Plan
----------------------------------------------------------
Plan hash value: 2553029091
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 133 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 133 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMPLOYEE_N1 | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=107)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1083 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter table employees disable constraint employees_pk;
Table altered.
SQL> drop index employee_n1;
Index dropped.
SQL> create unique index employee_n1 on employees(employee_id);
Index created.
SQL> alter table employees enable constraint employees_pk;
Table altered.
SQL> select index_name,uniqueness from user_indexes;
INDEX_NAME UNIQUENES
------------------------------ ---------
EMPLOYEE_N1 UNIQUE
SQL> select constraint_name,status,index_name from user_constraints;
CONSTRAINT_NAME STATUS INDEX_NAME
------------------------------ -------- ------------------------------
SYS_C009770 ENABLED
SYS_C009771 ENABLED
SYS_C009772 ENABLED
SYS_C009773 ENABLED
EMPLOYEES_PK ENABLED EMPLOYEE_N1
SQL> select * from employees where employee_id=107;
Execution Plan
----------------------------------------------------------
Plan hash value: 1716760386
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 133 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 133 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMPLOYEE_N1 | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=107)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
987 bytes sent via SQL*Net to client
409 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed