Re: Calculating Extents

Naomi Walker writes: > >Can someone please tell me a reliable way to calculate extents >on tables with TEXT BLOBS? I've tried using the formula in the >book, but that doesnt work well. In our case, this BLOB is in >the tblspace, not in a blobspace. Does it make a difference? Absolutely! If your blobs are in a blobspace, then you only need add 56 bytes (the size of the struct stored in the table to locate the blob) for each blob column to your rowsize, and calculate the extent size as for a "regular" row. When you use tblspace blobs, the space required for the blob storage must be included in the extent sizing. I'll outline some details on this further on. >Should I just use the average size of our BLOBS (2k) for the row length? No, but an average size will be used in the calculation. >How do you calculate extents on varchars? There are a few options here. You are just adding the "size" of the column to those of the rest of the columns to come up with a rowsize, but the question is what "size" to use for a varchar. You can use the max possible size the varchar column you've defined could be (my preference); you could use the average estimated size (ok if you have a good idea of the data going in); you could use the min reserved size (if you use a min length for the definition). Pick one, and use it to calculate the rowsize. The rest of the calculation is the same as in the book. >Does the extent formula hold true if the table has no indicies? Yes, you just have a 0 size value for your index calculation, i.e. you only need size the data component. There is no additional overhead for indexing on a table with no indexes. ok, now for adding tblspace blobs (TBs from here on) into the extent calculation. First, understand that, while TBs are stored in the extents with the rest of the data, they are kept on their own pages. So calculate the pages needed for data (still using 56 bytes for each blob column when calculating the row size), and the pages needed for the indexes. Then calculate your "blob paging"; this is the number of blob "items" that will fit in a tblspace blob page (which is your pagesize). Recall that there is 24 bytes of overhead in each page, so for a 2K page size, the useable space will be 2020 bytes. If you have very small blobs, many "items" will fit onto a page. If they are larger than a page, several pages will be needed to hold the blob data. One page will be filled, and the other will use as much space as necessary for the remainder. Note that these "blob remainder pages" can hold remainders from several blobs. Also note that each blob "item piece" will require an 8 byte slot table entry on the page in which it is stored, and each "non terminating" piece will require a 4 byte forwarding pointer (to the "next piece"). So, to use your 2K blobs as an example, we can calculate: Since 2048 is greater that the page capacity, we'll need 1.x pages to store each blob. If we have 2020 bytes available on a page, and 8 + 4 bytes overhead is needed for each piece of blob data stored on the page, we know that 2008 bytes of a blob will fit on a page, leaving a 40 byte remainder piece to go onto another page. Each remainder will have 8 bytes of overhead (no forwarding pointer is necessary since it is a terminating piece), so each remainder will use up 48 bytes on that remainder page. With 2020 bytes available on a remainder page, we can get 42 remainder pieces onto that blob remainder page. So, to make the math easier, we would need 43 pages to store 42 2K blobs. This is in addition to the number of pages needed to store data and index stuff. Dave -- Disclaimer: These opinions are not those of Informix Software, Inc. ************************************************************************** "I look back with some satisfaction on what an idiot I was when I was 25, but when I do that, I'm assuming I'm no longer an idiot." - Andy Rooney