Re: char or varchar for small colums?

>Rainer Schaub wrote: >> >> I'm only thinking to take varchar if 1.) the table have or will have >> many rows (e.g. over 100.000) and 2.) the charcterfield has more than 10 >> Bytes. >> Very important is the fact that the field shoud'nt be changed anymore. >> for example you insert "i don't like mondays" in a varchar(50,1) and >> after some day you change it to "i dont't like working on mondys" than >> this row will probably spread over two pages. and this is really bad. > >This is not exactly right. Informix never spreads a row over two pages >unless the row itself is larger than 2020 bytes. However, if there is >not enough free space on the page where the row is currently (the >engine will compress all of the free space on the page first to see if >it can fit the newly expanded row on the same page) then the row will >be moved to another page where there is enough space and a forwarding >pointer will be left on the original page (the slot pointer contains >the new rowid instead of an in-page offset) so that the rowid of the >row stays the same. If this happens then two I/Os are needed to fetch >that row. > >RULE OF THUMB: >If you have a column that may contain a few characters in most rows but >many more characters in other rows then use varchar to save disk space. >For small columns and any relatively fixed size strings use char. Just a couple of comments. The impact of varchars vrs chars is dependent on the application and querries made against that table. If the queries tend to cause sequential scans, then the impact of a row that as been moved because of changes in a varchar field has very little impact. This is because the extra io is not required as an index is not being used to find the row. However, if indexes are being used to get to the rows, then it becomes more important to avoid increasing the size of varchar fields to avoid the "forward pointer" and extra IO. This is one of the reasons that sites that use a lot of varchars can encounter a gradual decrease in performance in dynamic tables. Otherwise it is true. Informix does not span rows across pages unless the row is larger than the page size, or there are text/blobs. Another thing to consider. If one of the rows is displaced to another page because the prime page gets too full, then there is going to be some free space left on the original page. This space can be used for column expansion of the rows remaining on the original page. > >> you have too know that varchar saves diskspace (sometimes a lot) but it >> cost a little cpu. the saving of diskspace means that much more rows fit >> in a page and therefore you speed up data-read. and disks are much more >> slowly than cpu!!! > >True! > >> > I am looking for some hints on char vs varchars. >> > Is there any overhead for varchar? I have a many small colums (i.e. 1 >> > to 5 characters) and wonder if I am better of using char or varchar. >> > So far I have been using char for <=3 and varchar for colums >= 4 >> > characters. > > Madison Pruet