From: davek@informix.com (David Kosenko)
Newsgroups: comp.databases.informix
Subject: Re: Calculating Extents
Date: 23 Dec 92 17:07:29 GMT
Organization: Informix Software, Inc.

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