CHAR vs VARCHAR, was: VARCHAR2(1) vs CHAR(1) CORRECTED!

One of the more interesting tidbits in the Oracle Internals seminars was the information on how Oracle handles row updates within a datablock. Apparently if an update causes the size of a column to grow then the data in the row will be written to a new location in the block. If the size remains the same (as it must with type char) then the new data is written to the same location. Thus there is slightly more overhead involved in updating a varchar column than a char column if you are updating to a large size.

I'm sure the various internals experts will correct me if I got any of that wrong... :)

-----Original Message-----
From: MTPConsulting_at_aol.com [mailto:MTPConsulting_at_aol.com] Sent: Wednesday, June 28, 2000 4:18 PM
To: Multiple recipients of list ORACLE-L Subject: Re: VARCHAR2(1) vs CHAR(1) CORRECTED!

Ah, excuse me, but I was wrong on this. It was politely pointed out to me that this was in dispute, and so we did an experiment this morning to check this out. This was done on Oracle 8.1.6 on NT, but I have no reason to suspect it is release or platform specific.

We created two tables, each with one column, the first with VARCHAR2(1) and the second CHAR(1). We inserted a row and analyzed the tables. The average

row size was 5 bytes in both cases. So, contrary to what I have believed for some time, there is NO difference between VARCHAR2(1) and CHAR(1). A length field is apparently used for both VARCHAR2 and CHAR.

(Just for benefit of newer Oracle users on the list, for lengths greater than
1, this will not be true, as CHAR is a fixed length type and spaces will be saved. So CHAR(2) with one character saved is definitely larger than VARCHAR2(2) with one character saved.)

Mea culpa!

Marc Perkowitz
MTP Systems Consulting

In a message dated 6/27/00 1:14:29 PM Central Daylight Time, MTPConsulting writes:

<< That's correct.  

 Marc Perkowitz
 MTP Systems Consulting  

 In a message dated 6/27/00 1:16:41 AM Central Daylight Time, KeesH_at_discoveryhealth.co.za writes:  

 << Can someone please tell me which datatype uses up more diskspace : a   VARCHAR2(1) or a CHAR(1) field. A VARCHAR2 field supposedly stores the data
  in the field plus the field length (1 + 1 byte) while a CHAR field only   stores the data (1 byte).   

  Thanks >>
>>

-- 
Author: 
  INET: MTPConsulting_at_aol.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).