Автор: Никифоров Дмитрий
Тема: Структуры, участвующие в поддержке 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 проблемной записи в соответствующей структуре на случай ее последующего удаления или изменения.