Первичный ключ - составной или суррогатный? - Oracle, купить Oracle - Программные продукты

Источник: ln

Этот выпуск посвящен "вечной" теме выбора столбцов для первичного ключа. По мотивам случайно обнаруженного замечательного ответа Тома Кайта на вопросы, заданные в 2001-2003 годах

Первичный ключ - составной или суррогатный?

Том,

У меня есть таблица из 3 полей, комбинация значений которых уникальна для каждой записи. Вот эти поля:

Object_ID      CHAR(4)
Ticket_Number  NUMBER
Start_DateTime DATE

Ни одно из этих полей никогда не будет пустым, и ни один объект в один и тот же момент времени не будет иметь такое же значение Ticket_Number.

Поэтому мне кажется, что, вместо добавления нового поля, единственным назначением которого будет уникально идентифицировать строку в таблице, я могу использовать комбинацию этих трех полей. Но в руководстве PL/SQL Developer's Guide рекомендуется не использовать составные первичные ключи.

Что ты думаешь по этому поводу?

Надо учитывать также следующее:

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

С другой стороны, если лучше добавить новое числовое поле для идентификации записей, как проще всего увеличивать значение этого поля при каждой вставке? Есть ли в Oracle подобие типа данных autonumber MS Access?

Ответ Тома Кайта

Если требуется, чтобы "эти три поля уникально идентифицировали запись в любом случае", придется задавать по ним ограничение уникальности (UNIQUE CONSTRAINT) в любом случае. Если дублирование object_id,ticket_number,start_datetime - ошибка, ограничение уникальности НЕОБХОДИМО.

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

Чтобы получить тип "auto increment" в Oracle, необходимо выполнить:

create sequence my_seq;
create trigger my_trigger before insert on T for each row
begin
   select my_seq.nextval into :new.ID from dual;
end;
/

Иногда высказывают опасение, что при генерации последовательных номеров таким образом возможны пропуски (связанные с откатом транзакции, например - В.К. )...

Да, использование последовательности не гарантирует отсутствие пропусков при нумерации - в любой не однопользовательской системе они точно будут. Единственный способ нумеровать без пропусков - обрабатывать транзакции по одной. Последовательности предназначались не для этого, и любая система с таким требованием не будет масштабироваться (для компьютерных систем оно вообще смешное - при обработке документов вручную еще куда ни шло, но в компьтерной системе - оно просто бессмысленно).

Последовательности - хорошо масштабируемый способ генерации суррогатных ключей.

Я считаю, что составные ключи прекрасно работают и могут использоваться при наличии внешних ключей, но:

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

Проще написать:

select * from p, c where p.primary_key = c.foreign_key

чем

select * from p, c where p.id1 = c.fk1 and p.id2 = c.fk2 and p.id3 = c.fk3

да и работает первый запрос быстрее.

Эти соображения необходимо учитывать. Как я уже писал, если составной первичный ключ не используется в качестве внешнего во многих таблицах - используйте его. В противном случае серьезно задумайтесь над использованием суррогатного ключа на базе последовательности (а про "пропуски" значений не думайте вовсе - важно, что получается уникальный идентфикатор).

Составной ключ в одном столбце

Мне интересно твое мнение о том, чтро делать, если клиенты настаивают на использовании "магических кодов" - составных ключей, впихнутых в один столбец.

Простой пример - следующий идентификатор события:

03-40123    Формат:  FY-XNNNN

где:

FY    = 2 цифры финансового года
X     = 1 цифра, задающая тип события
NNNN  = 4 цифры, задающие порядковый номер события данного типа в этом финансовом году

Лично я пыаюсь избегать такого рода идентификаторов (как бы они не генерировались) как чумы, по следующим, как мне кажется, очевидным, причинам:

Если при вводе данных произошла ошибка, и ключ оказался неверным (например, событие было типа 3, а не 4) - все очень печално, ведь это событие уже всем известно как 03-40123
Делаются конкретные предположения о максимальных значениях:
1) В году никогда не будет более 10000 событий типа 1
2) Нельзя добавить одиннадцатый тип события
3) Две цифры года - это мы уже проходили...
Такие ключи неудобно реализовывать
Зачем вообще эта возможность узнать финансовый год и тип события без дополнительного запроса?

Я периодически сталкиваюсь с этой проблемой, обычно - при обновлении старых систем и/или систем "бумажного" документооборота, пользователи которых не хотят изменять систему нумерации. Часто мне удается уговорить клиента перейти на простые последовательности, но не всегда.

