2436357 - Identity values are not assigned to identity column in the expected order - SAP IQ

• Identity values are not assigned to an identity column in the order of primary key
   
• If a table with the identity column is loaded with data from another table using INSERT…SELECT…ORDER BY <pkey_columns> : 

CREATE TABLE mytable (<list of all columns>, seq int IDENTITY);
CREATE UNIQUE INDEX ui ON mytbale(<pkey_columns>); 

INSERT mytable (<list of all columns>)
SELECT <list of all columns>
FROM feeder_table
ORDER BY <pkey_columns>;

• A simple query using 'ORDER BY <pkey_columns>':
   
       select * from mytable ORDER BY <pkey_columns>; 
   
  shows that the identity column values are not assigned to the seg column in the order of the primary key by the above INSERT statement.
   
• Instead, the indentity values are assigned to the seq column in blocks of multiples of 100 to various blocks of rows sorted by the primary key.
  For example, the identity column value jumps from 400 to 501, from 700 to 801, from 900 to 1001 and this goes on:

pkey1    pkey2                    ....  seq   
-------- ----------------         ....  -----------
00000001      Aug  1 2006 000     ....          1 
00000001      Sep 18 2006 000     ....          2 
00000001      Feb 16 2007 000     ....          3 
00000001      Jul 13 2007 000     ....          4 
...                               ....            
00000024      Jul 13 2007 000     ....        398 
00000024      Sep 14 2007 000     ....        399 
00000024      Sep 28 2007 000     ....        400 
00000024      Apr 25 2008 000     ....        501 
00000024      May  2 2008 000     ....        502 
...                               ....            
01000090      May  2 2008 000     ....        698 
01000090      May 30 2008 000     ....        699 
01000090      Oct 17 2008 000     ....        700 
01000090      Oct 20 2008 000     ....        801 
01000090      Oct 21 2008 000     ....        802 
...                               ....            
01000120      Mar  6 2009 000     ....        898 
01000120      Jul 20 2010 000     ....        899 
01000120      Jul 23 2010 000     ....        900 
01000120      Jul 30 2010 000     ....       1001 
01000120      Aug  6 2010 000     ....       1002 
01000120      Dec  9 2011 000     ....       1003 

• There are no gaps in identity values – all values are used up. However, they are not assigned in the order of the primary key.

SAP IQ 16.0 SP11

parallelism, thread, "GROUP BY", max_query_parallelism, identity_insert , KBA , BC-SYB-IQ , Sybase IQ , Problem