Re: VARCHAR2 vs. CHAR

Guy Harrison wrote:
>
> BoB Pierce <rjpierce_at_bga.com> wrote in article <334183EF.4E1B_at_bga.com>...
> > Could someone shed some light on the difference between using CHAR and
> > VARCHAR2 for strings? I understand that VARCHAR2 will be more efficient
> > storage-wise by only using as many characters as the input string
> > requires, but I have also heard that performance may be negatively
> > impacted with VARCHAR2 types because Oracle must look up the length of
> > the strings all the time.
>
> Any small overhead required to lookup string lengths would usually be
> overwhelmed by the reduction in buffer cache efficiency (less rows per
> block) and table scan performance (more blocks to scan) resulting from the
> longer row length.
>
> If performance is your concern, I'd suggest VARCHAR2.
> --
> Guy Harrison
>
> gharriso@werple.net.au || http://werple.net.au/~gharriso || 613 419377964

All the tests I have done fail to reveal *any* significant difference in processing time between cah and varchar2. Remember that varchar2 was originally Oracle's sole implementation for character data and that the code for handling fixed-length char was added later for completeness of implementation of SQL and ISI standards. Since comparisons between strings of different lengths may be non-intuitive for char strings I, too, always use varchar2.

Chrysalis. Received on Fri Apr 04 1997 - 00:00:00 CST