Я знаю, что ты не сторонник простых правил, но оправдывает ли природа данных или необходимость "запоминающегося" ключа создание такого типа ключей? Когда ты считаешь обоснованным использование такого составного ключевого столбца для идентфикации данных? Добавишь ли ты собственный суррогатный ключ и позволишь пользователям хранить свои магические коды где угодно, или будешь настаивать на использовании простой последовательности?

Ответ Тома Кайта

Такого рода поля могут (и должны) быть ПРОИЗВОДНЫМИ от других данных. Клиенту нет необходимости знать, как в физической схеме фактически реализован первичный ключ - это деталь реализации.

Так что, я бы сделал так:

create table t ( id int number primary key, /* заполняется значениями последовательности */
                 fy date,
                 incident_type varchar2(1),
                 goofy_number  number,
                 ...
create view v as
select
t.*, 
to_char(fy,'yy')//'-'//incident_type//to_char(goofy_number,'fm0000') their_field
from t;

Можно даже создать индекс по функции (function-based index) по полю their_field, если они собираются искать по его значениям.

Комментарий читателя от 23 ноября 2002 года

Как обычно, твое решение предельно ясно. Мне особенно понравилась идея про индекс по функции!

Однако это не рашает мою проблему "изменения адреса".

Как я попытался объяснить в первом пункте, как только значения fy, incident_type, и goofy_number определены и строка вставлена, значение their_number тоже неявно определено. С этого момента значение their_number может выдаваться в отчетах, сообщаться заинтересованным сторонам и т.д.

Если оказывается, что, например, значение incident_type перовначально оказалось ошибочным, и оно изменяется, значения their_number в базе данных и в отчетах, у заинтересованных сторон и т.д. перестают совпадать.

Можно строить their_number по столбцам, которые не меняются, но это не всегда соответствует требованиям клиента.

Можно строить значение their_number при вставке, помещать в отдельный столбец (с ограничением уникальности, а не первичного ключа), и больше никогда его не изменять при изменении базовых значений его компонентов.

В конечном итоге, меня интересует следующее:

Как "эксперт", нанятый для создания солидных моделей данных, не выхожу ли я за пределы моих полномочий (и не трачу ли зря время), часами пытаясь убедить клиентов не использовать their_number, а заменить его простым значением последовательности?

Ответ Тома Кайта

Если вы представили им все факты, как в вопросе, продемонстрировав, что это может привести к ошибкам в интерпретации данных, и они все равно настаивают на своем - вы сделали все, что могли. Можете включить СВОЙ первичный ключ в отчеты, чтобы при возникновении проблемы можно было получить соотвествующее значение. Вы не выходите за пределы своих полномочий. Я неоднократно повторял, что наша работа как раз и состоит в том, чтобы обращать на подобные вещи мнимание тех, кто не является профессиональным программистом. Последний раз пободная проблема возникла, когда меня спросили на сайте, как выбрать N случайных строк из таблицы. Я написал, как это сделать, но проблема все усложнялась, пока не выяснилось, что нужна случайная выборка 4 строк из сложного запроса со множеством соединений и т.п. Причем, выборка эта должна была делаться сотни/тысячи раз в день. Для этого требовалось множество ресурсов.

А зачем все это понадобилось? Чтобы на портале "вывесить" фотографии 4 случайно выбранных сотрудников. Я ответил: "Сообщите клиентам, что 90% ресурсов машины теперь будет уходить на выдачу этих 4 фотографий, - захотят ли они за это платить". Мнения разделились - надо ли "знать свое место" и тупо, как бараны, делать то, что требуют, или доказывать, что практически бесполезная возможность дается дорогой ценой, и не нужна.

Я бы продолжал настаивать на своем - ваши аргументы на 100% верны. Если они решат не прислушиваться к советам, попытайтесь, по возможности, защитить их от проблем (с помощью суррогатного ключа).

Не хотел бы я работать там, где за год происходит только 9999 событий... Маловато перспектив для роста... А первого января придется этот смешной счетчик снова в 0 сбрасывать...

Изменение составного первичного ключа

У нас есть две таблицы следующего вида:

create table t1(c1 number, c2 varchar2(50),
  constraint t1p primary key(c1, c2));

create table t2(c1 number, c2 varchar2(50),  c3 number,
  constraint t2p primary key(c1, c2, c3),
  constraint t2f foreign key (c1, c2) references t1(c1, c2));

insert into t1(c1, c2) values(1, 'c2');
insert into t2(c1, c2, c3) values(1, 'c2', 3);

Необходимо изменить значение c2 в таблице t1. Нет ли способа изменить этот первичный ключ, не создавая суррогатного?

Ответ Тома Кайта

Раз так, c1,c2 не является первичным ключом - первичный ключ не должен меняться.

Если хотите, используйте ограничения с отложенной проверкой (deferrable constraints).

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

Изменение первичного ключа. Комментарий от 15 января 2003 года

Пакет работает прекрасно.

Вопросы:

После изменения первичного ключа, как "отвязать" пакет от таблицы (он необходим для других таблиц, так что просто удалить его я не могу).
Этот пакет кажестя лучшим решением в нашем случае, поскольку остальной код менять не придется. Помимо снижения производительности, какие еще недостатки имеет данное решение?
"Ограничения с отложенной проверкой", о которых вы пишете, это когда добавляют ключевое слово deferrable для внешних ключей, так что можно сначала изменить первичный ключ, а потом - внешний. Вы это имеете в виду? Например:
```
create table t1(c1 number, c2 varchar2(50),
  constraint t1p primary key(c1, c2));

create table t2(c1 number, c2 varchar2(50),  c3 number,
  constraint t2p primary key(c1, c2, c3),
  constraint t2f foreign key (c1, c2) 
    references t1(c1, c2) deferrable initially deferred);
```

Ответ Тома Кайта

Не знаю, как относиться к утверждения, что "Пакет работает прекрасно". Сомнительный комплимент, как по мне...

Пакет не связывается с таблицей. Он создает ряд триггеров и хранимых процедур, для указанной таблицы - он генерирует специфический код. Сам по себе он ни с одним объектом не связан.
Вы имеете ввиду помимо того факта, что изменение первичного ключа вообще недачная идея, связанная с ошибкой проектирования?

Да, проверку внешних ключей можно отложить и делать так:

set constraints all deferred;
update parent primary key;
update child1 fkey;
update child2 fkey;
set constraints all immediate <<=== если это не сработает, не было зафиксировано изменение в подчиненной таблице

Изменение первичного ключа. Комментарий от 16 января 2003 года

Сразу после установки пакета и создания таблиц t1, t2, t3, для изменения первичного ключа мне пришлось сначала выполнить команду:

exec update_cascade.on_table('t2')

Но если открыть новый сеанс после этого, изменить первичный ключ мне удалось и без команды 'exec update_cascade.on_table('t2')'. Но я не хочу, чтобы пользователи постоянно меняли этот первичный ключ.

Вот пример:

SQL> connect UCDEMO/UCDEMO@e2rs Connected. SQL> update t2 2 set b=900 3 where b = 6; -- первичный ключ изменен без выполнения 'exec update_cascade.on_table('t2')' 1 row updated.

Ответ Тома Кайта

Команда exec update_cascade.on_table('t2') создала (как и описано на указанной странице) триггеры и пакеты для поддержки каскадного изменения. Если его больше не нужно поддерживать, УДАЛИТЕ их.

Как насчет использования sys_guid() вместо последовательности для генерации значений первичного ключа?

Какие преимущества и недостатки связаны с использованием sys_guid() (подозреваю, что используется больше места на диске?) sys_guid() можно указать в качестве стандартного значения (и не понадобиться триггер). Понятно, что "пропуски" значений - не проблема :)

