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

 

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

 

这一篇主要说明字符类型的存储格式主要包括char、varchar2和long等几种类型。

 

查看当前用户：

SQL> show user

USER is "LSF"

 

建立一个测试表：

SQL> create table test_char  

  2  (char_col char(10),

  3   varchar_col varchar2(10),

  4   long_col long

  5  );

 

Table created.

 

插入测试数据：

SQL> insert into test_char values('abc','123',',fd');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

查看rowid：

SQL> select rowid from test_char;

 

ROWID

------------------

AAAHw5AALAAAAKdAAA

 

ROWID是数据的详细地址，通过rowid，oracle可以快速的定位某行具体的数据的位置。

ROWID可以分为物理rowid和逻辑rowid两种。普通的堆表中的rowid是物理rowid，索引组织表(IOT)的rowid是逻辑rowid。oracle提供了一种urowid的数据类型，同时支持物理和逻辑rowid。这里主要关注物理rowid

物理rowid又分为扩展rowid(extended rowid)和限制rowid(restricted rowid)两种格式。限制rowid主要是oracle7以前的rowid格式，现在已经不再使用，保留该类型只是为了兼容性。所以本文的提到物理 rowid一般是指扩展rowid格式。

 

Rowid的显示形式：

我们从rowid伪列里select出来的rowid是基于base64编码，一共有18位，分为4部分：

OOOOOOFFFBBBBBBRRR

其中：

OOOOOO：六位表示data object id，根据object id可以确定segment。

FFF：三位表示相对文件号。根据该相对文件号可以得到绝对文件号，从而确定datafile。

BBBBBB：六位表示data block number，这里的data block number是相对于datafile的编号，而不是相对于tablespace的编号。

RRR：三位表示row number。

Oracle提供了dbms_rowid来进行rowid的一些转换计算。

SQL> select dbms_rowid.rowid_object(rowid) obj#,

  2  dbms_rowid.rowid_relative_fno(rowid) rfile#,

  3  dbms_rowid.rowid_block_number(rowid) block#,

  4  dbms_rowid.rowid_row_number(rowid) row#,

  5  dbms_rowid.rowid_to_absolute_fno(rowid,'LSF','TEST_CHAR') file#

  6  from test_char;

 

      OBJ#     RFILE#     BLOCK#       ROW#      FILE#

---------- ---------- ---------- ---------- ----------

     31801         11        669          0         11

 

如何从rowid计算得到obj#,rfile#,block#,row#

rowid是base64编码的，用A~Z a~z 0~9 + /共64个字符表示。A表示0，B表示1，……，a表示26，……，0表示52，……，+表示62，/表示63可以将其看做一个64进制的数。

所以，

ROWID

------------------

AAAGbEAAHAAAAB8AAA

 

obj#=AAAGbE=6*64^2+27*64+4=26308

rfile#=AAH=7

block#=AAAAB8=64+60=124

row#=AAA=0

 

我们的例子中

ROWID

------------------

AAAHw5AALAAAAKdAAA

obj#=AAAHw5=7*64^2+48*64+57=31801

rfile#=AAL=11

block#=AAAAKd=669

row#=AAA=0

 

如何从obj#,rfile#,block#,row#计算得到rowid

实际上就是将十进制数转化成64进制数，当然，从二进制转化的规则比较简单点。

将二进制数从右到左，6个bit一组，然后将这6个bit组转成10进制数，就是A~Z a~z 0~9 + /这64个字符的位置（从0开始），替换成base64的字符即可。

obj#=26308=110 011011 000100=6 27 4=G b E，补足成6位base64编码，左边填0，也就是A，结果为AAAGbE

rfile#=7=111=7=H，补足成3位，得到AAH

block#=124=1 111100=1 60=B 8，补足成6位，得到AAAAB8

row#=0，3位AAA

合起来就是AAAGbEAAHAAAAB8AAA

 

我们的例子中

ROWID

------------------

AAAHw5AALAAAAKdAAA

obj#=AAAHw5=7*64^2+48*64+57=31801

