| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Re: char(1) VS varchar2(1)
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:
3 c1 number, 4 c2 number, 5 c3 number, 6 c4 number, 7 c5 number
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 ------------------- ----- ------------------- ----- -----------------------------
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 06010020000
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 tanel.poder.003_at_mail.ee 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" <ORACLE-L_at_fatcity.com>
> 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"
>>> <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).
>>
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: tim_at_sagelogix.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rajesh Dayal INET: rajesh_at_iitcoman.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 Sun Nov 02 2003 - 22:49:25 CST
 |
 |