RE: Fragmentation questions involving Primary Keys and unique indexes

There are restrictions on creating indexes on fragmented table: it is only possible to create a unique fragmented index if it contains the fragmentation column (columns). There are three possible approaches to creating Primary key on a fragmented table: 1. create Primary key without creating a proper index in advance. My understanding is that IDS should create a detached non-fragmented index in a default dbspace (where the database is created) 2. Manually create a non-fragmented (aka Global) unique index in a specific dbspace, then create a Primary key. Primary key should re-use that index 3. Manually create a unique index without specifying a dbspace. It that case, so-called 'Local' index (co-fragmented with a table) will be created. Note, that index MUST contain the fragmentation column. Subsequent creation of a Primary Key should re-use that index. I usually use the approach #3 -Alexey ________________________________________ From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of Zev Berezin Sent: Thursday, June 29, 2006 12:13 PM To: informix-list@iiug.org Subject: Fragmentation questions involving Primary Keys and unique indexes Hi All, Tech Specs: O/S Solaris 9 - IDS 7.31.FD7 I was playing around with fragmentation recently when I came across a couple of interesting questions. Question 1) I came across a seemingly inconsistency when fragmenting a table that contained a Primary Key using a fragment by expression clause. Oncheck showed the P.K. got fragmented across multiple dbspaces same as the table however the underlying unique index that is system created upon creation of the P.K. remained in the original dbspace and was not fragmented. This was not the case however for a P.K. that was build upon an existing unique index that I had previously created. In that scenario the unique index got fragmented along with the rest of the table. Question 2) How could in the above case an unique index get fragmented upon fragmentation of table if creating it with the Same fragment by expression clause after the table gets fragmented results in a 872 error. finderr 872 -872 Invalid fragment strategy or expression for the unique index. The round-robin method cannot fragment unique indexes. If the expression method fragments the indexes, all the columns that are used in the fragmentation expressions must also be part of the index key. Any assistance is greatly appreciated. TIA, Zev Berezin