Re: CHAR v. VARCHAR2: Space or Performance Optimization

Original post asked if space saving of varchar2 vs char was worth performance cost of dealing with variable data. One reply disagreed >>

>In principle, varchar2 should save space and inprove performance, but
>I have no quantative data to back this up. The reason:
>- save space: The space used to hold length info is probably less
> than the space wasted in fixed fields that are padded out to the
> full width

I don't agree. Consider names for example. The average length of surnames in the U.S. is about 7 characters. Will all surnames in your database fit in 7 characters. Allow 20 to fit 99.9% of them.

With a length byte, that is an average of 8 bytes plus overhead ( 2 bytes in this case I believe).
<<
In general the use of varchar2 for all character columns in place of char will result in better performance due to denser block packing that results. Oracle stores the length and does not have to scan for the end of string like 'c'. The space savings of varchar2 over char for descriptive type data columns, names, addresses, and so on can be a very significiant portion of the total table size. The storing of the size in a one byte field for column lenghts of 128 or less, (3 bytes for 129+, see manual) results in fast access. If you check out the machine instruction sets you will see that character data is generally moved and compared one byte at a time or one word at a time so processing a varchar2 column can be less costly than processing a fixed length char column once the column length exceeds a couple of bytes.

This is all opinion when you get down to it. But if you check out the Oracle internal tables, i.e., x$ and v$ tables, then you will notice that Oracle uses varchar2 for its own stuff. If it is good enough for Oracle then it is probably good enough for you and me.

Mark Powell -- The only advise that counts is the advise that you follow so follow your own advise Received on Sun Jul 13 1997 - 00:00:00 CDT