Автор: Никифоров Дмитрий
Тема: Структуры, участвующие в поддержке deferrable constraints
Источник: Russian Oracle User Group
Номер документа:4.1
Дата публикации: 31.05.2009
Последнее изменение: 31.05.2009


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

SQL> alter table parent_t add constraint parent_t_pk primary key (x);

Table altered.

SQL> alter table child_t add constraint child_t_fk
2 foreign key (y) references parent_t (x) deferrable;

Table altered.

SQL> set constraint all deferred;

Constraint set.

SQL> insert into child_t values(1,1);

1 row created.

SQL> declare
  2   pragma autonomous_transaction;
  3  begin
  4   insert into parent_t values(1);
  5   commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (SCOTT.CHILD_T_FK) violated - parent key not
found

Некоторое недоумение авторов кода понятно. Действительно, если обратиться к документации [1], то можно увидеть, каким образом Oracle трактует deferrable constraints:

You can defer checking constraints for validity until the end of the transaction.
· A constraint is deferred if the system checks that it is satisfied only on commit.
If a deferred constraint is violated, then commit causes the transaction to undo.
· If a constraint is immediate (not deferred), then it is checked at the end of each statement.
If it is violated, the statement is rolled back immediately.

В данном случае документация говорит нам, что deferrable constraints в режиме deferred проверяются на момент фиксации транзакции. Но если понимать эту фразу буквально, то совершенно неясно с концептуальной точки зрения, почему вышеприведенный код генерирует ошибку. Ведь на момент фиксации транзакции данные являются согласованными. При этом внесение родительской записи в той же транзакции вместо автономного блока решает проблему. Более того, если мы затронем простейшим оператором UPDATE поля foreign key в ранее вставленных записях таблицы CHILD_T, то транзакция закончится успешно:

SQL> set constraint all deferred;

Constraint set.

SQL> insert into child_t values(1,1);

1 row created.

SQL> declare
  2   pragma autonomous_transaction;
  3  begin
  4   insert into parent_t values(1);
  5   commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> update child_t set y = y where x = 1;

1 row updated.

SQL> commit;

Commit complete.

Все это наводит на некоторую гипотезу, которую можно сформулировать следующим образом:

a) для поддержки отложенных ограничений внешнего ключа транзакция использует некоторую диагностическую информацию, которую хранит до момента фиксации

b) данная диагностическая информация является transaction-specific и недоступна для изменения другим транзакциям (иначе проблем с автономной транзакцией не возникало бы)

c) данная диагностическая информация не связана с конкретной записью таблицы, иначе транзакция имела бы возможность в момент фиксации проверить фактическое соответствие “проблемной” записи требованиям ограничения

Рассуждая далее, можно предположить, что хранение такой диагностики в оперативной памяти может потенциально представлять некоторые проблемы, связанные с исчерпанием PGA. Поэтому было решено проверить, не проявится ли такой гипотетический метод поддержки deferred constraints в виде использования транзакцией временного сегмента. Простой эксперимент показал, что по достижении определенного порогового значения количества записей, нарушающих ограничение целостности (которое не будет обсуждаться в данной статье) можно наблюдать появление временного сегмента, связанного с текущей транзакцией (все дальнейшие выкладки приводятся для версии Oracle Database 10g Enterprise Edition Release 10.2.0.3.0) :

SQL> set constraint all deferred
  2  /

Constraint set.

SQL> insert into child_t select rownum, mod(rownum,4) from dual connect by level <= 10000;

10000 rows created.

SQL> select x.segtype, x.extents, x.blocks, x.segfile#, x.segblk#, x.segrfno#
  2  from v$tempseg_usage x, v$session y
  3  where x.session_addr = y.saddr and y.sid = USERENV('SID')
  4  /

SEGTYPE      EXTENTS     BLOCKS   SEGFILE#    SEGBLK#   SEGRFNO#
--------- ---------- ---------- ---------- ---------- ----------
INDEX              1        128        202    255753           2

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

