Re: Question about VARCHAR Vs. CHAR fields

DA Morgan wrote: > Ian wrote: > >> jdokos wrote: >> >>> Hello, >>><SNIP> > > Is it true that in Informix VARCHAR takes more space than CHAR? In > Oracle the waste of space and CPU comes with working with CHAR and > it has been almost completely abandoned. Yes and no. A VARCHAR stored a 1 byte length and an LVARCHAR a 2 byte length as part of the field, however, both types are true variable length fields. So, if the field is full it will take a byte or two extra storage than the equivalent CHAR, if not full it will take less storage than CHAR. The rub and performance hit for using VARCHAR or LVARCHAR in IDS is similar to that in DB2. If expanding a variable column in a row causes the row to no longer fit on the data page which is its home then the row is moved to another page and a forwarding pointer is left behind in its original location. This is so that index keys do not have to be rewritten. Beyond that if expanding a variable lenght field on a row causes the row to become larger than a page (not possible in DB2, but that's another issue) IDS will create a remainder page to hold the tails of oversized rows from several pages and a forwarding pointer to the location of the tail is left at the end of the home row portion. Since LVARCHARs can be as long as 32K this can happen several times with a row taking up several full pages and a tail entry. Usually, performance wise, I find breaking long strings into multiple fixed length CHAR rows in a child table or by using a MULTISET is far more efficient and no harder to code for. Art S. Kagel

Art S. Kagel wrote: > DA Morgan wrote: > >> Ian wrote: >> >>> jdokos wrote: >>> >>>> Hello, >>>> <SNIP> >> >> >> Is it true that in Informix VARCHAR takes more space than CHAR? In >> Oracle the waste of space and CPU comes with working with CHAR and >> it has been almost completely abandoned. > > > Yes and no. A VARCHAR stored a 1 byte length and an LVARCHAR a 2 byte > length as part of the field, however, both types are true variable > length fields. So, if the field is full it will take a byte or two > extra storage than the equivalent CHAR, if not full it will take less > storage than CHAR. > > The rub and performance hit for using VARCHAR or LVARCHAR in IDS is > similar to that in DB2. If expanding a variable column in a row causes > the row to no longer fit on the data page which is its home then the row > is moved to another page and a forwarding pointer is left behind in its > original location. This is so that index keys do not have to be > rewritten. Beyond that if expanding a variable lenght field on a row > causes the row to become larger than a page (not possible in DB2, but > that's another issue) IDS will create a remainder page to hold the tails > of oversized rows from several pages and a forwarding pointer to the > location of the tail is left at the end of the home row portion. Since > LVARCHARs can be as long as 32K this can happen several times with a row > taking up several full pages and a tail entry. > > Usually, performance wise, I find breaking long strings into multiple > fixed length CHAR rows in a child table or by using a MULTISET is far > more efficient and no harder to code for. > > Art S. Kagel Thanks. Daniel Morgan -- Daniel A. Morgan http://www.psoug.org damorgan@x.washington.edu (replace x with u to respond)