rfile#=AAL=11

block#=AAAAKd=669

row#=AAA=0

 

obj#=31801= 111 110000 111001=7 48 57=H w 5,补足成6位，得到AAAHw5

rfile#=11=1011=11=L,补足成3位，得到AAL

block#=669= 1010 011101=10 29=Kd,补足成6位，得到AAAAKd

row#=0，三位AAA

合起来就是AAAHw5AALAAAAKdAAA

 

Rowid的内部存储格式

虽然我们从rowid伪列中select出来的rowid是以base64字符显示的，但在oracle内部存储的时候还是以原值的二进制表示的。一个扩展 rowid采用10个byte来存储，共80bit，其中obj#32bit,rfile#10bit,block#22bit,row#16bit。所以相对文件号不能超过1023，也就是一个表空间的数据文件不能超过1023个（不存在文件号为0的文件），一个datafile只能有2^22=4M个 block,一个block中不能超过2^16＝64K行数据。而一个数据库内不能有超过2^32=4G个object。

SQL> select dump(rowid,16) from test;

DUMP(ROWID,16)

--------------------------------------------

Typ=69 Len=10: 0,0,66,c4,1,c0,0,7c,0,0

00000000 00000000 01100110 11000100 00000001 11000000 00000000 01111100 00000000 00000000

最右边16bit为row#=00000000 00000000=0

接下来22bit为block#=000000 00000000 01111100=124

接下来10bit为rfile#=00000001 11=7

接下来32bit为obj#=00000000 00000000 01100110 11000100=26308

 

我们的例子中

SQL> select dump(rowid,16) from test_char;

 

DUMP(ROWID,16)

-----------------------------------------------------------------------------------------------------------------------------------

Typ=69 Len=10: 0,0,7c,39,2,c0,2,9d,0,0

00000000 00000000 01111100 00111001 00000010 11000000 00000010 10011101 00000000 00000000

最右边16bit为row#=00000000 00000000=0

接下来22bit为block#=000000 00000010 10011101=669

接下来10bit为file#=00000010 11=11

接下来32bit为obj#=00000000 00000000 01111100 00111001=31801

 

Index中存储的rowid

a.普通B-tree索引

SQL> create index ix_test on test(id);

Index created.

SQL> select file_id,block_id from dba_extents where segment_name='IX_TEST' and owner=user;

FILE_ID BLOCK_ID

---------- ----------

7 129

---由于是assm表空间，去掉3个block的头

SQL> alter system dump datafile 1 block 132;

System altered.

得到trace文件内容如下（省略无关内容）：

row#0[8024] flag: -----, lock: 0

col 0; len 2; (2): c1 02 ---索引键数据ID=1

col 1; len 6; (6): 01 c0 00 7c 00 00 ---对应的rowid记录

----- end of leaf block dump -----

End dump data blocks tsn: 7 file#: 7 minblk 132 maxblk 132

普通索引中保存的rowid是不包括obj#的，但是分区表的global index是包括obj#的，这是因为分区表包括多个segment，每个segment可能在不同的datafile中，根据表的obj#就无法确定该索引键对应的rowid（rfile#确定不了）。

01 c0 00 7c 00 00 转化为二进制 000000001 11000000 00000000 01111100 00000000 00000000

右边8bit row#=0

接下来22bit block#=000000 00000000 01111100=124

接下来10bit rfile#=000000001 11=7

b.唯一索引

SQL> drop index ix_test;

Index dropped.

SQL> create unique index ix_test on test(id);

Index created.

SQL> select file_id,block_id from dba_extents where segment_name='IX_TEST' and wner=user;

FILE_ID BLOCK_ID

---------- ----------

7 129

SQL> alter system dump datafile 1 block 132;

System altered.

得到trace文件内容如下：

row#0[8025] flag: -----, lock: 0, data:(6): 01 c0 00 7c 00 00 ---对应的rowid记录

col 0; len 2; (2): c1 02 ---索引键数据ID=1

----- end of leaf block dump -----