Ответ Тома Кайта

Да, RAW(16) - больше по размеру, чем большинство числовых полей. Его не так удобно записывать, как число. Во многих случаях данные типа raw не обрабатываются - они неявно преобразуются в 32-байтовую строку типа varchar2. Не уверен, что использовал бы этот подход без веских причин.

Первичный ключ: sys_guid или последовательность

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

Результаты показали следующее:

По времени работы последовательности - быстрее (я использовал опцию cache с кэшем размером 5000) - разброс значений был велик, но на разных прогонах последовательности оказались от 52% до 90% быстрее, чем вызовы sys_guid.
Думаю, причина в кэшировании значений последовательностей в области sga, правильно? Однако странно, почему sys_guid работает медленнее - как внутренне реализована функция sys_guid?
Понятно, что значения последовательности занимают меньше места (примерно в 2 раза)

Однако sys_guid использует меньше защелок, в частности, следующих:

STAT...session logical reads   319,026    317,195    -1,831
STAT...db block gets           318,025    315,680    -2,345
LATCH.cache buffers chains   1,398,729  1,366,857   -31,872
LATCH.library cache            407,043    207,830  -199,213
LATCH.sequence cache           300,040          0  -300,040

Не мог бы ты объяснить, что это за защелки - я думаю, последние две связаны с получением и установкой следующего значения для сеанса.