SQL> alter system dump tempfile 2 block min 255753 block max 255760;

System altered.

Start dump data blocks tsn: 3 file#: 2 minblk 255753 maxblk 255880
buffer tsn: 3 rdba: 0x0083e709 (2/255753)
scn: 0x0875.7bd4b765 seq: 0x00 flg: 0x08 tail: 0xb7651000
frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 127
                  last map  0x00000000  #maps: 0      offset: 4128
      Highwater::  0x0083e70b  ext#: 0      blk#: 1      ext size: 127
  #blocks in seg. hdr's freelists: 0
  #blocks below: 1
  mapblk  0x00000000  offset: 0
                   Unlocked
     Map Header:: next  0x00000000  #extents: 1    obj#: 8644361 flag: 0x40000000
  Extent Map
  -----------------------------------------------------------------
   0x0083e70a  length: 127

  nfl = 1, nfb = 1 typ = 2 nxf = 0 ccnt = 0
  SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000 

buffer tsn: 3 rdba: 0x0083e70a (2/255754)
scn: 0x0875.7bd4b7d3 seq: 0x00 flg: 0x08 tail: 0xb7d30600
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump:  0x0083e70a
 Object id on Block? Y
 seg/obj: 0x83e709  csc: 0x875.7bd4b764  itc: 2  flg: -  typ: 2 - INDEX
     fsl: 0  fnx: 0x0 ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x0006.02b.000050aa  0x00873a96.165c.02  ----    4  fsc 0x0000.00000000

Leaf block dump
===============
header address 242391644=0xe729a5c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 4
kdxcofbo 44=0x2c
kdxcofeo 7997=0x1f3d
kdxcoavs 7953
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 4
kdxlebksz 8032
row#0[8024] flag: ------, lock: 2, len=8, data:(4):  c4 09 00 00
col 0; len 1; (1):  80
row#1[8015] flag: ------, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 02
row#2[8006] flag: ------, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 03
row#3[7997] flag: ------, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 04

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

Давайте удалим все записи в таблице CHILD_T со значением поля Y = 1.

SQL> delete from child_t where y = 1;

2500 rows deleted.

В результате дамп будет содержать следующую информацию:

row#0[8024] flag: ------, lock: 2, len=8, data:(4):  c4 09 00 00
col 0; len 1; (1):  80
row#1[8015] flag: ---D--, lock: 2, len=9, data:(4):  01 00 00 00
col 0; len 2; (2):  c1 02
row#2[8006] flag: ------, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 03
row#3[7997] flag: ------, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 04

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

Теперь в той же транзакции внесем родительскую запись со значением первичного ключа = 2:

SQL> insert into parent_t values(2);

1 row created.

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

row#0[8024] flag: ------, lock: 2, len=8, data:(4):  c4 09 00 00
col 0; len 1; (1):  80
row#1[8015] flag: ---D--, lock: 2, len=9, data:(4):  01 00 00 00
col 0; len 2; (2):  c1 02
row#2[8006] flag: ---D--, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 03
row#3[7997] flag: ------, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 04

Внесение родительской записи в автономном PL/SQL-блоке (для Y = 3), как и следовало ожидать, никак не влияет на содержимое индексного блока:

SQL> declare
  2   pragma autonomous_transaction;
  3  begin
  4   insert into parent_t values(0);
  5   commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

row#0[8024] flag: ------, lock: 2, len=8, data:(4):  c4 09 00 00
col 0; len 1; (1):  80
row#1[8015] flag: ---D--, lock: 2, len=9, data:(4):  01 00 00 00
col 0; len 2; (2):  c1 02
row#2[8006] flag: ---D--, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 03
row#3[7997] flag: ------, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 04

И в качестве последней операции выполним оператор UPDATE, затрагивающий поле foreign-ключа:

SQL> update child_t set y = y where y = 0;

2500 rows updated.

