Первинний ключ – складової або сурогатний?, Інші СУБД, Бази даних, статті

Цей випуск присвячений “вічної” теми вибору стовпців для первинного ключа. За мотивами випадково виявленого чудового відповіді Тома Кайта на питання, задані в 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;
/

Іноді висловлюють побоювання, що при генерації послідовних номерів таким чином можливі пропуски (пов’язані з відкотом транзакції, наприклад – В.К.) …


Так, використання послідовності не гарантує відсутність пропусків при нумерації – в будь-який не однокористувальницької системі вони точно будуть. Єдиний спосіб нумерувати без пропусків – обробляти транзакції по одній. Послідовності призначалися не для цього, і будь-яка система з такою вимогою не буде масштабуватися (для комп’ютерних систем воно взагалі смішне – при обробці документів вручну ще куди не йшло, але в компьтерной системі – воно просто безглуздо).


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


Я вважаю, що складові ключі прекрасно працюють і можуть використовуватися при наявності зовнішніх ключів, але:



Ці міркування необхідно враховувати. Як я вже писав, якщо складовою первинний ключ не використовується в якості зовнішнього у багатьох таблицях – використовуйте його. В іншому випадку серйозно задумайтеся над використанням сурогатного ключа на базі послідовності (а про “пропуски” значень не думайте зовсім – важливо, що виходить унікальний ідентфікатор).


Складовою ключ в одному стовпці


Мені цікава твоя думка про те, чтро робити, якщо клієнти наполягають на використанні “магічних кодів” – складових ключів, впихнути в один стовпець.


Простий приклад – наступний ідентифікатор події:

03-40123 Формат: FY-XNNNN
де:
FY = 2 цифри фінансового року X = 1 цифра, що задає тип події NNNN = 4 цифри, які визначають порядковий номер події даного типу в цьому фінансовому році

Особисто я пиаюсь уникати такого роду ідентифікаторів (як би вони не генерувалися) як чуми, за такими, як мені здається, очевидним, причин:



Я періодично стикаюся з цією проблемою, зазвичай – при оновленні старих систем та / або систем “паперового” документообігу, користувачі яких не хочуть змінювати систему нумерації. Часто мені вдається умовити клієнта перейти на прості послідовності, але не завжди.


Я знаю, що ти не прихильник простих правил, але чи виправдовує природа даних або необхідність “запам’ятовується” ключа створення такого типу ключів? Коли ти вважаєш обгрунтованим використання такого складного ключового стовпця для ідентфікаціі даних? Додаси ти власний сурогатний ключ і дозволиш користувачам зберігати свої магічні коди де завгодно, чи будеш наполягати на використанні простої послідовності?


Відповідь Тома Кайта


Такого роду поля можуть (і повинні) бути похідним від інших даних. Клієнту немає необхідності знати, як у фізичній схемі фактично реалізований первинний ключ – це деталь реалізації.


Так що, я б зробив так:

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


Пакет працює прекрасно.


Питання:



  1. Після зміни первинного ключа, як “відв’язати” пакет від таблиці (він необхідний для інших таблиць, так що просто видалити його я не можу).
  2. Цей пакет кажестя кращим рішенням у нашому випадку, оскільки решті код міняти не доведеться. Крім зниження продуктивності, які ще недоліки має дане рішення?
  3. “Обмеження з відкладеним перевіркою”, про які ви пишете, це коли додають ключове слово 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);

Відповідь Тома Кайта


Не знаю, як ставитися до твердження, що “Пакет працює прекрасно”. Сумнівний комплімент, як на мене …



  1. Пакет не зв’язується з таблицею. Він створює ряд тригерів і збережених процедур, для зазначеної таблиці– Він генерує специфічний код. Сам по собі він ні з одним об’єктом не пов’язаний.
  2. Ви маєте на увазі крім того факту, що зміна первинного ключа взагалі недачная ідея, пов’язана з помилкою проектування?
  3. Так, перевірку зовнішніх ключів можна відкласти і робити так:
    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 замість послідовності (є вимога глобальної унікальності первинних ключів) – в будь-якому випадку, має сенс зробити тест і поділитися результатами.


Результати показали наступне:



  1. За часом роботи послідовності – швидше (я використовував опцію cache з кешем розміром 5000) – розкид значень був великий, але на різних прогонах послідовності виявилися від 52% до 90% швидше, ніж виклики sys_guid.

    Думаю, причина в кешуванні значень послідовностей в області sga, Правильно? Однак дивно, чому sys_guid працює повільніше – як внутрішньо реалізована функція sys_guid?


  2. Зрозуміло, що значення послідовності займають менше місця (приблизно в 2 рази)
  3. Однак 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


Відповідь Тома Кайта



  1. Хоча це і сказано в документації, sys_guid звертається до ОС і, безсумнівно, вимагає більше ресурсів процесора

  2. 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 баз даних.”

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


В ідеалі один і той же сценарій установки повинен працювати завжди, не створюючи конфліктів в будь-якій базі. Так, крім використання централізованої таблиці з початковими значеннями для послідовностей, до якої будуть звертатися послідовно, – іншої гідної альтернативи не видно …

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*