RE: char(1) VS varchar2(1)

From: Stephane Faroult <sfaroult_at_oriolecorp.com>
Date: Fri, 31 Oct 2003 09:04:24 -0800
Message-ID: <F001.005D5351.20031031090424@fatcity.com>

I have believed for a while that a varchar2(1) would have included a 'length byte', making it more wasteful of storage than a char(1) but in fact the two are strictly identical storage-wise :

SQL> create table t(c1 char(1),

  2                 c2 varchar2(1));

Table created.

SQL> insert into t values('A', 'B');

1 row created.

SQL> select vsize(c1), dump(c1), vsize(c2), dump(c2)   2 from T;

 VSIZE(C1)

---

DUMP(C1)

---

 VSIZE(C2)

---

DUMP(C2)

---

         1
Typ=96 Len=1: 65

         1
Typ=1 Len=1: 66

SQL> That said, for the sake of logic I still prefer using CHAR instead of VARCHAR when the length doesn't vary at all - call it autodocumentation. VARCHAR2(1) columns - especially when NOT NULL - are unlikely to vary much in length.

SF

>----- ------- Original Message ------- -----
>From: "Stephane Paquette"
><stephane.paquette_at_standardlife.ca>
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>Sent: Fri, 31 Oct 2003 08:04:27
>
>Hi,
>
>Some people here are telling me that using char(1)
>is better than
>varchar2(1) for a field code.
>I do not see why.
>
>I never used char as it may cause problems when
>doing some comparisons.
>
>Any reasons ?
>
>
>Stephane Paquette
>Administrateur de bases de donnees
>Database Administrator
>Standard Life
>www.standardlife.ca
>Tel. (514) 499-7999 7470 and (514) 925-7187
>stephane.paquette_at_standardlife.ca
><mailto:stephane.paquette_at_standardlife.ca>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriolecorp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).

Received on Fri Oct 31 2003 - 11:04:24 CST