Re: TBLSPACE table extents. (fwd)

Thank you VERY VERY MUCH JOHN since you are good at this maybe you could provide PAGE number, Book title, part number for all those in question. Believe I'm not the one!!!! ------------------------------------------------------------------------- Cheryl Kendricks Internet:cherylk@prod1.jcdc.doleta.gov OR cherylk@gwysmtp.jcdc.doleta.gov DTSI, Inc. Database Administrator - DOL Job Corps San Marcos, Texas ------------------------------------------------------------------------ On Fri, 21 Jun 1996, John Hess wrote: } June Tong wrote: } > } > : From: Cheryl Kendricks <cherylk@prod1.jcdc.doleta.gov> } > : To: Jacques Renaut <jrenaut@informix.com> } > : Subject: Re: TBLSPACE table extents. } > } > : May work on a 7, yet to see it happen on 5. ALL DOC I'v read (will be more } > : specific if need) SAY DELETE SPACE IS NOT REUSED. } > } > Please be more specific. If our documentation indeed says this, then it is } > erroneous. } } This happens with Informix 5.x and 7.x. I have read the Informix 7.x docs and they } do specifically state (pardon for not having them in front of me) that index pages } are NOT reused unless you try to put the same key values back into the table. I've } had about 1 year of heavy 7.x experience and have done several tests to verify that } this is true. In fact, I have a MAJOR client for which we have to unload data, drop } the table, reload the data, then rebuild indexes JUST to get around this problem and } reclaim those index pages. The table data pages DO get reused, as stated in the docs, } regardless of whether key values are reused. The docs also mention something that } sort of makes you think that doing UPDATE STATISTICS will reclaim those index pages, } but it isn't very clear and I've had no luck using that as a workaround for this } problem. } } If you only have one index on the table in question, I'm pretty sure you can use } the ALTER INDEX XXX TO CLUSTER to reclaim all of those index pages (IF you have enough } space in the dbspace). But it is too time consuming to do that if you've got 2 or } 3 indexes on an even moderately large table, which is why we had to do the unload, } drop table, create table, reload data, rebuild indexes business I mentioned above. } Also, if the table is the child table for a foreign key which has an "Informix created" } index to support the foreign key, that's all the more reason to be forced to do the data } unloading and reloading stuff. } } By the way, I haven't tested my claim about Informix not reusing index pages with } different key values on tables with keys which DO NOT consist of DATE types. So } perhaps Informix will, under some circumstances, reuse index pages with different key } values. Unfortunately, most of the tables in our systems have a DATE type as part of } the primary key and new data is placed into those tables EVERY day. But we don't keep } all of the data forever, so reclaiming this space (and it's a LOT) is a very } important, but painful, process. } } You don't have to query a bunch of system tables to prove whether Informix exhibits this } behaviour. Do a simple test: create a table with a DATE type column (make sure } the extent size is small so that the table will extend when you start inserting rows). } Now create an index on the DATE column, then insert say 3000 rows into it. Do a } "oncheck -pt" on the table to see how much space got allocated. Then delete all rows } from the table and do the "oncheck -pt" again. You'll see that the allocated space is } still the same. If you put the same 3000 rows BACK into the table, the amount of } allocated space should stay the same, but put 3000 "different" rows into the now-empty } table and the allocated space will only increase. } } I can appreciate the performance implications of Informix reclaiming this index space } WHEN DELETES OCCUR (e.g. shuffling and reconnecting BTREE nodes/pointers), but it } still surprises me that this activity does not seem to take place when Informix (or } the system on which it resides) is not too busy doing anything else. } } } John Hess } Database Administrator } Aeronomics Incorporated } Atlanta, GA }