Triggers and constraints: diagnostic mechanism, part II

The following question we can ask - what the column DATA represents in the index
structure ? Of course it's not rowid.
The most reasonable suggestion is what Oracle keeps in this index-like structure
the pairs "key - count" for the constraint. Each pair reflects the number of times the constraint voilation
has been detected for particular key value. Each DML operation executed in the current transaction
can change this information.
OK, let's check this suggestion.

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.

The dump shows:

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

Really:

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

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

Looks truth, right ?

Ok, let's remove some rows with y = 1:

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

32 rows deleted.

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

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

Dump is:

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

Now remove all rows with Y=1:


row#0[8024] flag: ------, lock: 0, len=8, data:(4): 40 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: ------, 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

As we can see Oracle doesn't set count to zero when it has to - instead,
it marks the index record as deleted.

If after that we input new record with y = 1 the key is actual again:

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): 01 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

If we input parent row for key value = 2:

SQL> insert into parent_t values(2);

1 row created.

when Oracle marks index row as deleted but doesn't change DATA value:

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): 01 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

If now we remove all rows with Y = 2

SQL> delete from child_t where y = 2;

64 rows deleted.


Oracle does nothing with index

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): 01 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

The removement of the parent record doesn't affect the index too:

SQL> delete from parent_t where x = 2;

1 row deleted.


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): 01 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

But if now we insert several child records with this key, Oracle
marks the index record as actual and resets DATA value:

SQL> insert into child_t select rownum, 2 from dual
2 connect by level <= 10;

10 rows created.

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

Y HEX
---------- ---
0 40
1 1
2 A
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): 01 00 00 00
col 0; len 2; (2): c1 02
row#2[8006] flag: ------, lock: 2, len=9, data:(4): 0a 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