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 > > > > > > >