Re: Detached indices in 7.3

Michael Segel wrote: > > SaTriGuy wrote: > [SNIP] > > Even so - I would not worry about detached/attached indexes. If this is > > implemented like it is in 9.x, then the indexes will be created as detached, > > but existing attached indexes will be functional. There is a performance > > benifit by having detached indexes inplace of attached indexes (i.e. data scans > > do not have to "skip" pages), and the total space required should be identical. > > > > Madison Pruet > > Are you sure that the *total* space will be the same? > I seem to recall that Tony Adduchi <sp?> gave a quick talk > on a project at Sears. He mentioned that one strange phenomonon > was that the index space was the same size as the table space. > It appears that Informix has one set of variables used to > calculate space requirements. > > Of course this was 7.2x so hopefully it was fixed. No. ODS 7.2x calculates an extent size for the index tablespace by calculating the relative size of the key versus the table row. The index extent sizes are the ratio of key length (plus index overhead) to row length times the table's own extent values. This, by the way, can cause one of the few down sides to detached indexes. Detached indexes can only have up to 64 extents, then they have to be reorged. So if the rowsize is large, the key size small, the table's next extent size is small, and the table is actively growing then a detached index sharing a dbspace with other objects can quickly run out of extents. If you are using detached indexes you must take this into account when you determine the table's extent sizes. Madison, a nice feature request would be to be able to independently specify the detached indexes' extent sizes as we do with tables and the indeed the index's dbspace. I can see the results that Sears reported if the index keys were a very large percentage of the rowsize such as in an index designed for keyonly searches.