Indexes much larger on rebuilt table

IDS 11.5 FC6 on Linux x64 I have a table in a single dbspace taking over 8 milllion pages. I want to defragment over over 8 dbspaces. I've copied most of the rows to a new table that is fragmented. The strange thing is that indexes are now about *twice* the size on the new table as the old! And, reporting some strange things, eg: Index Usage Report for index ..... Average Average Level Total No. Keys Free Bytes ----- -------- -------- ---------- 1 1 6 1952 2 6 47 1454 3 283 115 628 4 32772 104 768 5 3411130 105 -279 ----- -------- -------- ---------- Total 3444192 105 -269 Any ideas what might be going on here? thanks Neil I raised a PMR, 18686 019 866.

Are they local or global indexes? In other words, did you fragment the index using the same expression? A table and index schema would be helpful.... If the index is on an integer column, and it became a global index, this is to be expected because the index row pointer needs to include the partnum of the record. Does this make sense and/or apply to your case? Regards. On Fri, Jun 3, 2011 at 3:06 AM, Neil Truby <neil.truby@ardenta.com> wrote: > IDS 11.5 FC6 on Linux x64 > > I have a table in a single dbspace taking over 8 milllion pages. I > want to defragment over over 8 dbspaces. I've copied most of the rows > to a new table that is fragmented. > > The strange thing is that indexes are now about *twice* the size on the > new table as the old! And, reporting some strange things, eg: > > Index Usage Report for index ..... > > Average Average > Level Total No. Keys Free Bytes > ----- -------- -------- ---------- > 1 1 6 1952 > 2 6 47 1454 > 3 283 115 628 > 4 32772 104 768 > 5 3411130 105 -279 > ----- -------- -------- ---------- > Total 3444192 105 -269 > > Any ideas what might be going on here? > > thanks > Neil > > I raised a PMR, 18686 019 866. > > _______________________________________________ > 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...

"Fernando Nunes" <domusonline@gmail.com> wrote in message news:mailman.970.1307093359.1071.informix-list@iiug.org... >> Are they local or global indexes? In other words, did you fragment the index using the same expression? A table and index schema would be helpful.... If the index is on an integer column, and it became a global index, this is to be expected because the index row pointer needs to include the partnum of the record. >> Does this make sense and/or apply to your case? I sent you the schemas and sistributions privately. I don't want to paste them to the group, as they're someone else's IP. The indexes were not fragmented, just built back into a standard 2k dbspace. Each of those indexes *does* contain an integer so maybe you're onto something ...? Thanks for your interest! Neil

I think there is something wrong with the files as the tables look the same... Did you use -ss on dbschema.? My point is that if your table has only one partition, your indexes will have just the row id. If you have a global index (an index partition that contains rows from several - all - table partitions) the index must contain the partnum in the row pointer. Partnum and rowid. That can be the cause of the increase in size.... This is documented somewhere... look for data/index partitioning in the performance guide. I believe it's there... Regards. On Fri, Jun 3, 2011 at 4:55 PM, Neil Truby <neil.truby@ardenta.com> wrote: > > "Fernando Nunes" <domusonline@gmail.com> wrote in message > news:mailman.970.1307093359.1071.informix-list@iiug.org... > >> Are they local or global indexes? > In other words, did you fragment the index using the same expression? > A table and index schema would be helpful.... If the index is on an integer > column, and it became a global index, this is to be expected because the > index row pointer needs to include the partnum of the record. > >> Does this make sense and/or apply to your case? > > I sent you the schemas and sistributions privately. I don't want to paste > them to the group, as they're someone else's IP. > > The indexes were not fragmented, just built back into a standard 2k > dbspace. > Each of those indexes *does* contain an integer so maybe you're onto > something ...? > > Thanks for your interest! > > Neil > > _______________________________________________ > 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...