RE: Char and Varchar
One addendum - (to my previous reply) that Obie-one makes me think of - sh*t I forgot it. Oh yes. Disk space is cheap. Processor cycles are not. Deep words I know. You can process a char faster than you can a varchar. Unless using a char will inflate your table size by some incredible value (thus making it MORE expensive to read, because there is more blank data) it just ain't worth it. How big is an 'incredible'? YMMV. 2x and you should be looking at varchars. 1.5x perhaps not. Always benchmark something like that. Inserts/reads and updates. "Sure boss-man and we are using 20GB less space now, but it takes us 3 hours longer to read it yeah." cheers j. > -----Original Message----- > From: Obnoxio The Clown [mailto:obnoxio@hotmail.com] > Sent: Monday, November 27, 2000 11:01 AM > To: informix-list@iiug.org > Subject: Re: Char and Varchar > > > From: "Peter Morris" <no_sp@m.please> > > > >My company proposes changing all char(x) columns to varchar(x) > >on the grounds that > > > > "Char stores data in a fixed length format and if data varies in > >length from row to row then space is wasted. By using varchar we > >can use the storage space more efficiently " > > > >What about fixed length fields? If we have a char(10) column, > >and we know that every item in that column will be exactly 10 > >characters long, then is there any advantage in making it a varchar? > > > >I think it might even be less efficient. I presume that for a varchar > >the database must store the length of the data, in addition > to the data, > >so there would be extra resources used. > > > >Am I right? For a fixed length field, is char or varchar better? > >Or doesn't it make any difference? > > Here's another idea -- tell your boss that switching to a > smaller font on > all company documents will save space, as well as using fewer > electrons. > > ______________________________________________________________ > _______________________ > Get more from the Web. FREE MSN Explorer download : http://explorer.msn.com
At updates when a varchar field gets WIDER means it not longer fits in the original space. Online moves it and create a forward pointer on the old 'home' page! Parker, Jack wrote in message <8vuj05$j6f$1@news.xmission.com>... > > >One addendum - (to my previous reply) that Obie-one makes me think of - sh*t >I forgot it. > >Oh yes. Disk space is cheap. Processor cycles are not. Deep words I know. > >You can process a char faster than you can a varchar. Unless using a char >will inflate your table size by some incredible value (thus making it MORE >expensive to read, because there is more blank data) it just ain't worth it. > > >How big is an 'incredible'? YMMV. 2x and you should be looking at >varchars. 1.5x perhaps not. Always benchmark something like that. >Inserts/reads and updates. > >"Sure boss-man and we are using 20GB less space now, but it takes us 3 hours >longer to read it yeah." > >cheers >j. > >> -----Original Message----- >> From: Obnoxio The Clown [mailto:obnoxio@hotmail.com] >> Sent: Monday, November 27, 2000 11:01 AM >> To: informix-list@iiug.org >> Subject: Re: Char and Varchar >> >> >> From: "Peter Morris" <no_sp@m.please> >> > >> >My company proposes changing all char(x) columns to varchar(x) >> >on the grounds that >> > >> > "Char stores data in a fixed length format and if data varies in >> >length from row to row then space is wasted. By using varchar we >> >can use the storage space more efficiently " >> > >> >What about fixed length fields? If we have a char(10) column, >> >and we know that every item in that column will be exactly 10 >> >characters long, then is there any advantage in making it a varchar? >> > >> >I think it might even be less efficient. I presume that for a varchar >> >the database must store the length of the data, in addition >> to the data, >> >so there would be extra resources used. >> > >> >Am I right? For a fixed length field, is char or varchar better? >> >Or doesn't it make any difference? >> >> Here's another idea -- tell your boss that switching to a >> smaller font on >> all company documents will save space, as well as using fewer >> electrons. >> >> ______________________________________________________________ >> _______________________ >> Get more from the Web. FREE MSN Explorer download : >http://explorer.msn.com
The row will only be moved to another page if there is no room on the current page for the expanded row. Otherwise the existing rows on the page are shifted to make room. Physically IB the engine removes the expanded row, compresses the remaining rows upwards to recover space between rows that may occur when a VARCHAR column shrinks, then tries to readd the row at the end placing the new offset into the rows original slot entry. If the row does not fit still it is moved to another page reserved for such orphaned rows and again the original slot on the original page is updated with the page and slot number of the new location. Anyway, bottom line if the row still fits on the page no cost to expansion. If it no longer fits every access to the row will have to access two pages the original home page and slot (where the ROWID still points) and the new overflow page and slot. In a table scan it can mean additional I/Os. Art S. Kagel smooth1 wrote: > > At updates when a varchar field gets WIDER means it not longer > fits in the original space. Online moves it and create a forward > pointer on the old 'home' page! > > Parker, Jack wrote in message <8vuj05$j6f$1@news.xmission.com>... > > > > > >One addendum - (to my previous reply) that Obie-one makes me think of - > sh*t > >I forgot it. > > > >Oh yes. Disk space is cheap. Processor cycles are not. Deep words I > know. > > > >You can process a char faster than you can a varchar. Unless using a char > >will inflate your table size by some incredible value (thus making it MORE > >expensive to read, because there is more blank data) it just ain't worth > it. > > > > > >How big is an 'incredible'? YMMV. 2x and you should be looking at > >varchars. 1.5x perhaps not. Always benchmark something like that. > >Inserts/reads and updates. > > > >"Sure boss-man and we are using 20GB less space now, but it takes us 3 > hours > >longer to read it yeah." > > > >cheers > >j. > > > >> -----Original Message----- > >> From: Obnoxio The Clown [mailto:obnoxio@hotmail.com] > >> Sent: Monday, November 27, 2000 11:01 AM > >> To: informix-list@iiug.org > >> Subject: Re: Char and Varchar > >> > >> > >> From: "Peter Morris" <no_sp@m.please> > >> > > >> >My company proposes changing all char(x) columns to varchar(x) > >> >on the grounds that > >> > > >> > "Char stores data in a fixed length format and if data varies in > >> >length from row to row then space is wasted. By using varchar we > >> >can use the storage space more efficiently " > >> > > >> >What about fixed length fields? If we have a char(10) column, > >> >and we know that every item in that column will be exactly 10 > >> >characters long, then is there any advantage in making it a varchar? > >> > > >> >I think it might even be less efficient. I presume that for a varchar > >> >the database must store the length of the data, in addition > >> to the data, > >> >so there would be extra resources used. > >> > > >> >Am I right? For a fixed length field, is char or varchar better? > >> >Or doesn't it make any difference? > >> > >> Here's another idea -- tell your boss that switching to a > >> smaller font on > >> all company documents will save space, as well as using fewer > >> electrons. > >> > >> ______________________________________________________________ > >> _______________________ > >> Get more from the Web. FREE MSN Explorer download : > >http://explorer.msn.com