Re: TBLSPACE table extents. (fwd)
For those who want to try it!
---------- Forwarded message ----------
Date: Thu, 20 Jun 1996 17:21:54 -0500 (CDT)
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.
-------------------------------------------------------------------------
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 Thu, 20 Jun 1996, Jacques Renaut wrote:
} Cheryl Kendricks wrote:
} >
} > Delete row are not REUSED!!! You have to ALTER table: Best ALTER index to
} > CLUSTER or UNLOAD(TBUNLOAD)/LOAD(TBLOAD). I'v find if you have an existing
} > DB and have been keep stats on growth estimates work out GOOD!!! But
} > that's an IF!!!
} >
} >
}
} I would have to disagree with you. Deleted row space is reused and
} I'll show you a quick way to verify that it is.
}
} 1) create a table with just 1 serial column
} 2) insert 10000 records into the table
} 3) get the hex partnumber of the table from systables
} (select hex(partnum) from systables where tabname = " whatever")
} 4) as informix run oncheck -pp 0x<partnum> 0
} this will dump on the bitmap page with tracks space. You should
} noticed that probably all but one of the values are 3 (for
} full data pages) and then a 1 (data page with space)
} 5) delete from table where col < 3000...this should delete the
} 1st 3000 rows of the table
} 6) run oncheck -pp 0x<partnum> 0 and notice that some of your pages
} that had 3's are now 0 (for free page)
} 7) insert another 10000 records
} 8) run oncheck -pp 0x<partnum> 0 and now notice that the 0's turned
} back to 3's and you also probably have more 3's then before
} and there could have been some 1's that turned to 3's. All
} illustrating that we do indeed reuse space from deleted rows.
}
}
}
} --
} ********************************************************************
} * 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> *
} ********************************************************************
}