If you don't specify a fragmentation expression, the index will follow
the table, but since the engine no longer creates attached indexes, the
index will be semi-attached, ie following the table, but with independent
extents.

Art S. Kagel

If you don't specify a fragmentation expression, the index will follow
the table, but since the engine no longer creates attached indexes, the
index will be semi-attached, ie following the table, but with independent
extents.

Art S. Kagel

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

Thank you, Art for taking the time to respond.

I do not understand your answer and it might be due to my original
question not being phrased correctly and needing clarification.

In Question 1) the P.K. had already been part of the table prior to the
fragmentation and still there was a difference if the underlying unique
index was automatically created by the engine or explicitly created by
myself prior to creating the P.K.
In either case they were created before the table was fragmented.

Zev Berezin

-----Original Message-----
From: KAGEL@bloomberg.net [mailto:KAGEL@bloomberg.net]
Sent: Thursday, June 29, 2006 2:02 PM
To: Zev Berezin
Cc: informix-list@iiug.org
Subject: Re: Fragmentation questions involving Primary Keys and unique
indexes

If you don't specify a fragmentation expression, the index will follow
the table, but since the engine no longer creates attached indexes, the
index will be semi-attached, ie following the table, but with
independent
extents.

Art S. Kagel