End dump data blocks tsn: 7 file#: 7 minblk 132 maxblk 132

得到rowid为 01 c0 00 7c 00 00，具体的转换计算和前面的一样，就不重复了。

 

下面继续我们上面的实验：

根据rowid的定义规则，第7～9位是表示的是数据文件，L表示11，而10～15位表示的是在这个数据文件中的第几个BLOCK，Kd表示669。（rowid编码相当于64进制。用A~Z a~z 0~9 + /共64个字符表示。A表示0，B表示1，……，a表示26，……，0表示52，……，+表示62，/表示63。）

 

我们根据计算的结果去dump这个block。

SQL> @trace.sql                             

 

Session altered.

 

 

Session altered.

SQL> alter system dump datafile 11 block 669;

 

System altered.

SQL> @gettracefilename.sql

 

RTRIM(C.VALUE,'/')||'/'||D.INSTANCE_NAME||'_ORA_'||LTRIM(TO_CHAR(A.SPID))||'.TRC'

-----------------------------------------------------------------------------------------------------------------------------------

/u01/app/oracle/admin/orcl/udump/orcl_ora_2758.trc

 

打开产生的trace文件：

Start dump data blocks tsn: 12 file#: 11 minblk 669 maxblk 669

buffer tsn: 12 rdba: 0x02c0029d (11/669)

scn: 0x0000.0005ee3e seq: 0x01 flg: 0x06 tail: 0xee3e0601

frmt: 0x02 chkval: 0x82c5 type: 0x06=trans data

Block header dump:  0x02c0029d

 Object id on Block? Y

 seg/obj: 0x7c39  csc: 0x00.5ee3c  itc: 2  flg: E  typ: 1 - DATA

     brn: 0  bdba: 0x2c00289 ver: 0x01

     inc: 0  exflg: 0

 

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0004.002.0000014c  0x008000bf.0053.4d  --U-    1  fsc 0x0000.0005ee3e

0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

 

data_block_dump,data header at 0xc6b3e64

===============

tsiz: 0x1f98

hsiz: 0x14

pbl: 0x0c6b3e64

bdba: 0x02c0029d

     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: 12 file#: 11 minblk 669 maxblk 669

 

观察dump出来的结果，可以发现以下几点：

1.对于每个字段，除了保存字段的值以外，还会保存当前字段中数据的长度。而且，oracle显然没有把字段的长度定义或类型定义保存在block中，这些信息保存在oracle的数据字典里面。

2.根据dump的结果，可以清楚的看到，字符类型在数据库中是以ascii格式存储的。

SQL> select chr(to_number('61','xx')) from dual;

 

