Re: TBLSPACE table extents. (fwd)

> > 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. John, I just tried your example and found that my table didn't increase in size. My table has 1 column which is a date type. I inserted 15,000 rows. It grew to 160 pages, 60 data and 99 index and 1 bitmap. I deleted all the rows. I then inserted 15,000 more dates which were different then the previous dates and I still end up with 160 pages, 60 data, 99 index and 1 bitmap. So my table didn't grow at all. I'm using 7.12.UC1. Now, I'm not sure why you are unloading and droping the table. If you are concerned with index pages then you should just drop the index and rebuild it and it will compress the index (if you are doing lots of deletes which don't delete all the keys from leaf node). Now if you are talking about giving pages of the table back to the rest of the online instance then yes you will need to drop the table or alter index to cluster. But if you just want to compress an indexbecause you think it is too sparse then you should only have to drop the index and rebuild it because we will reuse those old index pages. The case were we won't is if you don't delete all keys off a leaf page we don't reuse the space unless a merge or shuffle occurs. But if you insert 10000 integers 1 to 10000 and then delete the 1st 5000 then insert 10000-15000 your index should stay about the same size because we loped off the left half of the index and then added to the right side of the index. But if you had the same 1-10000 but deleted every other number and then added 5000 more your index would probably increase in size because we didnt clean out any leaf nodes. Also there is a bit of confusion on reuse. Once an extent is allocated to a table it will not be given back to the online instance but if you remove all the keys from the page (if it was an index) we will reuse it for either another index page or a data page. So if you are tryign to decrease the size of an index that has been deleted from but not big ranges that would cause whole leaf pages to have all keys removed then you should drop and rebuild the index. If you are trying to decrease the size of a table that has had large deletes done to it and you want the space to be available for the rest of the online instance then you should drop and recreate the entire table. From my testing with 7.12 and DATE types if insert 15000 dates, then delete the 1st 7000 dates we will reuse index pages. oncheck -pT data pages 60 index 99 (1st 15000 rows) delete 7000 oncheck -pT data pages 33 index pages 54 (8000 rows) add 7000 different dates: oncheck -pT dpgs 60 ipgs 99 (15000 row in table) So we are reusing free pages. Now the problem would be if on your delete you don't remove enough of the keys on a leaf page to either cause it to merge with another leaf or remove all the keys altogher so that it changes from an index page to a free page. If it is still an index page then yes it will only be reused if you add a row that has a key value that fits within the range of key values that leaf node would store. Jacques -- ******************************************************************** * Jacques P. Renaut "I'd dazzle you with brilliance * * Informix Advanced Support if I only had the knack..." * * email: jrenaut@informix.com #include <disclamier.h> * ********************************************************************