Re: free space in .dat files

Charles Francis (ancrcs4!cfrancis@ancrcs4.attmail.com) wrote: : 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. : Chuck Francis : Be Safe NOT Sorry As far as I know, the above is incorrect. The space required to store each row in the .dat file is the rowsize + 1. (At least from C-ISAM version 2.0 to Standard Engine 7.1). (The extra character is a LINE-FEED character added after each row) Thus, the space need to store nrows is round_to_the_next_logical_block( (rowsize + 1) * nrows ) ) Example: take 80 rows of 60 bytes each (60 + 1) * 80 = 61 * 80 = 4880 bytes logical block size 1 K or 1024 bytes round_..._block(4880) = 5120 bytes Personally, I have not seen any sort of difference for NULL or NOT NULL columns. Deleted rows are replaced with ascii null ('\\0') characters. Catalin =========================================================================== Catalin Badea _/_/_/ _/_/_/ _/_/_/ _/_/_/ _/ _/ _/_/_/ _/ _/ _/ _/ _/_/ _/ Senior Technical Architect _/ _/_/ _/ _/_/_/ _/ _/_/_/ _/ _/ _/ _/ _/ _/ Tecsys Inc. _/ _/_/_/ _/_/_/ _/_/_/ _/ _/_/_/ ====== cat@tecsys.com =========== include system "disclaimer.4gh" ======