CHR(TO_N

--------

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 at line 1:

ORA-00997: illegal use of LONG datatype

 

由于DUMP不支持LONG类型，因此我们使用了alter system dump block的方式，通过比较两种方式得到的结果，发现DUMP()函数不但方便，结果清晰，而且指出了进行DUMP的数据类型，在以后的例子中，除非必要的情况，否则都会采用DUMP()函数的方式进行说明。

 

下面看一下插入中文的情况，首先看一下数据库的字符集

SecureCRT设置会话选项->字符编码->UTF-8

$ export NLS_LANG=.AL32UTF8

SQL> COL VALUE FOR A30

SQL> COL PARAMETER A30

SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET';

 

PARAMETER                      VALUE

------------------------------ ------------------------------

NLS_CHARACTERSET               AL32UTF8

SQL> select * from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET';

 

PARAMETER                      VALUE

------------------------------ ------------------------------

NLS_NCHAR_CHARACTERSET         AL16UTF16

SQL> select * from nls_database_parameters;

 

PARAMETER                      VALUE

------------------------------ ------------------------------

NLS_LANGUAGE                   AMERICAN

NLS_TERRITORY                  AMERICA

NLS_CURRENCY                   $

NLS_ISO_CURRENCY               AMERICA

NLS_NUMERIC_CHARACTERS         .,

NLS_CHARACTERSET               AL32UTF8

NLS_CALENDAR                   GREGORIAN

NLS_DATE_FORMAT                DD-MON-RR

NLS_DATE_LANGUAGE              AMERICAN

NLS_SORT                       BINARY

NLS_TIME_FORMAT                HH.MI.SSXFF AM

 

PARAMETER                      VALUE

------------------------------ ------------------------------

NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM

NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY              $

NLS_COMP                       BINARY

NLS_LENGTH_SEMANTICS           BYTE

NLS_NCHAR_CONV_EXCP            FALSE

NLS_NCHAR_CHARACTERSET         AL16UTF16

NLS_RDBMS_VERSION              9.2.0.4.0

 

20 rows selected.

 

以SYS用户

SQL> SELECT NAME,VALUE$ FROM SYS.PROPS$ WHERE NAME='NLS_CHARACTERSET';

也可以查看数据库的字符集

SQL> select userenv('language') from dual;

 

USERENV('LANGUAGE')

----------------------------------------------------

AMERICAN_AMERICA.AL32UTF8

 

Dump 文件的第2和第3个字节记录了dump文件的字符集

如果dump文件相对较小，用UltraEdit打开（16进制方式），看第2第3个字节的内容，如0354，然后用以下SQL查出它对应的字符集。

如果dmp文件很大，比如有2G以上(这也是最常见的情况)，用文本编辑器打开很慢或者完全打不开，可以用以下命令(在unix主机上):

cat exp.dmp |od -x|head -1|awk '{print $2 $3}'|cut -c 3-6

然后用上述SQL也可以得到它对应的字符集。

 

SQL> select nls_charset_name(to_number('0354','xxxx')) from dual;

 

NLS_CHARSET_NAME(TO_NUMBER('0354','XXXX'

----------------------------------------

ZHS16GBK

 

SQL> insert into test_char values('定长','变长',null);

 

1 row created.

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: e5,ae,9a,e9,95,bf,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=6: e5,8f,98,e9,95,bf

根据dump结果，可以清楚的看出，普通英文字符和标点用一个字节表示，而中文字符或中文标点需要三个字节来表示。

 

下面，对比一下nchar和nvarchar2与char、varchar2类型有什么不同。

SQL> create table test_nchar(nchar_col nchar(10), nvarchar_col nvarchar2(10));

 

Table created.

 

SQL> insert into test_nchar values ('nchar定长','nvarchar变长');

 

1 row created.

从这里已经可以看出一些不同了，如果按照刚才中文的计算方法，'nvarchar变长'的长度是8+2*3=14已经超过了数据类型定义的大小，可是为什么插入成功了？--可能有错

还是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，并没有超过数据类型的限制。

 

补充：

nchar（size）的定义：

CREATE TABLE tab1 (col1 NCHAR(30));

This statement creates a column with maximum character length of 30. The maximum byte length is the multiple of the maximum character length and the maximum number of bytes in each character.

size是以字符character为定义,而不是byte;同理nvarchar;

查看tom的chapter 12 Datatypes章节有这个介绍。

 

一个英文字母是一个字符，一个中文汉字是几个字符呢？我们知道，os字符集是gbk环境下，一个中文汉字是双字节字符，但它算做几个字符与其数据库字符集有关。如果数据库字符集使用单字节US7ASCII，则一个中文汉字是二个字符；如果数据库字符集使用双字节字符集ZHS16GBK，则一个中文汉字被视为一个字符。有关这一点可以使用 Oracle的函数substr得到证明。

使用US7ASCⅡ字符集时:

select substr(‘南京航空航天大学’,1,2) from dual;

语句执行结果返回‘南’。

使用ZHS16GBK字符集时:

select substr(‘南京航空航天大学’,1,2) from dual;

语句执行结果返回‘南京’。

使用AL32UTF8字符集时：

select substr('南京航空航天大学',1,2) from dual;

语句执行结果返回‘南京’。

SQL> select substr('南京航空航天大学',1,2) from dual;

 

SUBSTR

------

南京