Unwanted index fragmentation

Re-sizing a database for migration and taking care to get the extent sizes just how we want them, I was shocked to see indexes immediately grabbing 20+ extents. It seems worse on tables with lots of varchars, where I've really pulled the extent sizes back to suit the actual rather than maximum row lengths. Any workaround that doesn't involve oversizing the base extents?

Hi, Indices on varchar-columns always allocate the maximum possible column length to store the index keys. Regards, Andreas > ------------------------------------------- SPAR Österreichische Warenhandels-AG Hauptzentrale A - 5015 Salzburg, Europastrasse 3 FN 34170 a Tel: +43 662 4470 24223 Mobile: +43 664 6259575 E-Mail: Andreas.KUTSCHE@spar.at Internet: http://www.spar.at Wichtiger Hinweis: Der Inhalt dieser E-Mail kann vertrauliche und rechtlich geschützte Informationen, insbesondere Betriebs- oder Geschäftsgeheimnisse, enthalten, zu deren Geheimhaltung der Empfänger verpflichtet ist. Die Informationen in dieser E-Mail sind ausschließlich für den Adressaten bestimmt. Sollten Sie die E-Mail irrtümlich erhalten haben so ersuchen wir Sie, die Nachricht von Ihrem System zu löschen und sich mit uns in Verbindung zu setzen. Über das Internet versandte E-Mails können leicht manipuliert oder unter fremdem Namen erstellt werden. Daher schließen wir die rechtliche Verbindlichkeit der in dieser Nachricht enthaltenen Informationen aus. Der Inhalt der E-Mail ist nur rechtsverbindlich, wenn er von uns schriftlich bestätigt und gezeichnet wird. Sollte trotz der von uns verwendeten Virus-Schutzprogramme durch die Zusendung von E-Mails ein Virus in Ihre Systeme gelangen, haften wir nicht für evtl. hieraus entstehende Schäden. Wir danken für Ihr Verständnis. Important notice: The contents of this e-mail may contain confidential and legally protected information that is in particular related to operational and trade secrets, which the recipient is obliged to treat as confidential. The information in this e-mail is made available exclusively for use by the addressee. In the event that the e-mail may have been sent to you in error, we would ask you to kindly delete this communication from your system and to contact us. E-mails sent via the Internet can be easily manipulated or sent out under someone else's name. We therefore do not accept legal liability for the information contained in this communication. The contents of the e-mail are only legally binding if they have been confirmed and signed by us in writing. If, in spite of our using Antivirus protection software, a virus may have penetrated your system through the sending of this e-mail, we do not accept liability for any damage that may possibly arise as a result of this. We trust that you appreciate our position. ------------------------------------------- -----Ursprüngliche Nachricht----- > Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im Auftrag von > ANDY KENT > Gesendet: Dienstag, 22. Jänner 2008 14:49 > An: ids@iiug.org > Betreff: Unwanted index fragmentation [11046] > > Re-sizing a database for migration and taking care to get the extent sizes > just how we want them, I was shocked to see indexes immediately grabbing > 20+ > extents. It seems worse on tables with lots of varchars, where I've really > pulled the extent sizes back to suit the actual rather than maximum row > lengths. > > Any workaround that doesn't involve oversizing the base extents? > > > ************************************************************************** > ***** > Forum Note: Use "Reply" to post a response in the discussion forum. > > See you at the IIUG Informix 2008 Conference > The Power Conference for Informix Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas > http://www.iiug.org/conf > Registration Now Open!!

