Re: char vs. varchar?

In article <3a791aa3_at_news.iprimus.com.au>,   "Howard J. Rogers" <howardjr_at_www.com> wrote:
> If SURNAME is defined as CHAR(15), then it takes 15 characters of
 space in
> the block, even if your surname is 'SMITH' -the remaining length is
 just
> padded with trailing spaces. If it is defined as VARCHAR2(15), then
 the
> surname field can be UPTO 15 characters in length, but if your actual
> surname is 'SMITH', only 5 characters are stored in the row (and
 hence in
> the block).
>
> Accordingly, VARCHAR2 is a more efficient way to store data, in terms
 of
> taking up space in the row.
>
> EXCEPT....
>
> Miss Smith (stored as 5 characters) has just married Mr
 Calegeropoulos (14
> characters), and accordingly wishes her name to be changed in the
 database.
> No problem: UPDATE EMP set SURNAME='Calegeropoulos' where
 surname='Smith'.
> Except that Ms Smith's record can't actually grow -physically, it
 would
> require an awful lot of shuffling of block contents around to allow
 her
> record to grow. What Oracle actually does to accomodate such growth
 of
> records is to mark the old one as deleted, and to add the new version
 at the
> top of the block. Provided, of course, that there is *room* at the
 top of
> the block. If there isn't such room, we have to actually add the new
> verison of her record into an entirely new block -BUT THE INDEX STILL
 POINTS
> TO WHERE THE OLD ONE WAS!
>
> This is row migration, and it's a real pain for performance -with
 index
> accesses having to query the old block *and* the new before finding
 the
> record.
>
> So row migration is a Bad Thing, and it arises in large part (though
 not
> exclusively) because of the ability of Varchar2's to grow over time.
 It
> wouldn't have been a problem if all our data types were fixed length
 (like
> char). To prevent it, you have to arrange for there always to be
 room at
> the top of an Oracle block to accomodate row growth -and that's done
 by
> deliberately wasting space in each block by setting something called
> PCTFREE. If all data types were fixed length, we could set PCTFREE
 to 0;
> with variable length data, we have to set 10 or even 20 percent (or
 even
> higher in extreme cases).
>
> So, although we save space in the ROW by using varchar2's, we have to
 waste
> space in the block.
>
> Although that's something to watch out for, it doesn't mean
 Varchar2's are
> bad: numeric fields are variable width too, and hence even if you
 stuck with
> char datatypes, you'd probably have to allow a PCTFREE anyway
 (people's
> salaries increasing over time, etc).
>
> HTH
> Regards
> HJR
Excellent explanation! May I add that you need a non-zero PCTFREE also because ITLs (interested transaction lists) may grow from INITRANS toward MAXTRANS taking free space in blocks. This is due to concurrent inserts into the same block.

A very minor point. People drawing the picture of a data block conventionally show new rows added from bottom up, while block header (which contains row directory etc.) grows from top down, thus narrowing the middle ground.

Yong Huag
yong321_at_yahoo.com

Sent via Deja.com
http://www.deja.com/ Received on Thu Feb 01 2001 - 13:40:11 CST