ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle数据类型与存储结构(by oratea)

oracle数据类型与存储结构(by oratea)

原创 Linux操作系统 作者:alidw 时间:2008-03-31 11:18:05 0 删除 编辑

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”是用“19379”来存储的。

注: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的类型编码Typ96

Len表示该数据在数据库中的存储长度,char1)为1,而char5)为5

。“a”在数据库的存储格式为“97”,“b”为“98

。“ab”在数据库的存储格式为“9798323232

。“abc”在数据库的存储格式为“9798993232

结论:

1,  CHAR的类型编码Typ96

2,  CHAR在数据库中按固定长度存储,不足的为用空格补齐(chr32))

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 = 高位*power28+ 低位,所以''ascii47811

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的类型编码Typ1

VARCHAR2类型的数据在数据库的存储长度与实际插入的数据有关系,不需要补位

。“a”在数据库的存储格式为“97”,“b”为“98

。“ab”在数据库的存储格式为“9798323232

。“abc”在数据库的存储格式为“9798993232

结论:

1,  VARCHAR2的类型编码Typ1

2,  VARCHAR2在数据库存储就是数据的实际长度,不需要补位

3,  转化规则:与CHAR的转化规则完全一致

 

另:NCHARNVARCHAR2CHARVARCHAR2类似,就不一一介绍了。

 

结论

1,  CHARVARCHAR2的最大的区别就是一个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的类型编码Typ2

。如果数值为负数那么需要在最后面补一位102,正数和零则不需要

Len为数值的实际值加上补位的长度

。数值0在数据库的存储格式为128

。数值1在数据库的存储格式为1932

。数值-1在数据库的存储格式为62100102

。数值0.12在数据库存储格式为19213

。数值34.56在数据库的存储格式为1933556

。数值-34.56在数据库的存储格式为626745102

结论:

1,  NUMBER的类型编码Typ2

2,   如果数值为负数那么需要在最后面补一位102,正数和零则不需要

之所以这样处理,是为了更加方便的进行两个数值的大小比较。Oracle在进行数据库中两个数值大小比较的时候,直接比较他们在数据库中的存储格式,并不是把他们转化成我们常用的数值形式后在比较。比如说:比较01这两个数,0在数据库中表示为1281在数据库中表示为1932,那么直接进行比较‘128’小于‘193’,所以0肯定比1要小。在比如说10.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应该为6389,而-0.125应该为638951,那么按照前面的规则来说‘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 = 3457 – 1 = 56,即3456

数值-34.56的数字为101 – 67 = 34101 – 45 = 56,即3456

    3)确定小数点位置

        确定小数点的位置需要标志位和第一位共同来决定

如果该数值为正数,那么该数值的前几位应该为第一位 * power100,(标志位 – 193))

如果该数值为负数,那么该数值的前几位应该为第一位 * power100,(62–标志位))

例如:

34.56 Typ=2 Len=3: 193,35,57,该数值的前几位应该为34*power1000=34,所以34.56的小数点应该在4的后面,那么就可以得到该数值为34.56

-34.56 Typ=2 Len=4: 62,67,45102该数值的前几位应该为34*power1000=34,所以34.56的小数点应该在4的后面,那么就可以得到该数值为-34.56

    

4, LONG

 

LONG类型可变长度的字符类型,最高可以达到2G个字符,可以在数据库和用户session的字符集之间自动的转化,内部编码为8oracle建议使用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的后面。

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

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2008-03-28

  • 博文量
    5
  • 访问量
    4971