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).
