Char versus varchar

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. 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). Moreover, the varchar saves the space used. Based on your experiences, are those statements correct? What do you recommend? How is in others engines, for example in Oracle ? Is it transparent for applications ? or is there any issue ? Thanks in advance

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) - 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

On Aug 13, 6:27 am, Fernando Nunes <domusonl...@gmail.com> wrote: > 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) > - 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

On Aug 13, 1:40 pm, prasad <psreeniv...@gmail.com> wrote: > On Aug 13, 6:27 am, Fernando Nunes <domusonl...@gmail.com> wrote: > > > 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) > > - 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 Another case is , we may not be able to take advantage of Inplace alters when performing schema changes on varchar columns. Thanks Prasad