Результат уже вполне предсказуем – индексная запись отмечена как удаленная, поле data изменилось:

row#0[8024] flag: ---D--, lock: 2, len=8, data:(4):  01 00 00 00
col 0; len 1; (1):  80
row#1[8015] flag: ---D--, lock: 2, len=9, data:(4):  01 00 00 00
col 0; len 2; (2):  c1 02
row#2[8006] flag: ---D--, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 03
row#3[7997] flag: ------, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 04

Основываясь на полученных результатах, логично предположить, что в записях индекса хранятся пары “значение внешнего ключа – количество записей, нарушающих ограничение” для конкретного foreign key. Попробуем провести простой эксперимент, чтобы в этом убедиться.

Выполним в пустую таблицу CHILD_T ряд операций вставки, нарушающих ограничение внешнего ключа (попутно необходимо отметить, что число 257 было выбрано не случайно – оно связано со значением упоминавшейся выше границы количества “неудачных” операций по конкретному constraint – 256, после которого "проявляет себя" индексная структура во временном сегменте).

SQL> begin
2 execute immediate 'set constraint all deferred';
3 for i in 1..257 loop
4 insert into child_t values(i, mod(i,4));
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

Получим данные о количестве записей в таблице, соответствующих каждому значению ключа. Выведем количество в hexadecimal – формате:

SQL> select y, to_char(count(*),'XXXX') hex
  2  from child_t group by y
  3  order by 1
  4  /

         Y        HEX
---------- ----------
         0         40
         1         41
         2         40
         3         40

Дамп индексного блока временного сегмента подтверждает предположения:

row#0[8024] flag: ------, lock: 0, len=8, data:(4):  40 00 00 00
col 0; len 1; (1):  80
row#1[8015] flag: ------, lock: 0, len=9, data:(4):  41 00 00 00
col 0; len 2; (2):  c1 02
row#2[8006] flag: ------, lock: 0, len=9, data:(4):  40 00 00 00
col 0; len 2; (2):  c1 03
row#3[7997] flag: ------, lock: 0, len=9, data:(4):  40 00 00 00
col 0; len 2; (2):  c1 04

Удалим ряд записей для одного из значений ключа:

SQL> delete from child_t where y = 1 and x <= 127;

32 rows deleted.

SQL> select y, to_char(count(*),'XXXX') hex
  2  from child_t group by y
  3  order by 1
  4  /

         Y        HEX
---------- ----------
         0         40
         1         21
         2         40
         3         40

Вполне ожидаемый результат дампа:

row#0[8024] flag: ------, lock: 0, len=8, data:(4):  40 00 00 00
col 0; len 1; (1):  80
row#1[8015] flag: ------, lock: 2, len=9, data:(4):  21 00 00 00
col 0; len 2; (2):  c1 02
row#2[8006] flag: ------, lock: 0, len=9, data:(4):  40 00 00 00
col 0; len 2; (2):  c1 03
row#3[7997] flag: ------, lock: 0, len=9, data:(4):  40 00 00 00
col 0; len 2; (2):  c1 04

Теперь вставим в родительскую таблицу запись со значением первичного ключа, равным 2:

SQL> insert into parent_t values(2);

1 row created.

Как и можно было ожидать, запись в индексном блоке помечена как удаленная:

row#0[8024] flag: ------, lock: 0, len=8, data:(4):  40 00 00 00
col 0; len 1; (1):  80
row#1[8015] flag: ------, lock: 2, len=9, data:(4):  21 00 00 00
col 0; len 2; (2):  c1 02
row#2[8006] flag: ---D--, lock: 2, len=9, data:(4):  40 00 00 00
col 0; len 2; (2):  c1 03
row#3[7997] flag: ------, lock: 0, len=9, data:(4):  40 00 00 00
col 0; len 2; (2):  c1 04

Удалим несколько записей из таблицы CHILD_T, у которых Y=2:

SQL> delete from child_t where x<=127 and y = 2;

32 rows deleted.