С чем связано большее количество db block gets и cache buffer chains при использовании последовательностей? Судя по руководству " Oracle Reference " cache buffer chains связаны с конфликтами при доступе к блоку, т.е. последовательности многократно обращаются к одному и тому же блоку (вероятно, во внутренней таблицеЮ используемой для хранения последовательностей - таблица seq$)?

test@apps> @schema
test@apps> drop sequence my_seq;

Sequence dropped.

Elapsed: 00:00:00.09

test@apps> create sequence my_seq cache 5000;

Sequence created.

Elapsed: 00:00:00.02

test@apps> drop table t_guid ;

Table dropped.

Elapsed: 00:00:04.51

test@apps> create table t_guid
  2  (
  3    id RAW(16) default sys_guid() not null primary key,
  4    value number
  5  );

Table created.

Elapsed: 00:00:00.09

test@apps> drop table t_seq ;

Table dropped.

Elapsed: 00:00:01.44

test@apps> create table t_seq
  2  (
  3    id int not null primary key,
  4    value number
  5  );

Table created.

Elapsed: 00:00:00.08

test@apps> @data
test@apps> create or replace package run_benchmark_pkg as
  2    g_number_of_records constant integer := 100000 ;
  3  
  4    procedure insert_with_seq;
  5    procedure insert_with_guid;
  6  end;
  7  /

Package created.

Elapsed: 00:00:00.09

test@apps> create or replace package body run_benchmark_pkg as
  2    procedure insert_with_seq
  3    is
  4    begin
  5       for i in 1 .. g_number_of_records
  6       loop
  7         insert into t_seq values( my_seq.nextval, i);
  8       end loop;
  9    end;
 10  
 11    procedure insert_with_guid
 12    is
 13    begin
 14       for i in 1 .. g_number_of_records
 15       loop
 16         insert into t_guid (value) values( i);
 17       end loop;
 18    end;
 19  
 20  end;
 21  /

Package body created.

Elapsed: 00:00:00.16

test@apps> begin
  2    runstats_pkg.rs_start;
  3    run_benchmark_pkg.insert_with_seq;
  4    runstats_pkg.rs_middle;
  5    run_benchmark_pkg.insert_with_guid;
  6    runstats_pkg.rs_stop(200);
  7  end;
  8  /

Run1 ran in 5728 hsecs
Run2 ran in 7337 hsecs
run 1 ran in 78.07% of the time
    
Name                                        Run1              Run2       Diff
STAT...calls to get snapshot s           100,582           100,847        265
STAT...commit cleanouts                      423               708        285
STAT...commit cleanouts succes               423               708        285
LATCH.list of block allocation               567               858        291
STAT...immediate (CURRENT) blo               372               674        302
STAT...calls to kcmgcs                       396               718        322
LATCH.transaction allocation                 864             1,266        402
STAT...consistent gets - exami               670             1,130        460
STAT...consistent gets                     1,001             1,515        514
STAT...free buffer requested               2,916             3,775        859
STAT...change write time                   2,455             3,775      1,320
STAT...session logical reads             319,026           317,195     -1,831
STAT...dirty buffers inspected             1,996             3,991      1,995
STAT...free buffer inspected               1,996             3,991      1,995
LATCH.redo allocation                    205,074           207,309      2,235
STAT...redo entries                      203,898           206,150      2,252
STAT...db block gets                     318,025           315,680     -2,345
LATCH.cache buffers lru chain              4,826             7,585      2,759
STAT...db block changes                  408,338           411,971      3,633
LATCH.checkpoint queue latch               8,652            12,829      4,177
LATCH.cache buffers chains             1,398,729         1,366,857    -31,872
LATCH.library cache                      407,043           207,830   -199,213
LATCH.sequence cache                     300,040                 0   -300,040
STAT...redo size                      49,988,292        56,368,192  6,379,900
    
Run1 latches total versus runs -- difference and pct
Run1              Run2                   Diff     Pct
3,946,936         3,426,263          -520,673 115.20%

PL/SQL procedure successfully completed.

Elapsed: 00:02:11.30

