Этот выпуск посвящен "вечной" теме выбора столбцов для первичного ключа. По мотивам случайно обнаруженного замечательного ответа Тома Кайта на вопросы, заданные в 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 баз данных."
Я находил это решение на сайте. Оно очень элегантно, но надо заранее знать количество баз. В нашем случае требовалось, чтобы можно было наполнять данными любую локальную схему, а затем путем экспорта/импорта добавлять накопленные данные в центральную базу.
В идеале один и тот же сценарий установки должен работать всегда, не создавая конфликтов в любой базе. Да, кроме использования централизованной таблицы с начальными значениями для последовательностей, к которой будут обращаться последовательно, - другой достойной альтернативы не видно...