From daemon Wed Feb 7 06:07:19 1996 Received: from ccvm.sunysb.edu by alice.jcc.com; (5.65v3.0/1.1.8.2/01Aug94-0142PM) id AA13619; Wed, 7 Feb 1996 06:07:15 -0500 Message-Id: <9602071107.AA13619@alice.jcc.com> Received: from CCVM.SUNYSB.EDU by ccvm.sunysb.edu (IBM VM SMTP V2R3) with BSMTP id 2944; Wed, 07 Feb 96 05:47:50 EST Received: from CCVM.SUNYSB.EDU (NJE origin LISTSERV@SBCCVM) by CCVM.SUNYSB.EDU (LMail V1.2a/1.8a) with BSMTP id 7833; Wed, 7 Feb 1996 05:47:49 -0500 Date: Wed, 7 Feb 1996 10:45:48 GMT Reply-To: "ORACLE database mailing list." Sender: "ORACLE database mailing list." From: Jonathan Lewis Subject: Re: CHAR() vs VARCHAR@() X-To: ORACLE-L@ccvm.sunysb.edu To: Multiple recipients of list ORACLE-L > Paul T. Allison wrote: > Actually, every field with length greater than 250 takes > 2 extra space bytes. This could be significant in a large table. > > Ian MacGregor replied: > > Why 250, not 255. One can certainly store '2000' in two bytes. What's the > purpose of the third?... To store whether the column lenght is stored in > one or two bytes ? Exactly. Lengths are stored as byte or 3 bytes. However, Oracle will only use the three bytes ones the actual stored length of the data exceeds 250 bytes. When this occurs, the first byte is set to 254 to indicate that the next two bytes are the actual data length. A varchar2() holding 250 bytes will have its length given by: FA A varchar2() holding 251 bytes will have it length given by: FE 00 FB A varchar2() holding 258 bytes will have it length given by: FE 01 02 Since CHAR() is always space padded, any declaration of char(N) where N > 250 will result in a 'redundant' FE 00 being prepended to the 'actual' length byte. Personally, with this algorithm, I would have expected char() (and raw()) to be restricted to 250 bytes, or allowed to be the same maximum as varchar(2). Interestingly, there is an item somewhere in one of the manuals that states the break from 1 to 3 bytes occurs at 127 bytes, and I think that I tested this out on one version of Oracle some time ago where it was true. Can't rememebr the version though. Most recent tests on: 7.0.16, HP-UX --- Jonathan Lewis ora_mail@jlcomp.demon.co.uk