Re: Difference between CHAR vs VARCHAR

I agree, I hope you're not paying your consultant too much!. You should only use CHAR on columns that are mandatory and that the values all have a fixed length, like Y or N; T or F; M or F; etc...

If your primary key fits this, then fine, otherwise don't use CHAR.

Bob Johnson
Computervision
Sydney, Australia

In article <40fjbp$fmv_at_bytor.lerc.nasa.gov>, gapinski_at_lerc.nasa.gov says...
>
>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 |
> ------------------------------------------------------------
Received on Sun Aug 13 1995 - 00:00:00 CEST