Отличается ли производительность уникального индекса с уникальным столбцом или без него?

Question

Есть ли разница в производительности, когда столбец с уникальным индексом имеет также уникальное ограничение на этом же столбце?

Известно, что уникальные и неуникальные индексы имеют различия в производительности.

Но вопрос в том, будет ли разница в производительности, если столбец имеет просто уникальный индекс:

create table t (val int);

create unique index ui_t on t (val);

или, если на этом же столбце будет ещё и уникальное ограничение:

alter table t add constraint uq_t unique (val);

_{Свободный перевод вопроса Unique index performance with or without unique column от участника @oramas}

Answer 1

БД обеспечивает выполнение уникальных ограничений посредством (уникальных) индексов. Если на момент создания уникального ограничения необходимый ему индекс отсутсвует, то будет неявно создан уникальный индекс.

При проверке дубликатов записей, выполнении запросов и тд, будет использоваться индекс, а не ограничение. Поэтому, в основном производительность не будет отличаться:

create table t (c1 int, c2 int);

alter table t add constraint uq unique (c1);
  
create unique index ui on t (c2);
  
insert into t
    select rownum, rownum from dual
    connect by level <= 1e4;
commit;

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

alter session set statistics_level = all;

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         | UQ   |      1 |      1 |      1 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------
    
select * from t where  c2 = 1;

----------------------------------------------------------------------------------------------
| 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 |
----------------------------------------------------------------------------------------------

Но существует одно исключение, уникальное ограничение может быть источником для внешнего ключа, а уникальный индекс сам по себе - не может:

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);

Table T altered.

Если создан внешний ключ на уникальном ограничении, то это позволит оптимизатору исключить излишнее чтение таблиц в некоторых запросах, что может дать значительный выигрыш в производительности.

В первом запросе БД не будет читать подсоединяемую таблицу, чтобы проверить условие соединения, так как значение внешнего ключа отличного от NULL гарантирует, что соответствующее значение в другой таблице есть:

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

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |   5050 |00:00:00.01 |      22 |
|*  1 |  TABLE ACCESS FULL| T    |      1 |  10000 |   5050 |00:00:00.01 |      22 |
------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("T1"."C2" IS NOT NULL)

select t1.*
from t t1 join t t2 on t2.c2 = t1.c1;
    
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   5050 |00:00:00.02 |     126 |
|   1 |  NESTED LOOPS      |      |      1 |  10000 |   5050 |00:00:00.02 |     126 |
|   2 |   TABLE ACCESS FULL| T    |      1 |  10000 |   5050 |00:00:00.01 |      22 |
|*  3 |   INDEX UNIQUE SCAN| UI   |   5050 |      1 |   5050 |00:00:00.01 |     104 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("T2"."C2"="T1"."C1")

_{Свободный перевод ответа от участника @Chris Saxon}

БЛОГ НА HUSL

Отличается ли производительность уникального индекса с уникальным столбцом или без него?

Ответы (1 шт):