Re: Char versus varchar

Hi Fernando, Fernando Nunes schrieb: > Peternt wrote: >> Enviroment: Informix 7.31 >> S.O. HP-UX 11i >> >> I've heard about using the varchar data type has a certain overhead >> compared with the data type char. > > Yes. Varchar data includes information about the length that is specific > to each row, instead of other datatypes which length is equal in every > row. Besides this when you update a Varchar field the resulting row may > not fit into the already allocated space in the page. This may force the > row to be moved into a new page. > Furthermore there are certain "issues" with Varchar. I can't give you an > exhaustive list, but this two would be included in such a list: > > - You can't perform ligh scans on tables using VARCHAR columns on > current IDS versions (a light scan is a full table scan that don't use > the BUFFERs area of the instance memory. A light scan can only be done > in specific situations (documented) there are certain rumors that starting on IDS 11.50.xC6 there is a chance, that IDS will 'learn' what XPS did know to do for a long time, and that it will be possible to see LIGHT SCANS on a table having columns of type VARCHAR. > - Any ALTER TABLE including changes in a VARCHAR column will NOT be an > inplace ALTER TABLE > >> But, on the other hand, using char data type has advantages in terms >> of the amount of data that can be read (due to more data fit in a >> page). > > I don't quite follow that... The data that may fit into de page depends > on the row size. A char allocates the full length defined, a varchar > only the length used... > A more obscure aspect is that the number of "special" fields (including > varchar) reduce the possible number of extents a table may have... > >> Moreover, the varchar saves the space used. > > True... But a VARCHAR column includes an extra byte... This means > something like VARCHAR(2) is useless (even a row with 1 character will > ocupy 2... and a row with 2 characters will ocupy 3... > Of course with larger numbers and large tables the space savings can be > noticeable. > >> Based on your experiences, are those statements correct? >> What do you recommend? > > My *personal* recommendation it to make your space savings calculation. > More often than I would like I see customers using VARCHARs where it's > not useful... > >> How is in others engines, for example in Oracle ? > > I'm not the best person to answer that. But I had a chat about this with > an Oracle DBA recently. The idea he provided me is that Oracle uses that > extra "length" byte for most of their datatypes. Assuming this is so, > some disadvantages of VARCHAR in Informix would not apply to Oracle > (since the extra overhead is already there by default). > But if this information is relevant to you, please check it, and discuss > it with some people with Oracle knowledge... > >> Is it transparent for applications ? or is there any issue ? > > Yes. But also note another difference between the two datatypes: > Due to ANSI compliance (this should apply to Informix and Oracle, but I > don't think it applies to mySQL for example, a CHAR column includes the > trailing blanks that fulfill the complete field size. This means that > for a CHAR(3) field, if you INSERT "a", "a " and "a " you'll always get > "a " when you SELECT. > VARCHAR on the other end returns exactly what you inserted ("a", "a " > and "a "). > >> Thanks in advance > > Regards dic_k -- Richard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe

Richard Kofler wrote: > Hi Fernando, > > Fernando Nunes schrieb: >> Peternt wrote: >>> Enviroment: Informix 7.31 >>> S.O. HP-UX 11i >>> >>> I've heard about using the varchar data type has a certain overhead >>> compared with the data type char. >> >> Yes. Varchar data includes information about the length that is >> specific to each row, instead of other datatypes which length is equal >> in every row. Besides this when you update a Varchar field the >> resulting row may not fit into the already allocated space in the >> page. This may force the row to be moved into a new page. >> Furthermore there are certain "issues" with Varchar. I can't give you >> an exhaustive list, but this two would be included in such a list: >> >> - You can't perform ligh scans on tables using VARCHAR columns on >> current IDS versions (a light scan is a full table scan that don't use >> the BUFFERs area of the instance memory. A light scan can only be done >> in specific situations (documented) > > there are certain rumors that starting on IDS 11.50.xC6 there is a > chance, that IDS will 'learn' what XPS did know to do for a long time, > and that it will be possible to see LIGHT SCANS on a table having > columns of type VARCHAR. Hello Richard, I specified "current IDS versions". I should have pointed that XPS can do that... I can't comment on "rumours". Even approved features can be backed out if something goes wrong... But we'll see in a few months... I hope you're right ;) Some of the new features that came recently and that will appear in the future are things that XPS can do... Regards,