oracle数据类型data type与存储空间大小（二）

SQL> desc t_col_type;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  VARCHAR2(30 CHAR) #用的是char而非byte

 

 

####插入汉字，测试表存储大小
declare
v_i int;
begin
for i in 1..100000 loop
insert into t_col_type values('我爱北京天安门***');
commit;
end loop;
end;
/

select segment_name,bytes,bytes/1024/1024 mb from user_segments where segment_name='T_COL_TYPE';

实际占用的大小(100000条记录）
3000000

表结构占用的大小
SQL> select segment_name,bytes,bytes/1024/1024 mb from user_segments where segment_name='T_COL_TYPE';

SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES         MB
---------- ----------
T_COL_TYPE
     65536      .0625

 

插入100000记录的大小
SQL> select segment_name,bytes,bytes/1024/1024 mb from user_segments where segment_name='T_COL_TYPE';

SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES         MB
---------- ----------
T_COL_TYPE
   5242880          5

 

5242880 - 65536 = 5242880 与实际10w记录的占用大小3000000还有有有近一半的差距

 

1
 VARCHAR2(size [BYTE | CHAR])
 Variable-length character string having maximum length size bytes or characters. Maximum size is 4000 bytes or characters, and minimum is 1 byte or 1 character. You must specify size for VARCHAR2.

BYTE indicates that the column will have byte length semantics; CHAR indicates that the column will have character semantics
 

问题：
    按我的理解：10w记录占用的大小应是30byte*100000=3000000byte啊，再加上表结构的65536byte，哪其它的大小跑哪儿去了

    一个汉字占2byte还是3个byte哟

 

 

###表segment是由20个extent组成，且每个extent大小不一样
  1* select segment_name,extent_id,bytes,blocks from user_extents where segment_name='T_COL_TYPE'

SEGMENT_NAME                                                                       EXTENT_ID      BYTES     BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
T_COL_TYPE                                                                                 0      65536          8
T_COL_TYPE                                                                                 1      65536          8
T_COL_TYPE                                                                                 2      65536          8
T_COL_TYPE                                                                                 3      65536          8
T_COL_TYPE                                                                                 4      65536          8
T_COL_TYPE                                                                                 5      65536          8
T_COL_TYPE                                                                                 6      65536          8
T_COL_TYPE                                                                                 7      65536          8
T_COL_TYPE                                                                                 8      65536          8
T_COL_TYPE                                                                                 9      65536          8
T_COL_TYPE                                                                                10      65536          8

SEGMENT_NAME                                                                       EXTENT_ID      BYTES     BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
T_COL_TYPE                                                                                11      65536          8
T_COL_TYPE                                                                                12      65536          8
T_COL_TYPE                                                                                13      65536          8
T_COL_TYPE                                                                                14      65536          8
T_COL_TYPE                                                                                15      65536          8
T_COL_TYPE                                                                                16    1048576        128
T_COL_TYPE                                                                                17    1048576        128
T_COL_TYPE                                                                                18    1048576        128
T_COL_TYPE                                                                                19    1048576        128

20 rows selected.

SQL>

小结：
一个segment是由多个extent组成，extent是由多个block组成.每个extent大小不一定相同;所以要精确计算每个表table的真正存储占用大小，还要去了解block的内部存储,因为segment最终是由许多个block组成，研究block吧

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

转载于:http://blog.itpub.net/9240380/viewspace-666447/