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