INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10

When adding a Foreign Key constraint from a child with interval
fragmentation to a parent with interval fragmentation with all
required supporting indexes in place the required time to
complete will increase exponentially as opposed to linearly when
the number of keys to validate increases.

The following test Case demonstrates this:

create table parent (parent_pk serial8)
fragment by range (parent_pk) interval (50000)
store in (dbspace2k_1, dbspace2k_2, dbspace2k_3, dbspace2k_4)
partition parent_p0 values < 50000 in dbspace2k_3
extent size 512 next size 128 lock mode row statlevel fragment;

create unique index parent_pk_idx on parent(parent_pk);
alter table parent add constraint (primary key (parent_pk));

create table child (child_pk serial8, parent_pk int8)
fragment by range (parent_pk) interval (50000)
in (dbspace2k_1, dbspace2k_2, dbspace2k_3, dbspace2k_4)
partition child_p0 values < 50000 in dbspace2k_4
extent size 512 next size 128 lock mode row statlevel fragment;
create unique index child_pk_idx on child(child_pk) in rootdbs;
create index child_parent_pk_idx on child(parent_pk);

create procedure pop_parent()
define i int;
let i=1;
while i < 250000
        insert into parent values (i);
        let i = i + 1;
end while
end procedure;

execute procedure pop_parent();

create procedure pop_child()
define i int;
let i=1;
while i < 250000
        insert into child values (i,i);
        let i = i + 1;
end while
end procedure;

execute procedure pop_child();
select current from sysmaster:sysdual;
alter table child add constraint (foreign key (parent_pk)
references parent constraint child_parent_pk);
select current from sysmaster:sysdual;

The run time duration increases exponentially (VPCLASS 4, 2k
BUFFERS 10000):

Number of
PARENT
CHILD
Rows           non-PDQ   PDQ
250,000           4.3S   1.4S
500,000          17.4S   5.4S
750,000          40.0S  11.0S
1,000,000        70.0S  19.0S
2,000,000       314.0S  90.0S

****************************************************************
* USERS AFFECTED:                                              *
* Users of Informix Server prior to 12.10.xC15 and 14.10.xC6.  *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to Informix Server 12.10.xC15 (when available) or    *
* 14.10.xC6 (when available).                                  *
****************************************************************

****************************************************************
* USERS AFFECTED:                                              *
* Users of Informix Server prior to 12.10.xC15 and 14.10.xC6.  *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to Informix Server 12.10.xC15 (when available) or    *
* 14.10.xC6 (when available).                                  *
****************************************************************

Fixed in Informix Server 12.10.xC15 and 14.10.xC6.

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

10.12.2020
13.05.2021
13.05.2021