Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: VARCHAR2(1) vs CHAR(1)

Re: VARCHAR2(1) vs CHAR(1)

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Wed, 31 Jul 2002 22:23:11 +1000
Message-ID: <lxQ19.49544$Hj3.148356@newsfeeds.bigpond.com>


Hee Hee, Ok let me rephrase.

The use of CHARs *may* help to reduce the incidence of row migration (although as I said elsewhere I'm not a particular fan of chars).

Thanks as always for the clarification.

Cheers

Richard
"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:ai8h0m$lje$1_at_lust.ihug.co.nz...
>
> "Marco Muracchioli" <reply.on_at_group.please> wrote in message
> news:HcD19.160194$vm5.5374009_at_news2.tin.it...
> > Thank Richard. :)
> > Just two note:
> >
> > > Note it's actually possible for Char to worsen performance rather than
> > > improve it (although not so much in your example). Why, because large
> char
> > > values potentially waste space.
> >
> > I refer to char with size of 1 or 2 (as in original message).
> >
> > > The nice thing about chars are that updates don't cause char values to
> > > increase in length. If updates don't affect row size then row
migration
> will
> > > not be a problem and potential issues there can be avoided.
> >
> > Avoiding row chaining is a good reason, that I didn't consider before.
> > Learning never stop. :)
>
> Richard is correct that the use of CHARs will mean that an update of that
> field will not induce row growth, and hence will not induce row migration
> (row chaining has nothing to do with this, and is a completely separate
> issue). Unfortunately, that does NOT mean you should use CHARs and row
> migration will be a thing of the past. For a start, number data types are
> inherently variable in length, and thus can cause row migration. The
absence
> of NOT NULL constraints also mean that a field is variable in length, and
> could cause row migration.
>
> The use of CHAR to avoid row migration therefore only makes sense if you
> also promise never to use the NUMBER data type, and also promise to
decalre
> every single column NOT NULL. Unless you do both those things in addition,
> you can use CHARs all you want, and you will *still* remain at risk of row
> migration.
>
> Regards
> HJR
>
> >
> > thank again.. :)
> >
> > Marco
> >
> >
>
>
Received on Wed Jul 31 2002 - 07:23:11 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US