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吧