RE: Fragmentation questions involving Primary Keys and unique indexes

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 Zev Berezin wrote: > 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. 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 > 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 > > > > > > >