Index reduction

Hi Just wanted to clarify I am correct in the assumption that it is not possible to create an index that doesn't contain all rows. I am currently compressing data using the informix compression, repack, shrink tools to reduce the size of our SAP database. However, we still end up with very large indexes fragmented over multiple fragments, as the data within indexes is not compressed. Where a date field is used in the expression, most of the index is completely unused, but am I right to say it must exist. The other question is - why can't index data also be compressed, and in some cases, indexes are now 5 times the data size.

Yes, all rows must be indexed. Read the following carefully: No currently available release includes the ability to compress indexes. One thing you can do now is to make sure that the FILLFACTOR for the index is set as high as is practical to eliminate unused key slots on each index node following the build. Note that it is a bad idea to index variable length columns like VARCHAR and LVARCHAR for just the reason that index keys on these columns must include the maximum length of the column. Sometimes necessary, I know. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Nov 19, 2012 at 11:37 AM, JIM GODDEN <jim.godden@orange.co.uk>wrote: > Hi > > Just wanted to clarify I am correct in the assumption that it is not > possible > to create an index that doesn't contain all rows. I am currently > compressing > data using the informix compression, repack, shrink tools to reduce the > size > of our SAP database. However, we still end up with very large indexes > fragmented over multiple fragments, as the data within indexes is not > compressed. Where a date field is used in the expression, most of the > index is > completely unused, but am I right to say it must exist. > > The other question is - why can't index data also be compressed, and in > some > cases, indexes are now 5 times the data size. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec517cbe01f879604cedc5ab7

Jim, I would say your assumption is correct regaring missing rows.. other than to say if it did contain missing rows it would be known as a corrupted index. that said, Informix does not compress index pages in the sense it compresses data pages, it instead merges together near empty index pages/nodes together depending on the configuration of the btree scanner compression level to reduce I/O. As for the 5/1 to ration you obsevered not suprised there as it looks like your getting a decent compression ratio. I supported SAP customers for many years and SAP is synomous with wide indexes that are known to flood the buffers at times. The btree scanner index compression may help in reducing that ; check SAP notes for anything that may have been recently authored for guidance and the Informix online documentation for hints. Mark