test@apps> exec show_space ( 'T_GUID')
Unformatted Blocks .....................              30
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               1
FS4 Blocks (75-100).....................              31
Full Blocks        .....................             370
Total Blocks............................             512
Total Bytes.............................       4,194,304
Total MBytes............................               4
Unused Blocks...........................              64
Unused Bytes............................         524,288
Last Used Ext FileId....................               9
Last Used Ext BlockId...................         177,800
Last Used Block.........................              64

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.27

test@apps> exec show_space ( 'T_SEQ')
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               1
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................              40
Full Blocks        .....................             203
Total Blocks............................             256
Total Bytes.............................       2,097,152
Total MBytes............................               2
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               9
Last Used Ext BlockId...................         176,776
Last Used Block.........................             128

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.22

test@apps> spool off

Ответ Тома Кайта

Хотя это и сказано в документации, sys_guid обращается к ОС и, несомненно, требует больше ресурсов процессора
```
LATCH.cache buffers chains   1,398,729  1,366,857  -31,872
```
Защелки для получения блоков из буферного кэша... Последовательности хранятся в блоках... Эти защелки связаны с получением буфера для изменения последовательности и/или получения ее текущего значения.
```
LATCH.library cache          407,043    207,830    -199,213
```
Защелки в библиотечном кэше. Определение последовательности находится именно там. Обращение к последовательности вызывает установку этих защелок.
```
LATCH.sequence cache         300,040          0    -300,040
```
Очевидно ;) Это действительно защелки, обеспечивающие поддержку последовательности.

Комментарий от 4 августа 2003 года

В документации Oracle 9.0.1.1.1 сказано, что: "Составной первичный ключ может содержать не более 32 столбцов". Но я попробовал задать 33 столбца, и все получилось. Почему?

Вот как я это делал.

create table Test(x1 number(2) ,x2 number(2) ,x3 number(2) ,x4 number(2) ,x5 
number(2) ,x6 number(2) ,x7 number(2) ,x8 number(2) ,x9 number(2) ,x10 
number(2),x11 number(2),x12 number(2),x13 number(2),x14 number(2),x15 
number(2),x16 number(2),x17 number(2),x18 number(2),x19 number(2),x20 
number(2),x21 number(2),x22 number(2),x23 number(2),x24 number(2),x25 
number(2),x26 number(2),x27 number(2),x28 number(2),x29 number(2),x30 
number(2),x31 number(2),x32 number(2),x33 number(2),x34 number(2),x35 
number(2),x36 number(2),x37 number(2),x38 number(2),x39 number(2),x40 number(2),
primary key( 
x1,x2,x3,x4,x5,x6,x7,x8,x9,x10,x11,x12,x13,x14,x15,x16,x17,x18,x19,x20,x21,x22,x2
3,x24,x25,x26,x27,x28,x29,x30,x31,x32,x33))
/

Ответ Тома Кайта

Похоже, предел - 33 столбца. Меня это не беспокоит, поскольку "1" - наиболее типичное количество, а 5 или 6 - разумный максимум... 32 будет "слегка перебор", а 33 - еще хуже.

Первичный ключ: sys_guid или последовательность - комментарий от 18 августа 2003 года

Мы используем sys_guid, поскольку хотим избежать конфликтов при переносе данных из одной базы в другую. Точная причина мне не известна, но при экспортировании данных из базы db1 и импортировании в другую базу данных, db2, при использовании последовательностей возможны конфликты (поскольку одинаковые последовательности создавались в обеих схемах).

Сталкивались ли вы с такой ситуацией? Нет ли более элегантного решения проблемы, кроме использования sys_guid вместо последовательностей. Мы не знаем точного количества баз и не можем просто начинать последовательности с разных значений.

Ответ Тома Кайта

Пусть имеется N баз, которые потенциально придется поддерживать. Если не уверены, увеличьте количество в 100 раз.

Потом в перовй базе выполняем:

create sequence S start with 1 increment by n*100;

Во второй:

create sequence S start with 2 increase by n*100;

Пусть n = 10, тогда в первой базе будут генерироваться числа:

1, 1001, 2001, 3001, ...

Во второй:

2, 1002, 2002, 3002, ...

Получили не перекрывающиеся последовательности для 1000 баз данных.

Не перекрывающиеся последовательности!

"не перекрывающиеся последовательности для 1000 баз данных."

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

В идеале один и тот же сценарий установки должен работать всегда, не создавая конфликтов в любой базе. Да, кроме использования централизованной таблицы с начальными значениями для последовательностей, к которой будут обращаться последовательно, - другой достойной альтернативы не видно...

Ссылки по теме