Re: "Re-indexing" Informix

Hello Cesar. You should not confuse repack with extent reorganization. Repack moves rows to the begining of the table and marks pages at the end of the table as free. These pages can then be freed with shrink. What you're referring to is the extent concatenation (possibly moving them to bigger extents). B-Tree scanner already "compacts" the indexes if there are deleted items. This will prevent the index to allocate more extents. It also increases performance because with the same number of page reads you will get more index keys. And finnaly, neither of these two options will make a full "rebalance" of the index. So we're facing three types of improvements: - Reuse the alocated but deleted entries in the index (witch compacts the keys in the already allocated space): This is done by the b-tree scanner and could in some degree be compared to the repack task - Join small extents into bigger extents. This is what you were talking about. Not done in any GA version of Informix currently - Rebuild the index to make it more "ballanced" (there's much more to this than what we have space and time here). This is not done currently unless you recreate the Index. I believe other RDBMS can REBUILD the indexes without having to read the data. They just read the index already created and take advantage of the already sorted data. We don't do this and possibly it would be nice. However, if the index is really "unballanced", scanning the index in an ordered way is really slow, and I have great doubts that it would be better than read the data and order it. A problem I helped some months ago, make me think it's not a good idea. It would be faster is the index was ok, but in those cases it would be useless to rebuild the index. So, in short, I wouldn't choose this as a priority if I were in development. Regards. On Tue, Sep 28, 2010 at 11:57 AM, Cesar Inacio Martins < cesar_inacio_martins@yahoo.com.br> wrote: > Will be nice if, some day, IBM create the "index repack" (sysadmin API) to > regroup the index extents without need read all table. > > > --- Em *seg, 27/9/10, Fernando Nunes <domusonline@gmail.com>* escreveu: > > > De: Fernando Nunes <domusonline@gmail.com> > Assunto: Re: "Re-indexing" Informix > Para: informix-list@iiug.org > Data: Segunda-feira, 27 de Setembro de 2010, 12:55 > > > > > On Mon, Sep 27, 2010 at 3:47 PM, red_valsen <red_valsen@yahoo.com<http://mc/compose?to=red_valsen@yahoo.com>; > > wrote: > > > Here's what happened after indexes were dropped and recreated: The > number of extents used by individual indexes was substantially > reduced. Greatest number prior was 176; afterwards, 9. I'm somewhat > surprised, yet can't see that this would have anything but a positive > effect on performance, even if to a minor degree. I'll have to do a > little digging to explain the change. > > > This is perfectly normal. The B-Tree scanner will not reduce index extents. > So don't be surprised. What you should check it the number of extents on > your tables. > The index number of extents should somewhat reflect the table number of > extents. And 176 is too high specially if your pagesize is 2K. > > Regardind performance, yes, if you were able to measure any difference it > would be an improvement. But again, this would only be noticeable if you did > frequent and very large index range scans. > > Regards. > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > -----Anexo incorporado----- > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org <http://mc/compose?to=Informix-list@iiug.org>; > http://www.iiug.org/mailman/listinfo/informix-list > > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...