Extremely precious info. 
Thanks a lot for sharing this !!!!!!!

Rajesh Dayal
Senior Oracle DBA (OCP 8,8i,9i)
International Information Technology Company LLC


 -----Original Message-----
Sent:   Monday, November 03, 2003 7:14 AM
To:     Multiple recipients of list ORACLE-L
Subject:        Fwd: Re: char(1) VS varchar2(1)

Trailing columns with NULL values do not occupy any space, not even a length
byte.

Non-trailing columns with NULL values have a constant value of 0xFF (255) in
the length byte consuming just the one byte.

Column values with a length of 0-254 bytes have one length byte, and values
with a length greater than 254 bytes have 3 bytes, where the first byte is
the constant 0xFE (254) and the remaining 2 bytes actually have the length.

For example:

=============================================
SQL> create table xyz
  2  (
  3     c1 number,
  4     c2 number,
  5     c3 number,
  6     c4 number,
  7     c5 number
  8  ) tablespace tools;

Table created.

SQL> insert into xyz values (1, null, 1, null, 999999);

1 row created.

SQL> insert into xyz values (2, null, 2, null, 999999);
1 row created.

SQL> select dump(c1) c1, dump(c2) c2, dump(c3) c3,
  2  dump(c4) c4, dump(c5) c5 from xyz;

C1                  C2    C3                  C4    C5
------------------- ----- ------------------- -----
-----------------------------
Typ=2 Len=2: 193,2  NULL  Typ=2 Len=2: 193,2  NULL  Typ=2 Len=4:
195,100,100,100
Typ=2 Len=2: 193,3  NULL  Typ=2 Len=2: 193,3  NULL  Typ=2 Len=4:
195,100,100,100

SQL> select file_id,block_id,blocks from dba_extents where
 2   segment_name='XYZ';

   FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         2       5857          8

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

=============================================

OK, the checkpoint made sure that everything was flushed to the datafile.
Now, we can look at things using the UNIX "od" command:

$ dd if=/u01/oradata/PRD/tools_02.dbf bs=8192 skip=5858 count=1 | \
= od -x

0000000     0602    0000    0080    16e2    002f    5a39    0000    0104
0000020     0809    0000    0100    0000    0000    217b    002f    5a03
0000040     0000    90e4    0002    0300    0000    0000    0008    002f
0000060     0000    048b    0100    0040    0564    1600    0002    0000
0000100     0000    0000    0000    0000    0000    0000    0000    0000
0000120     0000    0000    0000    0000    0000    0000    0001    0002
0000140     ffff    0016    1f80    1f6a    1f6a    0000    0002    1f90
0000160     1f80    0000    0000    0000    0000    0000    0000    0000
0000200     0000    0000    0000    0000    0000    0000    0000    0000
*
0017720     0000    0000    0000    0000    0000    0000    2c01    0502
0017740     c103    ff02    c103    ff04    c364    6464    2c01    0502
0017760     c102    ff02    c102    ff04    c364    6464    5a39    0601
0020000

OK, now remember that data rows fill from the end of the block, working
backwards, not from the beginning.

So, at the end of the block, we see the 4-byte block tailer ("5a39 0601").
Just before that, we see the first row:

    2c 01 05 02 c1 02 ff 02 c1 02 ff 04 c3 64 64 64
   +--------+--------+--+--------+--+--------------+
    row hdr  c1       c2 c3       c4 c5

Then, just prior to that, we see the second row:


    2c 01 05 02 c1 03 ff 02 c1 03 ff 04 c3 64 64 64
   +--------+--------+--+--------+--+--------------+
    row hdr  c1       c2 c3       c4 c5

The third byte (0x05) of each row indicates that five columns comprise the
row.

The numbers are represented in "100s-complement" which is a form of base-100
arithmetic.  For column C1, the first byte (hex 0x02) is the length byte.
The next byte (hex 0xC1 or decimal 193) is both the sign and the exponent,
while the next byte (hex 02 again) is the mantissa or significant digits in
100s-complement.  To avoid a value of 0x0, they add one to the value, so the
value of "1" is represented as 0x02.

So in the first row, the value of "1" in column C1 is shown by the three
bytes 0x02c102.  The null value in column C2 is shown by the one byte 0xff.
The value of "1" in column C3 is shown by the three bytes 0x02c102.  The
null value in column C4 is shown by the one byte 0xff.  The value of
"999999" in column C5 is shown by the five bytes 0x04c3646464.

-Tim


on 11/2/03 4:44 PM, Tanel Poder at [EMAIL PROTECTED] wrote:

> Hi!
> 
> Just for the record, every column in a table has a length byte (or three,
> depending on column size). This works so even in clusters, where rows are
> split vertically, but column structures remain the same.
> 
> Tanel.
> 
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Friday, October 31, 2003 7:04 PM
> 
> 
>> 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"
>>> <[EMAIL PROTECTED]>
>>> To: Multiple recipients of list ORACLE-L
>>> <[EMAIL PROTECTED]>
>>> 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
>>> [EMAIL PROTECTED]
>>> <mailto:[EMAIL PROTECTED]>
>>> 
>> -- 
>> Please see the official ORACLE-L FAQ: http://www.orafaq.net
>> -- 
>> Author: Stephane Faroult
>>   INET: [EMAIL PROTECTED]
>> 
>> 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: [EMAIL PROTECTED] (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).
>> 
> 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rajesh Dayal
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

Reply via email to