SQL> select y, to_char(count(*),'XXXX') hex
  2  from child_t group by y
  3  order by 1
  4  /

         Y        HEX
---------- ----------
         0         40
         1         21
         2         20
         3         40

Наша операция никак не повлияла на значение data в удаленной записи:

row#0[8024] flag: ------, lock: 0, len=8, data:(4):  40 00 00 00
col 0; len 1; (1):  80
row#1[8015] flag: ------, lock: 2, len=9, data:(4):  21 00 00 00
col 0; len 2; (2):  c1 02
row#2[8006] flag: ---D--, lock: 2, len=9, data:(4):  40 00 00 00
col 0; len 2; (2):  c1 03
row#3[7997] flag: ------, lock: 0, len=9, data:(4):  40 00 00 00
col 0; len 2; (2):  c1 04

Удалим родительскую запись с X = 2 из таблицы PARENT_T:

SQL> delete from parent_t where x = 2;

1 row deleted.

Убеждаемся, что Oracle привел соответствующую пару “ключ – количество” в актуальное состояние:

row#0[8024] flag: ------, lock: 0, len=8, data:(4):  40 00 00 00
col 0; len 1; (1):  80
row#1[8015] flag: ------, lock: 2, len=9, data:(4):  21 00 00 00
col 0; len 2; (2):  c1 02
row#2[8006] flag: ------, lock: 2, len=9, data:(4):  20 00 00 00
col 0; len 2; (2):  c1 03
row#3[7997] flag: ------, lock: 0, len=9, data:(4):  40 00 00 00
col 0; len 2; (2):  c1 04

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

SQL> create table child_2_t (y int, z int);

Table created.

SQL> alter table child_2_t add constraint child_2_t_fky
  2  foreign key (y) references parent_t (x) deferrable
  3  add constraint child_2_t_fkz foreign key(z)
  4  references parent_t(x) deferrable
  5  /

Table altered.

SQL> set constraint all deferred
  2  /

Constraint set.

SQL> insert into parent_t values(1)
  2  /

1 row created.

SQL> insert into child_2_t select dbms_random.random, 1
  2  from dual connect by level <= 512
  3  /

512 rows created.

SQL> select x.segtype, x.extents, x.blocks, x.segfile#, x.segblk#, x.segrfno#
  2  from v$tempseg_usage x, v$session y
  3  where x.session_addr = y.saddr and y.sid = USERENV('SID')
  4  /

SEGTYPE      EXTENTS     BLOCKS   SEGFILE#    SEGBLK#   SEGRFNO#
--------- ---------- ---------- ---------- ---------- ----------
INDEX              1        128        202    255753           2

SQL> insert into child_2_t select dbms_random.random, dbms_random.random
  2  from dual connect by level <= 512
  3  /

512 rows created.

SQL> select x.segtype, x.extents, x.blocks, x.segfile#, x.segblk#, x.segrfno#
  2  from v$tempseg_usage x, v$session y
  3  where x.session_addr = y.saddr and y.sid = USERENV('SID')
  4  /

SEGTYPE      EXTENTS     BLOCKS   SEGFILE#    SEGBLK#   SEGRFNO#
--------- ---------- ---------- ---------- ---------- ----------
INDEX              1        128        202    255753           2
INDEX              1        128        202    255625           2

Вставив достаточное количество записей в таблицы, можно убедиться в том, что в индексной структуре появились branch-блоки:

Branch block dump
=================
header address 221379140=0xd31fa44
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 1
kdxconro 7
kdxcofbo 42=0x2a
kdxcofeo 8002=0x1f42
kdxcoavs 7960
kdxbrlmc 8644235=0x83e68b
kdxbrsno 6
kdxbrbksz 8056
kdxbr2urrc 0
row#0[8026] dba: 8644239=0x83e68f
col 0; len 2; (2):  3a 56
row#1[8041] dba: 8644237=0x83e68d
col 0; len 3; (3):  3a 5b 49
row#2[8010] dba: 8644241=0x83e691
col 0; len 3; (3):  3a 61 19
row#3[8049] dba: 8644236=0x83e68c
col 0; len 2; (2):  c4 5a
row#4[8018] dba: 8644240=0x83e690
col 0; len 3; (3):  c5 07 49
row#5[8033] dba: 8644238=0x83e68e
col 0; len 3; (3):  c5 0c 45
row#6[8002] dba: 8644242=0x83e692
col 0; len 3; (3):  c5 11 50

