Re: Newbie what to use char or varchar

<need_a_user_name_at_hotmail.com> wrote in message news:8m469h$fl$1_at_nnrp1.deja.com...
> I was told that char is better than varchar. Is this right?
>
> I am going to have fields that are set to be either 25 50 100 or 150
> characters long. Is there a reason any of these fields should be char
> or varchar? I have allways used varchar but I would like to know what
> is best.
>
> Phillip
>

Varchar2 (there's strictly no such thing as a varchar in Oracle) will save you space per row (because the data only chews up as much space as it actually requires -CHARs take up the whole length, even if they are only a few characters long).

However, use of Varchar2s does mean you are at risk of rows migrating due to subsequent updates making them no longer fit in the one block. To avoid that, you need to set PCTFREE carefully (so, in other words, you save space per row, but at the cost of having to set aside some wasted space per block).

If you get PCTFREE wrong and row migration does occur, you will see serious performance degradation, and it's a pain to fix up.

As a result, I tend to use Varchar2 only on large, static tables where subsequent updates aren't a major problem, and Chars on any table undergoing constant updates. On the other hand, number fields are variable length, too, so if you've got any of those defined for a table undergoing updates, you remain at risk from row migration, whatever character types you go for.

Regards
HJR
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Sun Aug 06 2000 - 00:00:00 CDT