Re: VARCHAR2(1) vs CHAR(1)

"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 - 06:18:05 CDT