Different Page Size of Primary Key

Hi. From previous question I have a table: CREATE TABLE informix.order_bulk ( orderdate DATE NOT NULL, ordernumber INTEGER NOT NULL, ... (other columns) primary key(orderdate, orderno) ) FRAGMENT BY RANGE(orderdate) interval(1 units year) store in (orderdbs03, orderdbs04)partition prv_partition values < date('2010-01-01') in orderdbs03 EXTENT SIZE 4194219 NEXT SIZE 4194219 LOCK MODE ROW; I create orderdbs03 and orderdbs04 using non default page size in 16 Kb. The default page size is 2 Kb. The table has primary key of orderdate and orderno column. When I see the table info via dbaccess in the Fragments session, it shows that the table is created in orderdbs03 and orderdbs04 as partitions, and the index of the primary key in other dbspace. It is in datadbs which is the first dbspace after rootdbs which has default pagesize (2k). I've tried to create the table first then the key later but still resulting the same fragments. The question is: 1. Is there any performance issue with different page size between table and index? 2. Is there a way to move the index to another dbspace? The index is not accessible via SQL command because it seems it has invinsible character in front of index name. ex: ' 160_722' Thank you.

OK, if you do NOT include the PRIMARY KEY specification in the create table, but then create a UNIQUE index on the primary key columns after creating the table fragmented the way you want in the dbspaces you want. Then use ALTER TABLE ADD CONSTRAINT PRIMARY KEY... to create the primary key constraint, it will use the UNIQUE index you created manually with its own IN or FRAGMENT BY clause and you can place the index where ever you want to. Indexes can have different page sizing than tables, no problem. However, note that indexes work best in wider pages, even 16K. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Feb 27, 2013 at 6:13 AM, MOHAMMAD IRFAN <irfan199@yahoo.com> wrote: > Hi. From previous question I have a table: > > CREATE TABLE informix.order_bulk ( > orderdate DATE NOT NULL, > ordernumber INTEGER NOT NULL, > .... (other columns) > primary key(orderdate, orderno) > ) > FRAGMENT BY RANGE(orderdate) interval(1 units year) store in (orderdbs03, > orderdbs04)> partition prv_partition > values < date('2010-01-01') in orderdbs03 > EXTENT SIZE 4194219 NEXT SIZE 4194219 > LOCK MODE ROW; > > I create orderdbs03 and orderdbs04 using non default page size in 16 Kb. > The > default page size is 2 Kb. > The table has primary key of orderdate and orderno column. > > When I see the table info via dbaccess in the Fragments session, it shows > that > the table is created in orderdbs03 and orderdbs04 as partitions, and the > index > of the primary key in other dbspace. It is in datadbs which is the first > dbspace after rootdbs which has default pagesize (2k). > > I've tried to create the table first then the key later but still resulting > the same fragments. The question is: > 1. Is there any performance issue with different page size between table > and > index? > 2. Is there a way to move the index to another dbspace? The index is not > accessible via SQL command because it seems it has invinsible character in > front of index name. ex: ' 160_722' > > Thank you. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f22beb9bb8ab204d6b45664