Re: free space in .dat files

---------- From: ilist To: internet!rmy.emory.edu!informix-list Subject: Re: free space in .dat files Date: Friday, June 16, 1995 5:47PM In article <3rs0ab$ias@cssun.mathcs.emory.edu> ant01@margherita.record.unipd.it "ant01" writes: >> As everybody knows, Informiz doesn't use phisical deletion but logical >> deletion. >> >> So, in a ".dat" file we have a lot of "logically" not used space. >> >> How can we calculate this lost space, knowing the number of rows >> contained in a table and the its structure ? >(very) Approximately rowsize+4 * nrows. To recover it, alter an index >to CLUSTER which will recover the free space for you. > >-- >============================================================================ >Sally Woolrich | This mail contains my personal >sally@excelsis.demon.co.uk | views not those of my employer! >============================================================================ Using (rowsize + 4) * nrows may not come close to estimating the size of an Informix SE data file. Unless the columns of the rows have been defined as not allowing NULLs every row stored may be a different length. For instance; If you have 1,000,000 rows and a rowsize of 100 the estimate would be a file size of 104,000,000 bytes. However; If on an average each row only uses 70 bytes for data the required storage space would be 74,000,000 bytes. Now let say there had been 200,000 deleted rows each using the full rowsize of 100 bytes. this would make the file size 94,000,00 bytes for all rows including the deleted rows. The estimate for the 1,000,000 rows is 10,000,000 bytes larger the the data file. If the schema shows that the "NOT NULL" clause has not been used when defining the columns, (Make no mistake I am NOT recommending) the use of "NOT NULL" just so you have a fixed storage length for your records), the only way to fine out how much space is used for data storage is write a program to select every row and add up the actual length of each column in each row and then add the Informix overhead for the total number of rows to the actual number of data bytes found in the table. If you want to guess at how much space your deleted rows are using then guess at how many rows have been deleted and figure that the percentage of deleted rows to the total of all rows will be a little (or a lot) more than the percentage of space reclaimed. This is because Informix does when possible, (when the new or updated row is equal to or smaller than the deleted row), re-use the storage space of deleted rows. I mentioned updated rows because if you do a lot of updates to your data, this too can leave reclaimable space because if the updated row is larger than the original, Informix will not use the original row storage space. One last word of caution. If you alter an index to CLUSTER to recover storage space, BE SURE YOU HAVE ENOUGH ADDITIONAL STORAGE SPACE FOR A SECOND COPY OF YOUR DATA AND YOUR INDEXES. Because if you do not, you will need to pray that you really can restore from your backup. Reclaiming storage space can also be done by unloading the table, rebuilding and reloading the table. Not fun but the unload can be to tape. Chuck Francis Be Safe NOT Sorry