Space Utilization by index.....IDS 7.31UD3

Hi, I had two indices built on a table of 450M by 32 bytes. One duplicate index is on a single column of type integer and the other unique index is a composite on two columns of type integer and char(2). To keep the index space minimum, I kept the first extent size of the table at 16k and the next extent size at 250Mb. To my surprise, the index have occupied 14Gb of space in the dbspace. Do let me know if you have any explanation or if there is a better way to build indexes without occupying much space. Reg Abraham __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com

Together the two indexes should be taking up about 200MB of disk space, not 14GB! Now each of the indexes would need to allocate multiple extents but index extents are scaled by the ratio of the rowsize to keysize so the next sizes of the two indexes would be ~62MB and ~50MB which might require three extents each which would work out to about 256MB with a default FILL FACTOR or 90%. Is it possible that the indexes were build with a low FILL FACTOR (either using the CREATE INDEX parameter or the default in the ONCONFIG file)? Were the indexesbuilt clean on the full table or on an empty table which grew over time? If the latter running update statistics on the keys may cause the indexes to be compressed or you could try dropping and rebuilding them. Art S. Kagel ----- Original Message ----- From: Abraham Kir.... <bull_informix@yahoo.com> At: 1/30 15:07 > Hi, > I had two indices built on a table of 450M by 32 > bytes. One duplicate index is on a single column of > type integer and the other unique index is a composite > on two columns of type integer and char(2). To keep > the index space minimum, I kept the first extent size > of the table at 16k and the next extent size at 250Mb. > To my surprise, the index have occupied 14Gb of space > in the dbspace. > > Do let me know if you have any explanation or if there > is a better way to build indexes without occupying > much space. > > Reg > Abraham > > __________________________________________________ > Do you Yahoo!? > Yahoo! Mail Plus - Powerful. Affordable. Sign up now. > http://mailplus.yahoo.com

Could you post a copy of the dbschema? > -----Original Message----- > From: Abraham Kir.... [mailto:bull_informix@yahoo.com] > Sent: Thursday, January 30, 2003 1:53 PM > To: ids@iiug.org > Subject: Space Utilization by index.....IDS 7.31UD3 [182] > > > Hi, > I had two indices built on a table of 450M by 32 > bytes. One duplicate index is on a single column of > type integer and the other unique index is a composite > on two columns of type integer and char(2). To keep > the index space minimum, I kept the first extent size > of the table at 16k and the next extent size at 250Mb. > To my surprise, the index have occupied 14Gb of space > in the dbspace. > > Do let me know if you have any explanation or if there > is a better way to build indexes without occupying > much space. > > Reg > Abraham > > __________________________________________________ > Do you Yahoo!? > Yahoo! Mail Plus - Powerful. Affordable. Sign up now. > http://mailplus.yahoo.com > "CONFIDENTIALITY NOTICE: This message originates from WHSmith USA Travel Retail. This email message and all attachments may contain legally privileged and confidential information intended solely for the use of the addressee. If you are not the intended recipient, you should immediately stop reading this message and delete it from the system. Any unauthorized reading, distribution, copying, or other use of this message or its attachments is strictly prohibited. All personal messages express solely the sender's views and not those of WHSmith USA Travel Retail. This message may not be copied or distributed without this disclaimer."