Re: Difference between CHAR vs VARCHAR

In article <3vinbv$3h4_at_ixnews4.ix.netcom.com>, Chuck Hamilton <chuckh_at_ix.netcom.com> wrote:
>ra_at_ofisa.ch (Yves Raisin) wrote:
>
>>Hi george,
>>In Oracle7 CGAR data type mean fixed length data (the data will be padded with
>>spaces is it's less than column size) and VARCHAR, VARCHAR2 are variable length
>>data i suggest to you to always use VARCHAR2 data type.
>
>Yves,
>
>We've hired a consultant to help us design a data warehouse. He says
>he likes to use CHAR instead of VARCHAR2 in primary key columns. I've
>only been using Oracle myself for under 1 year so I'm relying heavily
>on his expertise. My feeling is the same as yours...why use a datatype
>that's going to waste space? Is there any merit to his idea of using
>CHAR instead of VARCHAR2 for primary ket columns, but VARCHAR2
>everywhere else? If so, what's the reason?
>
>BTW we're running Oracle7 in a distributed database. The master
>database is running on HP-UX 9.04, the slave databases are running on
>Novell Netware 3.12.
>

Aside from forcing the primary hey length to be unchanging, a tactic whose utility I have never tested, this method will cause you endless grief when doing comparisons, manipulating fields with Pro*C or OCI if you happen to use those, and will as well waste space as you have noted.

An example, as previous respondents have noted:

insert into any_table (char_type_column) values ('123') where char_type_column is char(10) results in a stored value of '123 '!!!, not the actual value.

Others may hold differing opinions, but I prefer that the data base return information unchanged from that which was originally placed therein.

As I said, I haven't ever checked for any performance gain (or loss), but aside from holding the primary key length constant, there appears to be no other reason to do this.

I have even seen this technique cited for ALL text columns, not just primary keys.

Avoid it.

Just ensure that the PCTFREE and PCTUSED are appropriate for the table(s) in question.

Regards,

Gary

-- 

 ------------------------------------------------------------
| Gary Gapinski              | email: gapinski_at_lerc.nasa.gov |
| NASA Lewis Research Center | voice: +1 216 433 5251        |
 ------------------------------------------------------------