Таким образом, можно сделать обоснованный вывод, что Oracle использует для поддержания отложенных ограничений внешнего ключа индексоподобные структуры, в которых хранятся пары “значение ключа – количество записей, нарушающих ограничение”. Данные структуры являются transaction-specific. Oracle поддерживает эту информацию в актуальном состоянии во время выполнения операций DML. По завершении транзакции или при выполнении попытки перевода constraints в состояние IMMEDIATE выполняется проверка содержимого соответствующих структур на предмет наличия актуальных записей.

Если провести аналогичный тест для отлагаемого ограничения первичного ключа, то можно увидеть похожую индексную структуру:

SQL> create table t_pkd (x int,
  2  constraint t_pkd_pk primary key (x) deferrable)
  3  /

Table created.

SQL> set constraint all deferred
  2  /

Constraint set.

SQL> insert into t_pkd select 1 from dual connect by level <= 512;

512 rows created.

SQL> select x.segtype, x.extents, x.blocks, x.segfile#, x.segblk#, x.segrfno#
  2  from v$tempseg_usage x, v$session y
  3  where x.session_addr = y.saddr and y.sid = USERENV('SID')
4  /

SEGTYPE      EXTENTS     BLOCKS   SEGFILE#    SEGBLK#   SEGRFNO#
--------- ---------- ---------- ---------- ---------- ----------
INDEX              1        128        202    255753           2

Дамп блока индекса:

Leaf block dump
===============
header address 228563548=0xd9f9a5c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 1
kdxcofbo 38=0x26
kdxcofeo 8021=0x1f55
kdxcoavs 7983
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 4
kdxlebksz 8032
row#0[8021] flag: ------, lock: 2, len=11, data:(4):  ff 01 00 00
col 0; len 4; (4):  ca 40 01 00

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


SQL> set constraint all deferred
  2  /

Constraint set.

SQL> insert into t_pkd select x from
  2  (select dbms_random.random x from dual connect by level <= 100000),
  3  (select rownum y from dual connect by level <= 2)
  4  /

200000 rows created.

мы обнаружим, что единственным изменением является увеличение значения поля data:

Leaf block dump
===============
header address 230345308=0xdbaca5c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 1
kdxcofbo 38=0x26
kdxcofeo 8021=0x1f55
kdxcoavs 7983
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 4
kdxlebksz 8032
row#0[8021] flag: ------, lock: 2, len=11, data:(4):  a2 86 01 00
col 0; len 4; (4):  ca 40 01 00

В данном случае это общее количество записей, нарушающих ограничение первичного ключа:

SQL> select to_number('0186a2','XXXXXX') from dual;

TO_NUMBER('0186A2','XXXXXX')
----------------------------
                      100002

SQL> select count(*) - count(unique x) from t_pkd;

COUNT(*)-COUNT(UNIQUEX)
-----------------------
                 100002

Подобная разница в содержимом структур может объясняться тем, что для проверки нарушений первичного ключа Oracle может использовать индексную структуру, всегда имеющуюся в наличии. Поэтому в диагностической области достаточно вести учет количества “проблемных” записей без привязки к значению ключа. В то же время внешний ключ не обязан быть индексированным. Поэтому если в родительской таблице удаляется запись, то Oracle в общем случае вынужден будет проверить наличие “проблемных” записей в родительской таблице путем ее сканирования, чтобы поддержать в актуальном состоянии диагностическую информацию. А так как для данного значения ключа дочерние записи могут отсутствовать, с целью сокращения времени анализа Oracle может проверить наличие соответствующей записи для данного ключа в диагностической области, и, если такая запись отсутствует, или удалена, не выполнять поиск в таблице.

