Re: Underlying Implementation of VARCHAR data types

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Mon, 08 Mar 2004 13:05:35 -0800
Message-ID: <1078779903.372848@yasure>

Comments in-line.

Mike L. Bell wrote:

>>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.

There really is no benefit. Take for example state abbreviations. You define it as CHAR2 I define it as VARCHAR2. In either case someone can put in a single character. If, rather than 'NY' some puts in 'N' with VARCHAR2 I see 'N' and what you see is 'N' but is actually 'N '. Which misleads more often than not.

> 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?

Once again ... put a 2 after VARCHAR. There is no difference between the scenario you describe with CHAR or VARCHAR. One can not overflow any more than the other.

> Do I also gain savings on an index with a VARCHAR2 types?

Yes.

> 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.

If a column stores last names in a VARCHAR2(30) Morgan is 'Morgan' in both the column and in the index. If in a CHAR(30) it is

'Morgan                        ' in both the column and the index.

> 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

HTH

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Mon Mar 08 2004 - 15:05:35 CST