ITPub博客

首页 > 数据库 > Oracle > oracle数据类型与存储结构

oracle数据类型与存储结构

原创 Oracle 作者:sunwgneuqsoft 时间:2007-09-29 09:59:48 0 删除 编辑
Oracle 数据库内建的数据类型主要有如下几种:
。CHAR,NCHAR,VARCHAR2,NVARCHAR2
。NUMBER
。RAW
。LONG,LONG RAW
。DATE
。TIMESTAMP
。INTERVAL
。CLOB,BLOB,NCLOB,BFILE
。ROWID,UROWID
每种不同的数据类型用来存储不同的数据,CHAR 存储字符,NUMBER 存储数值型。此外,每
种数据类型在数据块中的存储结构也是不一样的,他们的存储规则存在很大的区别,下面详
细介绍一下每种数据类型在数据库中的存储结构,以及转化的算法。[@more@]Oracle 数据库内建的数据类型主要有如下几种:
。CHAR,NCHAR,VARCHAR2,NVARCHAR2
。NUMBER
。RAW
。LONG,LONG RAW
。DATE
。TIMESTAMP
。INTERVAL
。CLOB,BLOB,NCLOB,BFILE
。ROWID,UROWID
每种不同的数据类型用来存储不同的数据,CHAR 存储字符,NUMBER 存储数值型。此外,每
种数据类型在数据块中的存储结构也是不一样的,他们的存储规则存在很大的区别,下面详
细介绍一下每种数据类型在数据库中的存储结构,以及转化的算法。
1, 准备知识
SQL> create table test
2 (id varchar2(10),
3 score number(5,2));
Table created
SQL> insert into test values('a',78);
1 row inserted
SQL> insert into test values('b',85);
1 row inserted
SQL> commit;
Commit complete
SQL> select id,dump(id) from test;
ID DUMP(ID)
---------- --------------------------------------------------------------------------------
a Typ=1 Len=1: 97
b Typ=1 Len=1: 98
SQL> select score,dump(score) from test;
SCORE DUMP(SCORE)
------- --------------------------------------------------------------------------------
78.00 TYP=2Len=2: 193,79
85.00 TYP=2Len=2: 193,86
Oracle 在数据块中并不是直接的不加修改的存储我们的数据,而是进行一系列的转化,以
一种更高效的方式来存储。从上面的例子可以看出,字符“a”在数据块中用“97”来表示
的,数值“78”是用“193,79”来存储的。
注:dump 命令可以得到数据在数据库中的存储结构。Dump 的语法为dump(数据,进制),
默认是转化为10 进制的。如果需要转化为16 进制,则语法为dump(数据,16)。
2, CHAR,NCHAR,VARCHAR2,NVARCHAR2
CHAR:字符型,单字节,固定长度,最大为2000 个字符,内部编码96
VARCHAR2:字符型,单字节,可变长度,最大为4000 个字符,内部编码1
NCHAR:字符型,多字节,固定长度,最大为2000 个字符,内部编码96
NARCHAR2:字符型,多字节,可变长度,最大为4000 个字符,内部编码1
1) CHAR
SQL> create table test_char
2 (a1 char(1),
3 a2 char(5));
Table created
SQL> insert into test_char values('a','ab');
1 row inserted
SQL> commit;
Commit complete
SQL> insert into test_char values('b’,’abc’);
1 row inserted
SQL> commit;
Commit complete
SQL> select a1,dump(a1) from test_char;
A1 DUMP(A1)--
--------------------------------------------------------------------------------
a Typ=96 Len=1: 97
b Typ=96 Len=1: 98
SQL> select a2,dump(a2) from test_char;
A2 DUMP(A2)
----- --------------------------------------------------------------------------------
ab Typ=96 Len=5: 97,98,32,32,32
abc Typ=96 Len=5: 97,98,99,32,32
从dump 的结果可以看到:
。CHAR 的类型编码Typ 为96
。Len 表示该数据在数据库中的存储长度,char(1)为1,而char(5)为5
。“a”在数据库的存储格式为“97”,“b”为“98”
。“ab”在数据库的存储格式为“97,98,32,32,32”
。“abc”在数据库的存储格式为“97,98,99,32,32”
结论:
1, CHAR 的类型编码Typ 为96
2, CHAR 在数据库中按固定长度存储,不足的为用空格补齐(chr(32))
3, 转化规则
(1) 单字节字符:数据库中存储的为数据的ascii 码(1 个字节)
SQL> select ascii('a') from dual;
ASCII('A')
----------
97
SQL> select ascii('b') from dual;
ASCII('B')
----------
98
(2) 多字节字符:数据库中存储的为数据的ascii 码(多个字节)
SQL> select dump('好') from dual;
DUMP('好')
---------------------
Typ=96 Len=2: 186,195
SQL> select ascii('好') from dual;
ASCII('好')
-----------
47811
SQL> select 186*256 + 195 from dual;
186*256+195
-----------
47811
其中186 为高位,195 为低位,根据公式ascii 码 = 高位*power(2,8)+ 低
位,所以'好'的ascii 为47811。
2) VARCHAR2
SQL> create table test_varchar2
2 (a1 varchar2(1),
3 a2 varchar2(5));
Table created
SQL> insert into test_varchar2 values ('a','ab');
1 row inserted
SQL> insert into test_varchar2 values ('b','abc');
1 row inserted
SQL> commit;
Commit complete
SQL> select a1,dump(a1) from test_varchar2;
A1 DUMP(A1)
-- --------------------------------------------------------------------------------
a Typ=1 Len=1: 97
b Typ=1 Len=1: 98
SQL> select a2,dump(a2) from test_varchar2;
A2 DUMP(A2)
----- --------------------------------------------------------------------------------
ab Typ=1 Len=2: 97,98
abc Typ=1 Len=3: 97,98,99
从dump 的结果可以看到:
。VARCHAR2 的类型编码Typ 为1
。VARCHAR2 类型的数据在数据库的存储长度与实际插入的数据有关系,不需要补位
。“a”在数据库的存储格式为“97”,“b”为“98”
。“ab”在数据库的存储格式为“97,98,32,32,32”
。“abc”在数据库的存储格式为“97,98,99,32,32”
结论:
1, VARCHAR2 的类型编码Typ 为1
2, VARCHAR2 在数据库存储就是数据的实际长度,不需要补位
3, 转化规则:与CHAR 的转化规则完全一致
另:NCHAR,NVARCHAR2 与CHAR,VARCHAR2 类似,就不一一介绍了。
结论
1, CHAR 与VARCHAR2 的最大的区别就是一个2000 字节,一个4000 个字节,还有就是一个
补足空位,一个不需要补空位
2, 一般说来数据库能用CHAR 的地方都可以利用VARCHAR2 来代替,这样可以节省数据库的
空间。但是如果对性能有要求的系统来说,合理的使用CHAR 会比使用VARCHAR2 有更好
的性能。
3, NUMBER
SQL> create table test_number
2 (a1 number(8,2));
Table created
SQL> insert into test_number values (0);
1 row inserted
SQL> insert into test_number values (1);
1 row inserted
SQL> insert into test_number values (-1);
1 row inserted
SQL> insert into test_number values (0.12);
1 row inserted
SQL> insert into test_number values (34.56);
1 row inserted
SQL> insert into test_number values (-34.56);
1 row inserted
SQL> commit;
Commit complete
SQL> select a1 ,dump(a1) from test_number;
A1 DUMP(A1)
---------- --------------------------------------------------------------------------------
0.00 Typ=2 Len=1: 128
1.00 Typ=2 Len=2: 193,2
-1.00 Typ=2 Len=3: 62,100,102
0.12 Typ=2 Len=2: 192,13
34.56 Typ=2 Len=3: 193,35,57
-34.56 Typ=2 Len=4: 62,67,45,102
6 rows selected
从dump 的结果可以看到:
。NUMBER 的类型编码Typ 为2
。如果数值为负数那么需要在最后面补一位102,正数和零则不需要
。Len 为数值的实际值加上补位的长度
。数值0 在数据库的存储格式为128
。数值1 在数据库的存储格式为193,2
。数值-1 在数据库的存储格式为62,100,102
。数值0.12 在数据库存储格式为192,13
。数值34.56 在数据库的存储格式为193,35,56
。数值-34.56 在数据库的存储格式为62,67,45,102
结论:
1, NUMBER 的类型编码Typ 为2
2, 如果数值为负数那么需要在最后面补一位102,正数和零则不需要
之所以这样处理,是为了更加方便的进行两个数值的大小比较。Oracle 在进行数据库中
两个数值大小比较的时候,直接比较他们在数据库中的存储格式,并不是把他们转化成
我们常用的数值形式后在比较。比如说:比较0和1 这两个数,0 在数据库中表示为128,
1 在数据库中表示为193,2,那么直接进行比较‘128’小于‘193’,所以0 肯定比1
要小。在比如说1 和0.12 这两个数比较,0.12在数据库中表示为192.13,因为‘193’
大于‘192’,所以1 肯定比0.12 大。这个规则对于正数和0 完全适用,但是对于不补
位的负数就要出麻烦了。看下面的例子:
SQL> select dump(-0.12) from dual;
DUMP(-0.12)
----------------------
Typ=2 Len=3: 63,89,102
SQL> select dump(-0.125) from dual;
DUMP(-0.125)
-------------------------
Typ=2 Len=4: 63,89,51,102
如果不补位的话-0.12 应该为63,89,而-0.125 应该为63,89,51,那么按照前面的
规则来说‘63’等于‘63’,‘89’等于‘89’,‘51’肯定要大于空,就可以得出错误的
结论-0.12 小于-0.125。而当在负数后面补上102 后,情况就完全改变了,就可以很快
的得到正确的大小关系。
3, 转化规则
(1)数值在数据库中存储结构的第一位叫做标志位,是整个数值表示中最关键的一位。
如果标志位大于128,那么该数值为正数
如果标志位等于128,那么该数值为0
如果标志位小于128,那么该数值为负数
例如:
数值0 的标志位为128,所以为0
数值0.12 的标志位为192,所以0.12 为正数
数值-34.56 的标志位为62,所以-34.56 为负数
(2)数据库中存储结构的除了第一位其余的为数值位
如果该数值为正数,那么每位上的实际数值等于数据库中存储的数值减1
如果该数值为负数,那么每位上的实际数值等于101减去数据库中存储的数值,最
后补位的102 不用计算。
例如:
数值1 的数字为2 – 1 = 1
数值-1 的数字为 101 – 100 = 1
数值34.56 的数字为35 – 1 = 34,57 – 1 = 56,即3456
数值-34.56 的数字为101 – 67 = 34,101 – 45 = 56,即3456
(3)确定小数点位置
确定小数点的位置需要标志位和第一位共同来决定
如果该数值为正数,那么该数值的前几位应该为第一位 * power(100,(标志位 –
193))
如果该数值为负数,那么该数值的前几位应该为第一位 * power(100,(62–标志
位))
例如:
34.56 Typ=2 Len=3: 193,35,57,该数值的前几位应该为34*power(100,0)=34,
所以34.56 的小数点应该在4 的后面,那么就可以得到该数值为34.56
-34.56 Typ=2 Len=4: 62,67,45,102该数值的前几位应该为34*power(100,0)
=34,所以34.56 的小数点应该在4 的后面,那么就可以得到该数值为-34.56
4, LONG
LONG 类型可变长度的字符类型,最高可以达到2G 个字符,可以在数据库和用户session的
字符集之间自动的转化,内部编码为8,oracle 建议使用LOB 类型代替LONG。
由于dump 不能对LONG 型的字符进行处理,所以这里就不在举例子说明了。LONG 类型的转
化规则和其他字符型的类似。
下面着重说一下LONG 类型在数据表中的存放位置问题。
由于LONG 字符的特殊性,长度很大,所以一般当数据表中含有LONG类型的字段时,oracle
都会尽可能的把它放到最后面来存储,一来可以节省空间,二来可以提高效率。这里面说的
是尽可能,有的时候oracle 无法做出相应的处理,LONG也可能位于中间位置,这时就需要
DBA 进行响应的调整了。
举例1:
SQL> create table test_long_1
2 (a1 long,
3 a2 varchar2(10));
Table created
SQL> select object_id from dba_objects where object_name = 'TEST_LONG_1';
OBJECT_ID
---------
47383
SQL> select name,col#,segcol# from sys.col$ where obj# = '47383';
NAME COL# SEGCOL#
------------------------------ ---- -------
A1 1 2
A2 2 1
由上面的结果可以看到虽然LONG 类型的a1 首先被定义,但是在数据库存储的时候却被放到
了a2 的后面。
注:COL#列表示数据表定义时候的顺序,而SEGCOL#表示的是数据库实际存储的顺序。
举例2:
SQL> alter table test_long_1
2 add a3 number;
Table altered
SQL> select name,col#,segcol# from sys.col$ where obj# = '47383';
NAME COL# SEGCOL#
------------------------------ ---- -------
A1 1 2
A2 2 1
A3 3 3
如果表中已经存在了LONG 类型的字段,那么在新加入其他类型字段时候就不会发生任何转
化,此时LONG 类型的字段在中间,这对性能会有很大的影响,这就需要DBA 来手工的进行
调整了。
5,DATE
DATE 类型提供关于世纪,年,月,日,小时,分和秒的信息,不包含有小数的秒和时区信
息。DATE 的内部编码为12。由固定的7 个字节组成:
前两位表示世纪和年,基数为100
下两位表示月和日,基数为0
最后的三位是小时(24 小时制),分和秒,基数为1
SQL> create table test_date
2 (a1 date);
Table created
SQL> insert into test_date values(sysdate);
1 row inserted
SQL> commit;
Commit complete
SQL> select a1,dump(a1) from test_date;
A1 DUMP(A1)
-------------------------------------------------------------------------------
2006-5-29 2 Typ=12 Len=7: 120,106,5,29,22,33,41
世纪:120 – 100 = 20
年 :106 – 100 = 06
月 :5
日 :29
小时:22 – 1 = 21
分钟:33 - 1 = 32
秒 :41 - 1 = 40
5, TIMESTAMP
TIMESTAMP 类型提供关于年,月,日,小时,分,带有小数的秒的信息。TIMESTAMP 的内部
编码为180。
TIMESTAMP 由固定的11 个字节组成:
前七个字节与DATE 类型的一样
后四个字节表示秒的小数部分
SQL> create table test_stamp
2 (a1 timestamp);
Table created
SQL> insert into test_stamp values(systimestamp);
1 row inserted
SQL> commit;
Commit complete
SQL> select a1,dump(a1) from test_stamp;
A1 DUMP(A1)
-------------------------------------------------------------------------------
29-5月 -06 09.56.04.375000 Typ=180 Len=11: 120,106,5,29,22,57,5,22,90,11,192
世纪:120 – 100 = 20
年 :106 – 100 = 06
月 :5
日 :29
小时:22 – 1 = 21
分钟:57 - 1 = 56
秒(整数):5 - 1 = 4
秒(小数):0.23440320
SQL> select to_char('22','xx') from dual;
TO_CHAR('22','XX')
------------------
16
SQL> select to_char('90','xx') from dual;
TO_CHAR('90','XX')
------------------
5a
SQL> select to_char('11','xx') from dual;
TO_CHAR('11','XX')
------------------
b
SQL> select to_char('192','xx') from dual;
TO_CHAR('192','XX')
-------------------
c0
SQL> select to_number('165abc0','xxxxxxxxxxx') from dual;
TO_NUMBER('165ABC0','XXXXXXXXX
------------------------------
23440320

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8394333/viewspace-973776/,如需转载,请注明出处,否则将追究法律责任。

上一篇: oracle中的加密包
下一篇: 由FILE$想到的
请登录后发表评论 登录
全部评论
  • 博文量
    56
  • 访问量
    759830