Re: char vs. varchar?

Tom Weng <tomweng_at_home.com> wrote in message news:eS3e6.269797$hD4.65146495_at_news1.rdc1.mi.home.com...
> Could somebody explain to me how Oracle store char datatype data vs.
 varchar
> datatype internally?
>
> Whis is the pro/con in-term of performing update on a char column v.s
> varchar column?
>
>
>

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 Received on Thu Feb 01 2001 - 02:13:18 CST