Интересен также вопрос – что содержится во втором поле записи? Если перевести соответствующее hex-значение в десятичное, то можно обнаружить, что это число совпадает с OBJECT_ID соответствующего индекса:

SQL> select object_id, data_object_id, object_name, object_type
  2  from dba_objects where object_name = 'T_PKD_PK';

 OBJECT_ID DATA_OBJECT_ID OBJECT_NAME                    OBJECT_TYPE
---------- -------------- ------------------------------ -------------------
     82122          82122 T_PKD_PK                       INDEX

SQL> select to_number('0140ca','XXXXXX') from dual;

TO_NUMBER('0140CA','XXXXXX')
----------------------------
                       82122

То, что это именно OBJECT_ID, а не DATA_OBJECT_ID можно убедиться:

SQL> truncate table t_pkd;

Table truncated.

SQL> set constraint all deferred;

Constraint set.

SQL> insert into t_pkd select x from
  2  (select dbms_random.random x from dual connect by level <= 100000),
  3  (select rownum y from dual connect by level <= 2)
  4  /

200000 rows created.

SQL> select x.segtype, x.extents, x.blocks, x.segfile#, x.segblk#, x.segrfno#
  2  from v$tempseg_usage x, v$session y
  3  where x.session_addr = y.saddr and y.sid = USERENV('SID')
  4  /

SEGTYPE      EXTENTS     BLOCKS   SEGFILE#    SEGBLK#   SEGRFNO#
--------- ---------- ---------- ---------- ---------- ----------
INDEX              1        128        202     255753          2

SQL> select object_id, data_object_id, object_name, object_type
  2  from dba_objects where object_name = 'T_PKD_PK';

 OBJECT_ID DATA_OBJECT_ID OBJECT_NAME                    OBJECT_TYPE
---------- -------------- ------------------------------ -------------------
     82122          82125 T_PKD_PK                       INDEX

Leaf block dump
===============
header address 228813404=0xda36a5c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 1
kdxcofbo 38=0x26
kdxcofeo 8021=0x1f55
kdxcoavs 7983
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 4
kdxlebksz 8032
row#0[8021] flag: ------, lock: 2, len=11, data:(4):  a1 86 01 00
col 0; len 4; (4):  ca 40 01 00

SQL> select count(*) - count(unique x) from t_pkd;

COUNT(*)-COUNT(UNIQUEX)
-----------------------
                 100001

SQL> select to_number('0186a1','XXXXXX') from dual;

TO_NUMBER('0186A1','XXXXXX')
----------------------------
                      100001

Проведем соответствующие тесты для СHECK CONSTRAINT:

SQL> create table t_chkd (x int,
  2  constraint t_chkd_chk
  3  check (x < 0)
  4  deferrable initially deferred);

Table created.

SQL> insert into t_chkd select rownum from dual connect by level <= 257;

257 rows created.

SQL> select x.segtype, x.extents, x.blocks, x.segfile#, x.segblk#, x.segrfno#
  2  from v$tempseg_usage x, v$session y
  3  where x.session_addr = y.saddr and y.sid = USERENV('SID')
  4  /

SEGTYPE      EXTENTS     BLOCKS   SEGFILE#    SEGBLK#   SEGRFNO#
--------- ---------- ---------- ---------- ---------- ----------
INDEX              1        128        202     255625          2

