Re: Char & Varchar2

I would say just to make the code clearer (i.e. PL/SQL) and the database clearer, use CHAR(1) for 'flag' fields, i.e., M(ale) and F(emale), or Y(es) and N(o). Apart from that, varchar2 seems the better choice...

The point about row migrations and chaining with VARCHARs can usually be avoided by using an appropriate PCT_FREE value for the object in question.

Just my thoughts...

Steve Phelan.

Paul Brewer wrote in message ...
>In article <34fb2f35.107655129_at_192.86.155.100>, Thomas Kyte
><tkyte_at_us.oracle.com> writes
>>
>>Simply put, a CHAR is a VARCHAR2 that is blank padded to its maximum
 length.
>>The are stored the same on disk.
>>
>>A char and varchar2 are stored on disk in the same fashion -- both have a
>>leading byte(s) to indicate their length. A CHAR(5) will always (when not
>>null)
>>take 5 bytes for the data plus 1 byte for the length. a Varchar2(5) with
 the
>>value "ABC" will take 3 bytes for the data and 1 byte for the length.
>>
>>A CHAR therefore, always consumes maximum storage. This is good and bad.
 Its
>>bad in that it eats alot of space on disk. Good in that a varchar2, if
 updated
>>frequently with values of different lengths, can cause a row to migrate or
>>chain. A CHAR, since it takes maximum storage, will not cause a row to
 migrate
>>(unless it goes from being NULL to NOT NULL that is).
>>
>Thomas,
>Your point taken entirely. However, unless there are exceptional
>circumstances (such as one could posit based on your example) I would
>say that in the real world, a good rule of thumb would be simply never
>to use CHAR, and always to use VARCHAR2.
>Unless of course, I am missing something, which is entirely possible.
>--
>Paul Brewer
Received on Wed Mar 04 1998 - 00:00:00 CET