AW: alter table: 'lost' extents

I've just made some deeper investigation. One of extents (I took the largest for simplicity) reported by chunk's extent list (oncheck -pe) as belonging to the dropped table is also not reflected in the "Chunk free list" (the third page of any non-root chunk, can be displayed with 'oncheck -pP CHUNK_NUM 2', and recursively with 'oncheck -pP CHUNK_NUM NEXT' where NEXT is displaeyed in the 'next' field of the page header in the previous output; NEXT=0 means last page in a linked list) That means, the this extent will never be re-used by the engine. It's lost forever. The conclusion is: Informix is not releasing 'old' (previous version) extents after table subjected to 'in-place-alter-table' is dropped !!!!!!!!!!!!!! I'll try to wait for response from the 'list' for a few days and file a bug to PA. P.S. I converted my 9.21 DEV database to 9.40 and repeated the experiment. No change. Lost space again. THIS IS A REALLY BAD PROBLEM ------------------------------------------ Alexey Sonkin -----Original Message----- From: Alexey Sonkin [mailto:alexeis@grandvirtual.com] Hi, everybody, I've just found a very unpleasant problem with 9.21uc4 I have a huge table that was altered (column added) when it was big enough. 'Alter table' was 'in-place' - just because the operation went very fast. After that, a lot of inserts were made, and old records were not updated. Later, the table was dropped. When I made an analysis of disk extents with 'oncheck -pe', I was surprised: a lot of extents (actually, containing all the 'before alter table' records) were not released by the 'drop table'!!! Is it a known bug? Is it fixed in 9.40? Is there way to return extents to the free page pool? ------------------------------------------ Alexey Sonkin sending to informix-list

Alexey, What hardware o/s are you on? I'm heading for infx 9.3, then 9.4, and hope not to hit this if this is for real. I'm on sun solaris 2.9. Thanks, Norma Jean -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On Behalf Of Alexey Sonkin Sent: Thursday, August 05, 2004 9:52 PM To: ids@iiug.org Subject: AW: alter table: 'lost' extents [3327] I've just made some deeper investigation. One of extents (I took the largest for simplicity) reported by chunk's extent list (oncheck -pe) as belonging to the dropped table is also not reflected in the "Chunk free list" (the third page of any non-root chunk, can be displayed with 'oncheck -pP CHUNK_NUM 2', and recursively with 'oncheck -pP CHUNK_NUM NEXT' where NEXT is displaeyed in the 'next' field of the page header in the previous output; NEXT=0 means last page in a linked list) That means, the this extent will never be re-used by the engine. It's lost forever. The conclusion is: Informix is not releasing 'old' (previous version) extents after table subjected to 'in-place-alter-table' is dropped !!!!!!!!!!!!!! I'll try to wait for response from the 'list' for a few days and file a bug to PA. P.S. I converted my 9.21 DEV database to 9.40 and repeated the experiment. No change. Lost space again. THIS IS A REALLY BAD PROBLEM ------------------------------------------ Alexey Sonkin -----Original Message----- From: Alexey Sonkin [mailto:alexeis@grandvirtual.com] Hi, everybody, I've just found a very unpleasant problem with 9.21uc4 I have a huge table that was altered (column added) when it was big enough. 'Alter table' was 'in-place' - just because the operation went very fast. After that, a lot of inserts were made, and old records were not updated. Later, the table was dropped. When I made an analysis of disk extents with 'oncheck -pe', I was surprised: a lot of extents (actually, containing all the 'before alter table' records) were not released by the 'drop table'!!! Is it a known bug? Is it fixed in 9.40? Is there way to return extents to the free page pool? ------------------------------------------ Alexey Sonkin sending to informix-list ----------------------------------------- ============================================================ The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any reproduction, dissemination or distribution of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. Tellabs ============================================================