Re: VARCHAR2 vs. CHAR

No... I have eperience where Oracle though it was violating a unique index becuase the table columns where defined as CHAR instead of VARCHAR2. One of the other things is that VARCHAR2 does not exist in Oracle 6, it was a new type in Oracle 7.

Watch out for the data conversion. Either export the data, change the columns, truncate the table and re-import the data or you'll need to write a script to trunacte the data in each column (Oracle does not automatically trim the data just becuase you changed the data type - I got burnt the hard way on this)....

John P. Higgins wrote:
>
> How about indexes? I have heard that Oracle works better when the
> indexed column(s) are char rather than varchar2. I don't know what was
> meant by 'better'.
>
> John
>
> Thomas Kyte wrote:
> >
> > A varchar2 field is stored as a data field with a leading length inidicator of
> > either 1 or 2 bytes (depending on the string length) and the data. So, a
> > varchar2(30) with 10 characters in it will take 11 bytes.
> >
> > A char fields is stored as a data field with a leading length inidicator or 1 or
> > 2 bytes (depending on string length - O8 lets chars be 2000 bytes) and the data
> > BLANK PADDED out to the maximum length. So a char(30) will always take either
> > zero bytes (null at the end of the row) or 31 bytes.
> >
> > So a varchar2 is stored varying length and only consumes space for the
> > characters in it. A char is always maxed out to the maximum length and consumes
> > all of the space.
> >
> > On Wed, 10 Dec 1997 12:04:18 -0500, "Roy Chang" <roy_chang_at_mail.amsinc.com>
> > wrote:
> >
> > >
> > >
> > >Hello everbody,
> > >
> > >I had heard that if I set a field to VARCHAR2(XX), XX being less than 35 or
> > >so, that it wouldn't save any space or make a difference. Is this correct?
> > >And if so, should I change the fields that are VARCHAR2 and less than 35 to
> > >CHAR(XX)?
> > >
> > >Any comments would be appreciated.
> > >
> > >Thanks in advance,
> > >
> > >Roy.
> > >
> > >
> > >
> >
> >
> > Thomas Kyte
> > tkyte_at_us.oracle.com
> > Oracle Government
> > Bethesda MD
> >
> > http://govt.us.oracle.com/ -- downloadable utilities
> >
> > ----------------------------------------------------------------------------
> > Opinions are mine and do not necessarily reflect those of Oracle Corporation
> >
> > Anti-Anti Spam Msg: if you want an answer emailed to you,
> > you have to make it easy to get email to you. Any bounced
> > email will be treated the same way i treat SPAM-- I delete it.
Received on Wed Dec 10 1997 - 00:00:00 CST