Unique index performance with or without unique column

Question

Is there any difference on performance when an unique-indexed column has also a unique constraint on the related column?

I know that unique indexes and non-unique indexes have differences on performance.

But my question is, will there be any difference on performance if the column has both unique constraint and unique index, and just unique index without a unique constraint?

Another question is, does the column statistics have any affect on unique index usage?

Answer 1

Oracle Database policies unique constraints with (unique) indexes.

When checking for duplicate entries, querying the table, etc. the database will use the index. Not the constraint. So for the most part performance will come out the same:

create table t (
  c1 int, c2 int
);

alter table t 
  add constraint u 
  unique ( c1 );
  
create unique index ui 
  on t ( c2 );
  
insert into t
with rws as (
  select level x from dual
  connect by level <= 10000
)
  select x, x from rws;
  
commit;

exec dbms_stats.gather_table_stats ( user, 't' ) ;

alter session set statistics_level = all;
set serveroutput off

select * from t
where  c1 = 1;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));

----------------------------------------------------------------------------------------------    
| Id  | Operation                   | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |    
----------------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT            |      |      1 |        |      1 |00:00:00.01 |       3 |    
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |      1 |      1 |      1 |00:00:00.01 |       3 |    
|*  2 |   INDEX UNIQUE SCAN         | U    |      1 |      1 |      1 |00:00:00.01 |       2 |    
----------------------------------------------------------------------------------------------

select * from t
where  c2 = 1;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));

----------------------------------------------------------------------------------------------    
| Id  | Operation                   | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |    
----------------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT            |      |      1 |        |      1 |00:00:00.01 |       3 |    
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |      1 |      1 |      1 |00:00:00.01 |       3 |    
|*  2 |   INDEX UNIQUE SCAN         | UI   |      1 |      1 |      1 |00:00:00.01 |       2 |    
----------------------------------------------------------------------------------------------

There is one exception. A unique constraint can be the target of a foreign key. Whereas a unique index (alone) can't:

alter table t
  add constraint fk 
  foreign key ( c1 )
  references t ( c2 );
  
ORA-02270: no matching unique or primary key for this column-list
  
alter table t
  add constraint fk 
  foreign key ( c2 )
  references t ( c1 );

Provided you created unique and foreign key constraints, this enables the optimizer to eliminate tables in some queries. Which could give large performance benefits:

select t1.* from t t1
join   t t2
on     t1.c1 = t2.c2;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));

-------------------------------------------------------------------------------------    
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |    
-------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT   |      |      1 |        |   5000 |00:00:00.01 |     202 |    
|   1 |  NESTED LOOPS      |      |      1 |  10000 |   5000 |00:00:00.01 |     202 |    
|   2 |   TABLE ACCESS FULL| T    |      1 |  10000 |   5000 |00:00:00.01 |      60 |    
|*  3 |   INDEX UNIQUE SCAN| UI   |   5000 |      1 |   5000 |00:00:00.01 |     142 |    
------------------------------------------------------------------------------------- 

select t1.* from t t1
join   t t2
on     t1.c2 = t2.c1;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));

------------------------------------------------------------------------------------    
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |    
------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT  |      |      1 |        |   5000 |00:00:00.01 |      60 |    
|*  1 |  TABLE ACCESS FULL| T    |      1 |  10000 |   5000 |00:00:00.01 |      60 |    
------------------------------------------------------------------------------------  

Table stats will affect whether the optimizer uses the index. If you search for unique values less than 100:

select * from t
where  c1 <= 100;

The optimizer is more likely to go for a full table scan if there are only 100 rows in the table. But if there are millions, the index becomes much more attractive.