Re: Underlying Implementation of VARCHAR data types

> And for a very good reason.
>

> I urge people to never use CHAR for any reason and here's why. The
> fixed-length CHAR pads spaces no matter what you put into it. So a
> CHAR(10) with one character is 10 bytes while whe VARCHAR2(10) with
> one charater is 3 bytes. Storage is of little consequence in these
> days of inexpensive disk.
>
> What is of consequence is those spaces used to pad out the other
> nine bytes. They make string comparisons difficult and expensive.
>
> In Oracle VARCHAR (as opposed to VARCHAR2) is a C data type ... not a
> SQL or PL/SQL data type.

All of my data types in question were actually VARCHAR2, not VARCHAR. Apologies for the confusion.

I'm a little confused by your preceding statments. I would think that using CHAR type would be naturally beneficial when dealing with known domains for the target data type. I would think a CHAR(9) for a social security number, or a CHAR(2) would be preferable for a US state abbreviation since I always know the length. I'm totally willing to concede the point, if you tell me that there is no benefit.

Also, in Oracle do I not have to worry about performance penalties associated with VARCHAR types in other systems with things like page overflows if the resultant update of the field results in a length that will no longer fit on the same data page?

Do I also gain savings on an index with a VARCHAR2 types? If I have a index on LAST_NAME VARCHAR2(30), does the index get built using the non-space-padded data element too? This is huge (to me) if it does.

Thanks for your comments. For now this forum is one of my only sources of info. I can't access the online Oracle docs and my printed set is limited.

Cheers,
Mike Received on Mon Mar 08 2004 - 10:55:08 CST