Re: Sizing of char,varchar and text fields

In article alex@alexpc.films.com, "Alexander J. Oss" <alex.oss@films.com> writes: > > Whoops, looks like you're right <sheepish g>. P. 2-130 of the > Online 5.0 manual: "Blobs can share dbspace blob pages if more than > one blob can fit on a single page, or if more than one trailing portion > of a blob can fit on a single page." > > ...but hmmm! p. 10-17 of the SQL Tutorial: "Each blob data item occupies > a whole number of pages." Using their space-estimating scheme > example, it looks like each entry uses a whole page. Also, p. 9-19: "It [a > blob] is allocated in whole disk pages; a short item wastes space." > > Well, now I'm confused! > > Any clarification from out there? > The minimum amount of space allocated when you store a blob in either a blobspace or a dbspace is one blob page. (This stand alone fact, with no further clarification, might be the cause of the confusion unwittingly created by the author(s) of the SQL Tutorial.) The exception is when blobs are stored in dbspaces instead of blobspaces. When a blob is stored in a dbspace blob page, OnLine will fit it into an existing blob page if it can, or if it can't and the blob is bigger than an even blob page size, will fit its leftover (trailing) portion onto an existing blob page, if it can. Otherwise, one or more dbspace blob pages will be created to hold the new blob. Dbspace blob pages allow blobs to share blob pages. This gives you less space wasted because most blob pages will be filled as far as possible. It may also give you binary data fragmentation, which can adversely affect performance. The performance impact may not be great if you are storing a small to moderate number of small blobs. Blobspace blob pages do not allow blobs to share blob pages. This makes blob retrieval more efficient because if you sized your blobspace blob pages appropriately, there will be far less binary data fragmentation. It also causes you to almost certainly get more space wasteage as not all blobs are going to be exactly the size you allocated for blobspace blob pages. It looks like the author of the SQL Tutorial was assuming blobspaces would be used to store blobs in, and not dbspaces. The reference on page 9-19 is pretty misleading because it does not take into consideration the fact of blob storage in dbspace blob pages. The reference on page 10-17 is not so bad, because it refers to *estimating* blobpages. There is nothing too terribly wrong with *estimating* the allocation of one (or more) pages per blob: "more" pages if your blobs are >2K in size and you're using dbspace blob pages; "one" if you estimate one blob per blobspace blob page because you sized it that way. (FYI, the kind of context assumption you see in the SQL Tutorial references to blob storage is the most common kind of error you will find in the Informix documentation.) NOTE: if you're estimating EXACTLY one blob per blobspace blob page, you are: LUCKY - all your blobs are exactly the same size; SLOPPY - you sized your blob pages too large; or UNFINISHED - because you need to add that percentage of blobs which you expect to exceed one blob page in size. Then, again, you might be one of the other Seven Informix Dwarves, but I wouldn't begin to guess which one. I'm "HAPPY". Here is a sample blob distribution for a blobspace with a 20K blob page size: SIZE IN KB QUANTITY ------------- -------- < 10,000 77 10,000 - 19,999 235 20,000 - 29,999 1428 30,000 - 39,999 202597 40,000 - 49,999 3570 50,000 - 59,999 4 > 60,000 10 Total Number of Blobs: 207921 So long as you focus on the principles of blobspace blob page storage vs dbspace blob page storage - as presented in the OnLine Administrator's Guide - there is no reason to get wrapped around the axle because of other documentation. Hope this clarifies it for you. Bill Williams E-Mail: bwilliam@jaxnet.com Flames: nobody@nowhere.com -or- redirect to /dev/null +-----------------------------------------------------------------+ | Disclaimer: My views, opinions, and judgments are my own. | +-----------------------------------------------------------------+ | "Free scientific inquiry" is a redundancy. | | "Free governmental inquiry" is self-contradictory. | | | | Be sure to read "Atlas Shrugged" by Ayn Rand. It's the world's | | greatest novel, rated #2 of those books readers said had the | | most impact on their lives (NY Times readership poll)! | +-----------------------------------------------------------------+