IT35229: ALTER TABLE ADD FOREIGN KEY CONSTRAINT GOES EXPONENTIALLY SLOWERWITH INTERVAL FRAGMENTATION

APAR status

• Closed as program error.

Error 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
  

Local fix

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).                                  *
  ****************************************************************
  

Problem conclusion

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

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  INFORMIX SERVER

• Fixed component ID

  5725A3900

Applicable component levels