Oracle基本数据类型存储格式浅析（一）—（五）

http://yangtingkun.itpub.net/post/468/25748

Oracle基本数据类型存储格式浅析（一）——字符类型
发表人:yangtingkun | 发表时间: 2004年十二月09日, 23:47
前一阵看完文档，对oracle的基本数据类型的存储格式有了一些了解，最近有做了一些测试进行了验证。
打算整理总结一下，这一篇主要说明字符类型的存储格式。主要包括char、varchar2和long等几种类型。
 
SQL> create table test_char (char_col char(10), varchar_col varchar2(10), long_col long);
表已创建。
SQL> insert into test_char values (''''abc'''', ''''123'''', '''',fd'''');
已创建 1 行。
SQL> commit;
提交完成。
SQL> select rowid from test_char;
ROWID
------------------
AAAB3LAAFAAAAAgAAA
根据rowid的定义规则，第7～9位是表示的是数据文件，F表示5，而10～15位表示的是在这个数据文件中的第几个BLOCK，g表示32。（rowid编码相当于64进制。用A~Z a~z 0~9 + /共64个字符表示。A表示0，B表示1，……，a表示26，……，0表示52，……，+表示62，/表示63。）
我们根据计算的结果去dump这个block。
SQL> ALTER SYSTEM DUMP DATAFILE 5 BLOCK 32;
系统已更改。
打开产生的trace文件：
data_block_dump,data header at 0x3421064
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x03421064
bdba: 0x01400020
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f82
avsp=0x1f6e
tosp=0x1f6e
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f82
block_row_dump:
tab 0, row 0, @0x1f82
tl: 22 fb: --H-FL-- lb: 0x1  cc: 3
col  0: [10]  61 62 63 20 20 20 20 20 20 20
col  1: [ 3]  31 32 33
col  2: [ 3]  2c 66 64
end_of_block_dump
End dump data blocks tsn: 5 file#: 5 minblk 32 maxblk 32
观察dump出来的结果，可以发现以下几点：
1.对于每个字段，除了保存字段的值以外，还会保存当前字段中数据的长度。而且，oracle显然没有把字段的长度定义或类型定义保存在block中，这些信息保存在oracle的数据字典里面。
2. 根据dump的结果，可以清楚的看到，字符类型在数据库中是以ascii格式存储的。
SQL> select chr(to_number(''''61'''', ''''xx'''')) from dual;
CH
--
a
3.char类型为定长格式，存储的时候会在字符串后面填补空格，而varchar2和long类型都是变长的。
SQL> SELECT DUMP(CHAR_COL, 16) D_CHAR FROM TEST_CHAR;
D_CHAR
-------------------------------------------------------------
Typ=96 Len=10: 61,62,63,20,20,20,20,20,20,20
SQL> SELECT DUMP(VARCHAR_COL, 16) D_VARCHAR2 FROM TEST_CHAR;
D_VARCHAR2
-------------------------------------------------------------
Typ=1 Len=3: 31,32,33
SQL> SELECT DUMP(LONG_COL, 16) D_VARCHAR2 FROM TEST_CHAR;
SELECT DUMP(LONG_COL, 16) D_VARCHAR2 FROM TEST_CHAR
            *
ERROR 位于第 1 行:
ORA-00997: 非法使用 LONG 数据类型
由于DUMP不支持LONG类型，因此我们使用了alter system dump block的方式，通过比较两种方式得到的结果，发现DUMP()函数不但方便，结果清晰，而且指出了进行DUMP的数据类型，在以后的例子中，除非必要的情况，否则都会采用DUMP()函数的方式进行说明。
下面看一下插入中文的情况，首先看一下数据库的字符集
SQL> select name, value$ from sys.props$ where name like ''''%CHARACTERSET%'''';
NAME                           VALUE$
------------------------------ ------------------------------
NLS_CHARACTERSET               ZHS16GBK
NLS_NCHAR_CHARACTERSET         AL16UTF16
SQL> insert into test_char values (''''定长'''', ''''变长'''', null);
已创建 1 行。
SQL> SELECT DUMP(CHAR_COL, 16) D_CHAR FROM TEST_CHAR;
D_CHAR
----------------------------------------------------------------
Typ=96 Len=10: 61,62,63,20,20,20,20,20,20,20
Typ=96 Len=10: b6,a8,b3,a4,20,20,20,20,20,20
SQL> SELECT DUMP(VARCHAR_COL, 16) D_VARCHAR2 FROM TEST_CHAR;
D_VARCHAR2
----------------------------------------------------------------
Typ=1 Len=3: 31,32,33
Typ=1 Len=4: b1,e4,b3,a4
根据dump结果，可以清楚的看出，普通英文字符和标点用一个字节表示，而中文字符或中文标点需要两个字节来表示。
下面，对比一下nchar和nvarchar2与char、varchar2类型有什么不同。
SQL> create table test_nchar (nchar_col nchar(10), nvarchar_col nvarchar2(10));
表已创建。
SQL> insert into test_nchar values (''''nchar定长'''', ''''nvarchar变长'''');
已创建 1 行。
从这里已经可以看出一些不同了，如果按照刚才中文的计算方法，''''nvarchar变长''''的长度是8+2*2=12已经超过了数据类型定义的大小，可是为什么插入成功了？
还是dump一下看看结果吧。
SQL> select dump(nchar_col, 16) from test_nchar;
DUMP(NCHAR_COL,16)
--------------------------------------------------------------
Typ=96 Len=20: 0,6e,0,63,0,68,0,61,0,72,5b,9a,95,7f,0,20,0,20,0,20
SQL> select dump(nvarchar_col, 16) from test_nchar;
DUMP(NVARCHAR_COL,16)
--------------------------------------------------------------
Typ=1 Len=20: 0,6e,0,76,0,61,0,72,0,63,0,68,0,61,0,72,53,d8,95,7f
这下就明白了，虽然仍然是采用ascii码存储，但是nchar使用的AL16UTF16字符集，编码长度变为2个字节。这样中文使用两个字节，对于可以用一个字节就表示的英文字符，采用了高位补0的方式凑足2位，这样，对于采用AL16UTF16字符集的nchar类型，无论中文还是英文都用2位字符表示。因此''''nvarchar变长''''的长度是10，并没有超过数据类型的限制。
==============================================================
Oracle基本数据类型存储格式浅析（二）——数字类型
发表人:yangtingkun | 发表时间: 2004年十二月14日, 22:57
这篇文章主要描述NUMBER类型的数据和如何在数据库中存储的。
Oracle的NUMBER类型最多由三个部分构成，这三个部分分别是最高位表示位、数据部分、符号位。其中负数包含符号位，正数不会包括符号位。另外，数值0比较特殊，它只包含一个数值最高位表示位80，没有数据部分。
正数的最高位表示位大于80，负数的最高位表示位小于80。其中一个正数的最高位是个位的话，则最高位表示位为C1，百位、万位依次为C2、C3，百分位、万分为依次为C0、BF。一个负数的最高位为个位的话，最高位表示位为3E，百位、万位依次为3D、3C，百分位、万分位依次为3F、40。
数据部分每一位都表示2位数。这个两位数可能是从0到99，如果是数据本身是正数，则分别用二进制的1到64表示，如果数据本身是负数，则使用二进制65到2表示。
符号位用66表示。
上面的这些是我通过DUMP结果总结出来的，对于上面提到的这些关系常数，Oracle之所以这样选择是有道理的，我们后面根据例子也可以推导出来，而且会进一步说明为什么会采用这种方式表示。这里列出的意思是使大家先对NUMBER类型数据有一个大概的了解。
下面我们通过一个例子详细说明：
 
SQL> CREATE TABLE TEST_NUMBER (NUMBER_COL NUMBER);
表已创建。
SQL> INSERT INTO TEST_NUMBER VALUES (0);
已创建 1 行。
SQL> INSERT INTO TEST_NUMBER VALUES (1);
已创建 1 行。
SQL> INSERT INTO TEST_NUMBER VALUES (2);
已创建 1 行。
SQL> INSERT INTO TEST_NUMBER VALUES (25);
已创建 1 行。
SQL> INSERT INTO TEST_NUMBER VALUES (123);
已创建 1 行。
SQL> INSERT INTO TEST_NUMBER VALUES (4100);
已创建 1 行。
SQL> INSERT INTO TEST_NUMBER VALUES (132004078);
已创建 1 行。
SQL> INSERT INTO TEST_NUMBER VALUES (2.01);
已创建 1 行。
SQL> INSERT INTO TEST_NUMBER VALUES (0.3);
已创建 1 行。
SQL> INSERT INTO TEST_NUMBER VALUES (0.00000125);
已创建 1 行。
SQL> INSERT INTO TEST_NUMBER VALUES (115.200003);
已创建 1 行。
SQL> INSERT INTO TEST_NUMBER VALUES (-1);
已创建 1 行。
SQL> INSERT INTO TEST_NUMBER VALUES (-5);
已创建 1 行。
SQL> INSERT INTO TEST_NUMBER VALUES (-20032);
已创建 1 行。
SQL> INSERT INTO TEST_NUMBER VALUES (-234.432);
已创建 1 行。
SQL> COMMIT;
提交完成。
SQL> COL D_NUMBER FORMAT A50
SQL> SELECT NUMBER_COL, DUMP(NUMBER_COL, 16) D_NUMBER FROM TEST_NUMBER;
NUMBER_COL D_NUMBER
---------- --------------------------------------------------
         0 Typ=2 Len=1: 80
         1 Typ=2 Len=2: c1,2
         2 Typ=2 Len=2: c1,3
        25 Typ=2 Len=2: c1,1a
       123 Typ=2 Len=3: c2,2,18
      4100 Typ=2 Len=2: c2,2a
 132004078 Typ=2 Len=6: c5,2,21,1,29,4f
      2.01 Typ=2 Len=3: c1,3,2
        .3 Typ=2 Len=2: c0,1f
 .00000125 Typ=2 Len=3: be,2,1a
115.200003 Typ=2 Len=6: c2,2,10,15,1,4
        -1 Typ=2 Len=3: 3e,64,66
        -5 Typ=2 Len=3: 3e,60,66
    -20032 Typ=2 Len=5: 3c,63,65,45,66
  -234.432 Typ=2 Len=6: 3d,63,43,3a,51,66
已选择15行。
 
    下面根据例子得到的结果，对每行进行说明。首先说明两点基本的。DUMP函数返回的TYPE＝2表示DUMP的数据类型是NUMBER，LENGTH＝N表示数值在数据库中存储的长度是N。
1.DUMP(0)的结果是0x80，在前面已经提到，0只有高位表示位，没有数据位。由于0的特殊，既不属于正数，也不属于负数，因此使用高位表示位用80表示就足够了，不会和其它数据冲突，Oracle出于节省空间的考虑将后面数据部分省掉了。但是为什么Oracle选择0x80表示0呢？我们知道正数和负数互为相反数，每个正数都有一个对应的负数。因此如果我们要使用编码表示数值，则表示正数和负数的编码应该各占一半，这样才能保证使Oracle表示数据范围是合理的。而0x80的二进制编码是1000 0000，正好是一个字节编码最大值的一半，因此，Oracle选择0x80来表示0，是十分有道理的。
2.DUMP(1)的结果是0xc102，0xc1表示了最高位个位，0x2表示数值是1。首先，Oracle为什么用C1表示个位呢？其实，道理和刚才的差不多。采用科学计数法，任何一个实数S都可以描述为A.B×10n，A表示整数部分，B表示小数部分，而N表示10的指数部分。当S大于1时，N大于等于0，S小于1时，N小于0。也就是说，采用指数的方式表示，N大于0和N小于0的情况各占一半左右时，Oracle所表示的范围最广。因此，Oracle选择了C1表示个位是最高位的情况。
SQL> SELECT TO_CHAR(ROUND(TO_NUMBER(''''81'''', ''''XXX'''') + (TO_NUMBER(''''FF'''', ''''XXX'''') - TO_NUMBER(''''81'''', ''''XXX'''') + 1)/2), ''''XX'''') FROM DUAL;
TO_
---
 C1
 
为什么ORACLE使用0x2表示1，而不直接使用0x1表示1呢？Oracle每个字节表示2位数，因此对于这个2位数，出现的可能是0～99共100种可能，问题出在0这里。Oracle底层是用C语言实现的，我们知道二进制0在C语言中用作字符串终结符，Oracle为了避免这个问题，因此使用了0x1表示0，并依次类推，使用0x64表示99。
3.DUMP(2)的结果是0xc103。
4.DUMP(25)的结果是0xc11a。前面提到，数据部分是以2位为最小单位保存的。因此对于25来说，最高位表示位仍然是个位，个位上的值是25，根据上面推出的规则，25在存储为0xc11a。
SQL> SELECT TO_CHAR(25 + 1, ''''xx'''') FROM DUAL;
TO_
---
 1a
 
5.DUMP(123)的结果是0xc20218。由于123最高为是百位，所以最高位表示位为0xc2，百位上是1，用0x02表示，个位上是23，用0x18表示。
6.DUMP(4100)的结果是0xc22a。
注意一点，如果数字最后数位上如果是0，Oracle出于节省空间的考虑不会存储。比如：4100只保存百位上的41，12000000只保存百位位上的12，512000只保存万位上的51和百位上的20。
7.DUMP(132004078)的结果是0xc5022101294f。最高位是亿位，因此用0xC5表示，亿位上是1用0x02表示，百位位上是32用0x21表示，万位上是0用0x01表示，百位上是40用0x29表示，个位上78用0x4F表示。
注意：中间数位上的0不能省略。
8.DUMP(2.01)的结果是0xc10302。最高位是个位用0xC1表示，个位上是2用0x03表示，百分位上是1用0x02表示。
注意：个位下面一位是百分位不是十分位。
9.DUMP(0.3)的结果是0xc01f。最高位是百分位，使用0xC0表示，百分位上是30用0x1F表示。
10.DUMP(0.00000125)的结果是0xbe021a。最高位是百万分位，用0xBE表示，最高位上的1用0x02表示，25用0x1a表示。
11.DUMP(115.200003)的结果是0xc20210150104。
12.DUMP(-1)的结果是0x3e6466。最高位个位，用0x3E表示，64表示个位上是1，66是符号位，表示这个数是负数。
负数和

[1] [2] [3] [4] [5]  下一页

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18