Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: VARCHAR2(1) vs CHAR(1)

Re: VARCHAR2(1) vs CHAR(1)

From: Daniel Morgan <dmorgan_at_exesolutions.com>
Date: Tue, 30 Jul 2002 15:02:26 GMT
Message-ID: <3D46AA35.C9CD2F65@exesolutions.com>


Norman Dunbar wrote:

> Morning Stjepan,
>
> I recently discovered a set of tables in one of our Uniface repositories
> which had been set up with CHAR and not with VARCHAR2. This caused
> problems when using Uniface to generate table creation scripts as it
> assums that the destination database will be in the same format as the
> developers source code repository. (Major PITA !) So I was asked to
> change all the CHARs to VARCHARS.
>
> I exported the tables, dropped them and ran the script to recreate them
> in VARCHAR2. I then imported the old CHAR data - and all hell broke
> loose :o(
> The CHARS imported complete with their trailling spaces so I had a
> couple of hours running scripts to find all the VARCHAR2 fields which
> had trailing spaces and RTRIM them out. Much fun !!
>
> If you do change a table from CHAR to VARCHAR2 - just be careful !!!
>
> Regards,
> Norman.
>
> -------------------------------------
> Norman Dunbar
> Database/Unix administrator
> Lynx Financial Systems Ltd.
> mailto:Norman.Dunbar_at_LFS.co.uk
> Tel: 0113 289 6265
> Fax: 0113 289 3146
> URL: http://www.Lynx-FS.com
> -------------------------------------
>
> -----Original Message-----
> From: Stjepan Brbot [mailto:stjepan.brbot_at_zg.hinet.hr]
> Posted At: Sunday, July 28, 2002 10:40 PM
> Posted To: server
> Conversation: VARCHAR2(1) vs CHAR(1)
> Subject: Re: VARCHAR2(1) vs CHAR(1)
>
> If I change VARCHAR2(1) into CHAR(1) column type in table, what could
> happened? Will the application (expecting the VARCHAR2(1) column type)
> work correctly anymore by automatically converting the value from
> CHAR(1) into VARCHAR2(1) type?
>
> --
>
> Stjepan Brbot

A few comments on this thread.

  1. When converting CHAR to VARCHAR always TRIM or RTRIM all CHAR fields.
  2. I have, for my students, benchmarked the performance difference between CHAR and VARCHAR. And if there is one it is well below the visible threshold of the performance difference caused by reading files organized as chaotically as they are on a hard disk.

If one is using CHAR for performance they are wasting their time. The TRIM or RTRIM costs more than any possible saving. And if the field is a single character one might just consider using an integer.

Daniel Morgan Received on Tue Jul 30 2002 - 10:02:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US