Actually index on varchar columns do not allocate the maximum space in the index, but only allocate the space required by the data. Here is a little test which print out the data in the index pages I create a table with a varchar column sized of 200 bytes, create an index insert two rows filling just a small portion of the row. I then print out the index to see the size of each row. As you can see each index slot is only 10 and 12 bytes long, not the 200 bytes of the fully sized varchar column. John create table vchar (c1 varchar(200)); create index vchar_ix1 on vchar(c1); insert into vchar values("JOHN"); insert into vchar values("MILLER"); oncheck -pp 2097217 1addr stamp chksum nslots flag type frptr frcnt n= ext prev 2:2430 6418868 f8a9 2 b0 BTREE 46 10182 0= 0 slot ptr len flg 1 24 10 0 2 34 12 0 slot 1: 0: 4 4a 4f 48 4e 0 0 1 1 0 .JOHN.........= .. slot 2: 0: 6 4d 49 4c 4c 45 52 0 0 1 2 0 .MILLER.......= .. = "Andreas.KUTSCHE@ = spar.at" = <andreas.kutsche@ = To spar.at> ids@iiug.org = Sent by: = cc ids-bounces@iiug. = org Subj= ect AW: Unwanted index fragmentation= [11049] = 01/22/2008 06:06 = AM = = = Please respond to = ids@iiug.org = = = Hi, Indices on varchar-columns always allocate the maximum possible column length to store the index keys. Regards, Andreas > ------------------------------------------- SPAR =D6sterreichische Warenhandels-AG Hauptzentrale A - 5015 Salzburg, Europastrasse 3 FN 34170 a Tel: +43 662 4470 24223 Mobile: +43 664 6259575 E-Mail: Andreas.KUTSCHE@spar.at Internet: http://www.spar.at Wichtiger Hinweis: Der Inhalt dieser E-Mail kann vertrauliche und recht= lich gesch=FCtzte Informationen, insbesondere Betriebs- oder Gesch=E4ftsgehe= imnisse, enthalten, zu deren Geheimhaltung der Empf=E4nger verpflichtet ist. Die= Informationen in dieser E-Mail sind ausschlie=DFlich f=FCr den Adressat= en bestimmt. Sollten Sie die E-Mail irrt=FCmlich erhalten haben so ersuche= n wir Sie, die Nachricht von Ihrem System zu l=F6schen und sich mit uns in Verbindung zu setzen. =DCber das Internet versandte E-Mails k=F6nnen leicht manipuliert oder = unter fremdem Namen erstellt werden. Daher schlie=DFen wir die rechtliche Verbindlichkeit der in dieser Nachricht enthaltenen Informationen aus. = Der Inhalt der E-Mail ist nur rechtsverbindlich, wenn er von uns schriftlic= h best=E4tigt und gezeichnet wird. Sollte trotz der von uns verwendeten Virus-Schutzprogramme durch die Zusendung von E-Mails ein Virus in Ihre Systeme gelangen, haften wir nicht f=FCr = evtl. hieraus entstehende Sch=E4den. Wir danken f=FCr Ihr Verst=E4ndnis. Important notice: The contents of this e-mail may contain confidential = and legally protected information that is in particular related to operatio= nal and trade secrets, which the recipient is obliged to treat as confidential.= The information in this e-mail is made available exclusively for use by the= addressee. In the event that the e-mail may have been sent to you in er= ror, we would ask you to kindly delete this communication from your system and = to contact us. E-mails sent via the Internet can be easily manipulated or sent out und= er someone else's name. We therefore do not accept legal liability for the= information contained in this communication. The contents of the e-mail= are only legally binding if they have been confirmed and signed by us in writing. If, in spite of our using Antivirus protection software, a virus may ha= ve penetrated your system through the sending of this e-mail, we do not ac= cept liability for any damage that may possibly arise as a result of this. We trust that you appreciate our position. ------------------------------------------- -----Urspr=FCngliche Nachricht----- > Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im Auftrag vo= n > ANDY KENT > Gesendet: Dienstag, 22. J=E4nner 2008 14:49 > An: ids@iiug.org > Betreff: Unwanted index fragmentation [11046] > > Re-sizing a database for migration and taking care to get the extent sizes > just how we want them, I was shocked to see indexes immediately grabb= ing > 20+ > extents. It seems worse on tables with lots of varchars, where I've really > pulled the extent sizes back to suit the actual rather than maximum r= ow > lengths. > > Any workaround that doesn't involve oversizing the base extents? > > > ***********************************************************************= *** > ***** > Forum Note: Use "Reply" to post a response in the discussion forum. > > See you at the IIUG Informix 2008 Conference > The Power Conference for Informix Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas > http://www.iiug.org/conf > Registration Now Open!! ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!=

