Re: Difference between CHAR vs VARCHAR

From: Oracle FAQ <orafaq_at_bf.rmit.edu.au>
Date: 1995/08/17
Message-ID: <40uek1$lae_at_aggedor.rmit.EDU.AU>#1/1

bobj_at_magna.com.au (Bob Johnson) writes:

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

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

Tables with compound character primary keys will benefit from being CHAR rather than VARCHAR (e.g. if you have migrated keys from a number of other tables), especially if they are the first columns of the table (as primaries should be for neatness) because Oracle doesn't have to rummage around figuring out where the next field starts, etc, etc.

Although, generally I prefer VARCHAR2 to CHAR.

BUT, the big question is WHY do you have character primaries (other than a 2 byte field or the like) ANYWAY. If you use arbitrary INTEGERS

(easy with sequences) as the REAL primaries, you get the advantage of
(1) faster comparisons (processor word at a time vs byte at a time)
(2) they never need to change.  Character stuff out in the real world

is meant for humans, who change their mind about what codes and names should be. (good example, imagine a database with a primary on surname, given name, etc, etc - imagine the locking that happens when a surname changes and the change requires propagating.)

Using arbitrary sequence numbers for primaries makes like simple and elegant. I'll point to the Oracle internal dictionary (SYS.OBJ$, SYS.COL$, etc, etc) as a good example. Elegant. Can you imagine what RENAMEing a table would be like if Oracle migrated owner and table names out to a columns table? (Apart from 90 bytes to compare).

Of course, it is easy to write applications with databases based on integer primaries that APPEAR to the user as if they have character based primaries.

Regards
David

--
Oracle FAQ and archives maintainer
orafaq_at_bf.rmit.edu.au                 (private mail to dtb_at_bf.rmit.edu.au)
http://www.bf.rmit.edu.au/OracleFAQ   ftp://ftp.bf.rmit.edu.au/pub/Oracle
-- NOT TO BE ARCHIVED IN ANY MANNER ON MICROSOFT NETWORK --

Received on Thu Aug 17 1995 - 00:00:00 CEST