From: Connor McDonald <connor_mcdonald@yahoo.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: char vs. varchar?
Date: Fri, 02 Feb 2001 19:33:09 +0800
Organization: Customer of Energis Squared
Lines: 66
Message-ID: <3A7A9AF5.26AA@yahoo.com>
References: <eS3e6.269797$hD4.65146495@news1.rdc1.mi.home.com> <Y95e6.350392$U46.10584292@news1.sttls1.wa.home.com> <hBpe6.7$J2.2154@news.deakin.edu.au> <95db4o$1mv$1@nnrp1.deja.com>
Reply-To: connor_mcdonald@yahoo.com
NNTP-Posting-Host: modem-121.cleaner-wrasse.dialup.pol.co.uk
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Trace: newsg3.svr.pol.co.uk 981142353 7652 62.136.246.121 (2 Feb 2001 19:32:33 GMT)
NNTP-Posting-Date: 2 Feb 2001 19:32:33 GMT
X-Complaints-To: abuse@theplanet.net
X-Mailer: Mozilla 3.01 (Win95; I)
To: David Fitzjarrell <oratune@aol.com>


David Fitzjarrell wrote:
> 
> In our last gripping episode "John Smith" <john@smith.com> wrote:
> > Is there any reason to choose CHAR(1) over VARCHAR2(1)?
> >
> > Phil Carter.
> >
> > "Jim Kennedy" <kennedy-family@home.com> wrote in message
> > news:Y95e6.350392$U46.10584292@news1.sttls1.wa.home.com...
> > > Char - fixed length
> > > varchar - variable length
> > >
> > > use varchar.
> > > Jim
> > > "Tom Weng" <tomweng@home.com> wrote in message
> > > news:eS3e6.269797$hD4.65146495@news1.rdc1.mi.home.com...
> > > > Could somebody explain to me how Oracle store char datatype data
 vs.
 varchar
> > > > datatype internally?
> > > >
> > > > Whis is the pro/con in-term of performing update on a char column
 v.s
> > > > varchar column?
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
> 
> Since Jim did such an excellent job of explaining the difference...
> 
> There is overhead associated with VARCHAR2 fields, and for
> multicharacter columns this overhead is usually minimal relative to the
> length of the column. Since a VARCHAR2(1) column can hold, at most, one
> character the overhead to maintain this variable column width is
> unnecessary, in my opinion.  I would rather choose a CHAR(1) column and
> live with the fixed storage than declare a VARCHAR2(1) column and,
> through some fluke of data in the record, migrate the row simply
> because I decided to update that VARCHAR2(1) column.  True, that
> occurrence would be rare, for the most part, but it could happen.
> 
> I believe it is wasted space to declare a VARCHAR2(1) column when a CHAR
> (1) will suffice.
> 
> --
> David Fitzjarrell
> Oracle Certified DBA
> 
> Sent via Deja.com
> http://www.deja.com/

I've not dumped blocks to prove it, but I'm pretty sure both CHAR and
VARCHAR2 are stored in the same way, namely length byte(s) followed by
data, so no space saving would be made.

HTH
-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)

"Some days you're the pigeon, some days you're the statue"