ANDY KENT wrote: > Re-sizing a database for migration and taking care to get the extent sizes > just how we want them, I was shocked to see indexes immediately grabbing 20+ > extents. It seems worse on tables with lots of varchars, where I've really > pulled the extent sizes back to suit the actual rather than maximum row > lengths. > > Any workaround that doesn't involve oversizing the base extents? No. It MIGHT be allowed to specify extent sizing for indexes independently in the next release of IDS 11 (11.50?) but that's not quite in Beta testing yet, so the feature list hasn't been released. I very much doubt it will be back ported to 7.31. Art S. Kagel Oninit ================================================================================ =========== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ================================================================================ ===========

John Miller iii wrote: > Actually index on varchar columns do not allocate the maximum > space in the index, but only allocate the space required by > the data. Here is a little test which print out the data in the index > pages > True John, but the problem, and question, here is about the number of extents and FAIK IDS is allocating pages for the index as if the keys were maximum length while Andy sized his extents calculated on average actual length, so the extents are too small for the index pages and there are too many of them. Andy: One solution might be to move the indexes on VARCHARs to a small dbspace set asside just for them so that the extents are all continguous and so compressed into one or a few. Art S. Kagel Oninit > I create a table with a varchar column sized of 200 bytes, create an > index insert two rows filling just a small portion of the row. I then > print out the index to see the size of each row. As you > can see each index slot is only 10 and 12 bytes long, not the > 200 bytes of the fully sized varchar column. > > John > > create table vchar (c1 varchar(200)); > create index vchar_ix1 on vchar(c1); > insert into vchar values("JOHN"); > insert into vchar values("MILLER");> > oncheck -pp 2097217 1> addr stamp chksum nslots flag type frptr frcnt n= > ext > prev > 2:2430 6418868 f8a9 2 b0 BTREE 46 10182 0= > > 0 > > slot ptr len flg > > 1 24 10 0 > > 2 34 12 0 > slot 1: > > 0: 4 4a 4f 48 4e 0 0 1 1 0 .JOHN.........= > ... > slot 2: > > 0: 6 4d 49 4c 4c 45 52 0 0 1 2 0 .MILLER.......= > ... > > = > > "Andreas.KUTSCHE@ = > > spar.at" = > > <andreas.kutsche@ = > To > > spar.at> ids@iiug.org = > > Sent by: = > cc > > ids-bounces@iiug. = > > org Subj= > ect > > AW: Unwanted index fragmentation= > > [11049] = > > 01/22/2008 06:06 = > > AM = > > = > > = > > Please respond to = > > ids@iiug.org = > > = > > = > > Hi, > > Indices on varchar-columns always allocate the maximum possible column > length > to store the index keys. > > Regards, > Andreas > > > ------------------------------------------- > SPAR =D6sterreichische Warenhandels-AG > Hauptzentrale > A - 5015 Salzburg, Europastrasse 3 > FN 34170 a > > Tel: +43 662 4470 24223 > Mobile: +43 664 6259575 > E-Mail: Andreas.KUTSCHE@spar.at > Internet: http://www.spar.at > > >> Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im Auftrag vo= >> > n > >> ANDY KENT >> Gesendet: Dienstag, 22. J=E4nner 2008 14:49 >> An: ids@iiug.org >> Betreff: Unwanted index fragmentation [11046] >> >> Re-sizing a database for migration and taking care to get the extent >> > sizes > >> just how we want them, I was shocked to see indexes immediately grabb= >> > ing > >> 20+ >> extents. It seems worse on tables with lots of varchars, where I've >> > really > >> pulled the extent sizes back to suit the actual rather than maximum r= >> > ow > >> lengths. >> >> Any workaround that doesn't involve oversizing the base extents? >> > ================================================================================ =========== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ================================================================================ ===========