Informix - Problem description
Problem IT35229 | Status: Closed |
ALTER TABLE ADD FOREIGN KEY CONSTRAINT GOES EXPONENTIALLY SLOWERWITH INTERVAL FRAGMENTATION | |
product: | |
INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10 | |
Problem description: | |
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 | |
Problem Summary: | |
**************************************************************** * 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). * **************************************************************** | |
Local Fix: | |
Solution | |
Workaround | |
**************************************************************** * 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). * **************************************************************** | |
Comment | |
Fixed in Informix Server 12.10.xC15 and 14.10.xC6. | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 10.12.2020 13.05.2021 13.05.2021 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |