Re: char vs. varchar?

From: David Fitzjarrell <oratune_at_aol.com>
Date: Mon, 05 Feb 2001 04:29:02 GMT
Message-ID: <95la6b$v2q$1@nnrp1.deja.com>

In our last gripping episode connor_mcdonald_at_yahoo.com wrote:
> David Fitzjarrell wrote:
> >
> > In our last gripping episode "John Smith" <john_at_smith.com> wrote:
> > > Is there any reason to choose CHAR(1) over VARCHAR2(1)?
> > >
> > > Phil Carter.
> > >
> > > "Jim Kennedy" <kennedy-family_at_home.com> wrote in message
> > > news:Y95e6.350392$U46.10584292_at_news1.sttls1.wa.home.com...
> > > > Char - fixed length
> > > > varchar - variable length
> > > >
> > > > use varchar.
> > > > Jim
> > > > "Tom Weng" <tomweng_at_home.com> wrote in message
> > > > news:eS3e6.269797$hD4.65146495_at_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"
>

I shall stand corrected. I have created like tables, inserted like data and dumped blocks and can find no additional space consumed by the VARCHAR2 field. After excavating my stored library of Oracle course texts I found a reference to such overhead in an Oracle 7 DBA reference, although not from the printer. Apparently the instructor assured us that there was such overhead and my notes bear this out. These notes read as follows:

"For each VARCHAR2 column add 1 byte, and add 2 bytes to the row. This is to account for the overhead for storing such columns in the database."

I also have a handout with sample row size calculations including such figures from the instructor (whose name I cannot remember) so I must feel that my notes from the class were not in error.

I apologise for any confusion my earlier response may have created.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com
http://www.deja.com/

Received on Sun Feb 04 2001 - 22:29:02 CST