Re: CHAR vs VARCHAR2

From: Jill Stephenson <jill_at_tortuga.com.au>
Date: 1995/06/22
Message-ID: <3sboc0$6aj_at_dingo.cc.uq.oz.au>#1/1


Rod Fergusson (rod.fergusson_at_canada.attgis.com) wrote: -- snip --
: The big difference and problem that some people have run into
: with the CHAR vs. VARCHAR2 is in the evaluation of comparisons
: between these fields. One compares blank padded, and the other
: compares non-padded. It will depend on your applications,
: whether you would be affected by this or not.
:
: Hope this helps.... Sandra Fergusson

Apparently Oracle recommend using CHAR for performance reasons in some circumstaances.

The argument being along the lines of: a insert into a table with char fields results in a fixed amount of space being allocated for a row, so that when a delete takes place a subsequent insert can fill the previously vacated space. However, a insert into a table with varchar2 fields only results in 'enough' space being allocated for a row, so that if a row is deleted a subsequent insert may not be able to use the previouslly vacated space if the values in the fields are larger and chaining will result.

While I can see the logic of this I would have though the trade off of the extra storage capacity would outweigh this, as well as the issue of varchar2 being the type that is used to conform to ANSI standards.

Also, from a quick look at Oracle's internal tables they seem to use varchar2 - so Oracle doesn't follow it's own recommendations!

Would any DBA's or anyone with more detailed information on this issue care to comment?

Thanks,

Jill Received on Thu Jun 22 1995 - 00:00:00 CEST

Original text of this message