Re: CHAR() v. VARCHAR()

I beg to differ on the point where you beg to differ... >From: rmcall@on-ramp.ior.com (Rob McAllister) >Date: Tue, 12 Sep 1995 04:28:22 GMT >X-Informix-List-Id: <news.16970> > >CRAIG@CHEMISTRY.CHEM.UTAH.EDU wrote: ><snip> > >> So, varchars make much better use of you disk space-wise. There is, >>of course, a price to pay. When you add varchars to a table, the rows >>are no longer of a fixed length. If your select does a sequential >>scan of the table, it will now take longer because it will need to >>calculate the starting location of each row. For example, before >>the engine can examine record #2, it must figure out the length of >>record #1. Etc. Also, record updates become more difficult. For example, >>imagine a record with a varchar of 10 bytes. If you update the record >>so that the varchar is now 20 bytes, the record will most likely have >>to be moved to another spot on the disk where it can gain the 10 extra >>bytes it needs. Yes, it may need to be moved. If the row is bigger than a single page, the remainder pages will be juggled as necessary to get the new data to fit; new pages will be allocated if necessary. Otherwise, the row is smaller than a single page. If there is space after the existing record, then it will be used and the row won't move. If there is space on the page, the page will be reshuffled (compressed -- onstat/tbstat reports on page compressions). Otherwise, a forwarding pointer (4-bytes) will be left behind (so that the ROWID does not change), and the data will be moved to a page where there is enough room. >I beg to differ on only one point, the data record is not really variable length >in that offset need to be calculated. The VARCHAR actually stores a 4-byte >pointer in the data record for each occurance. NO! Only in the case where the updated record will not fit on the original home page will there be a forwarding pointer, and this points to all the data for the record, not to an individual VARCHAR field within the record. >This points to a section (file) reserved for the index. So, there is >always a 4 byte overhead for each VARCHAR field and the extra I/O to fetch >the variable length data from a different page in the index space. This >is how C-ISAM does it, and I believe this still holds true, even in the >ON-LINE product. That is roughly how C-ISAM does it; it is definitively NOT how OnLine does it. Read the OnLine Administrator's Guide for details on how VARCHAR data is stored. In the 5.00 Version, pages 2-111..2-118. In the 6.00 Version, it's in Vol 2, pages 40-33..40-43. In the 7.10 Version, it's in Vol 2, pages 43-38..43-45. >TEXT and BYTE are different in that this variable length data is stored >external to both the data and index spaces (see blob space). Unless the blob is stored IN TABLE, in which case it is stored in separate data pages from the rest of the data in the row within the dbspace (or dbspaces, potentially, if you're using 7.10), used by the table. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>