Re: detached index vs attached index

> >Greeting... > > > >Can anyone comment on detached index vs attached index? Thanks in > >advance! > > > >Andy Chang > > snip... > > All in all, I think detached indexes are a good feature in today's > environments. > Madison Pruet I agree with Madison's points. I have used detached indexes for a couple of years now. There are a couple of things that could be "gotchas" with detached indexes, though. The first involves fragmented tables and the ALTER FRAGMENT...DETACH clause. The manual states that you can not have any constraints on the table before you detach a fragment. In my experience, you also can not have detached indexes. Thus, to detach a fragment, you must drop all detached indexes, then run the ALTER FRAGMENT statement and re-create the indexes. Secondly, depending on which manual you look at, you might not be able to calculate the index size properly. In the Admin Guide, the formula specifies that you should add 9 bytes to the index entry size to calculate the number of index entries per page. No mention is made of fragmented indexes in this calculation. However, the 7.1 Performance Guide has a section on index space calculation that does show that detached indexes are handled differently. You have to add 13 bytes to the index entry size for them. I was bitten by this once, as I was calculating the space requirements for a serial key index. With a key length of 4 bytes, the difference amounts to almost 24%. I have contacted the documentation group at Informix to ask that they at least include a reference to the Perf. Guide in the section of the Admin Guide. We'll see. Mark Collins mcollins@us.dhl.com