Leaf block dump
===============
header address 212216412=0xca62a5c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 257
kdxcofbo 550=0x226
kdxcofeo 4691=0x1253
kdxcoavs 4141
kdxlespl 0
kdxlende 10
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8019] flag: ------, lock: 0, len=13
col 0; len 10; (10):  00 01 40 cf 03 47 b3 67 00 00
row#1[8006] flag: ------, lock: 0, len=13
col 0; len 10; (10):  00 01 40 cf 03 47 b3 67 00 01
row#2[7993] flag: ------, lock: 0, len=13
col 0; len 10; (10):  00 01 40 cf 03 47 b3 67 00 02
row#3[7980] flag: ------, lock: 0, len=13
col 0; len 10; (10):  00 01 40 cf 03 47 b3 67 00 03
row#4[7967] flag: ------, lock: 0, len=13
col 0; len 10; (10):  00 01 40 cf 03 47 b3 67 00 04
row#5[7954] flag: ------, lock: 0, len=13
col 0; len 10; (10):  00 01 40 cf 03 47 b3 67 00 05
row#6[7941] flag: ------, lock: 0, len=13
col 0; len 10; (10):  00 01 40 cf 03 47 b3 67 00 06
row#7[7928] flag: ------, lock: 0, len=13
col 0; len 10; (10):  00 01 40 cf 03 47 b3 67 00 07
row#8[7915] flag: ------, lock: 0, len=13
col 0; len 10; (10):  00 01 40 cf 03 47 b3 67 00 08
row#9[7902] flag: ------, lock: 0, len=13
col 0; len 10; (10):  00 01 40 cf 03 47 b3 67 00 09
row#10[7889] flag: ------, lock: 0, len=13
col 0; len 10; (10):  00 01 40 cf 03 47 b3 67 00 0a

Похоже, что в этом случае Oracle поддерживает индексную структуру, содержащую ROWID проблемных записей. Можно убедиться в том, что записи содержат именно ROWID:

SQL> select dump(rowid,16) from t_chkd where x = 11;

DUMP(ROWID,16)
--------------------------------------------------------------------------------
Typ=69 Len=10: 0,1,40,cf,3,47,b3,67,0,a

SQL> delete from t_chkd where x = 11;

1 row deleted.

row#0[8019] flag: ------, lock: 0, len=13
col 0; len 10; (10):  00 01 40 cf 03 47 b3 67 00 00
row#1[8006] flag: ------, lock: 0, len=13
col 0; len 10; (10):  00 01 40 cf 03 47 b3 67 00 01
row#2[7993] flag: ------, lock: 0, len=13
col 0; len 10; (10):  00 01 40 cf 03 47 b3 67 00 02
row#3[7980] flag: ------, lock: 0, len=13
col 0; len 10; (10):  00 01 40 cf 03 47 b3 67 00 03
row#4[7967] flag: ------, lock: 0, len=13
col 0; len 10; (10):  00 01 40 cf 03 47 b3 67 00 04
row#5[7954] flag: ------, lock: 0, len=13
col 0; len 10; (10):  00 01 40 cf 03 47 b3 67 00 05
row#6[7941] flag: ------, lock: 0, len=13
col 0; len 10; (10):  00 01 40 cf 03 47 b3 67 00 06
row#7[7928] flag: ------, lock: 0, len=13
col 0; len 10; (10):  00 01 40 cf 03 47 b3 67 00 07
row#8[7915] flag: ------, lock: 0, len=13
col 0; len 10; (10):  00 01 40 cf 03 47 b3 67 00 08
row#9[7902] flag: ------, lock: 0, len=13
col 0; len 10; (10):  00 01 40 cf 03 47 b3 67 00 09
row#10[7889] flag: ---D--, lock: 2, len=13
col 0; len 10; (10):  00 01 40 cf 03 47 b3 67 00 0a

Зачем понадобилась такая структура ? Возможная причина состоит в том, что для поддержки CHECK индекс не используется, а кроме того, при выполнении DELETE или UPDATE Oracle не проверяет соответствие уже имеющегося содержимого записи ограничениям CHECK. Поэтому Oracle сохраняет ROWID проблемной записи в соответствующей структуре на случай ее последующего удаления или изменения.


Информация по теме: Oracle Database Concepts. 21. Data Integrity