檔案系統儲存與oracle資料庫儲存對比

本文主要對檔案系統儲存與資料庫儲存作介紹,為了一些經常對儲存空間評估人士,提供比較準確評估依據.....

下面所有描述都是在字元字為:

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

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK

[@more@]

一、檔案系統儲存
1、1K=1024B(BYTE)=1024*8b(bit)；
2、一個字元=1個位元組(1B)；（備註：unix只有換行(0X0A)，沒回車，一個換行符佔用一個字元；windows既有換行又有回車。換行符和回車符各佔用一個字元）
3、一箇中文=2個位元組 --(如果是32位編碼的字符集,佔用四個位元組)
二、資料庫儲存(oracle)
DUMP函式的輸出格式類似:
型別 ，符號/指數位 [數字1，數字2，數字3，......，數字20]
各位的含義如下:
1.型別: Number型,Type=2 (型別程式碼可以從Oracle的文件上查到，見結尾)
2.長度:指儲存的位元組數
3.符號/指數位
在儲存上,Oracle對正數和負數分別進行儲存轉換:
正數：加1儲存(為了避免Null)，在實際演算法中就是要減1，必須>128
負數：被101減,如果總長度小於21個位元組，最後加一個102(是為了排序的需要),必須<128
1、NUMBER：正數--從右第一個非零數起，二個字元為計算單位，二個字元=2個位元組，如果最後單字元以二個位元組計算，總數+1就是佔用儲存。
負數--從右第一個非零數起，二個字元為計算單位，二個字元=2個位元組，如果最後單字元以二個位元組計算，總數+2就是佔用儲存。
SQL> select id,dump(id) dp from test2 order by id;

ID DP
---------- --------------------------------------------------
1 Typ=2 Len=2: 193,2
12 Typ=2 Len=2: 193,13
123 Typ=2 Len=3: 194,2,24
1234 Typ=2 Len=3: 194,13,35
12345 Typ=2 Len=4: 195,2,24,46
123456 Typ=2 Len=4: 195,13,35,57
1234567 Typ=2 Len=5: 196,2,24,46,68
123456789 Typ=2 Len=6: 197,2,24,46,68,90

SQL> select id ,dump(id) dp from test4;

ID DP
---------- ----------------------------------------------
0 Typ=2 Len=1: 128
10 Typ=2 Len=2: 193,11
100 Typ=2 Len=2: 194,2
1000 Typ=2 Len=2: 194,11
10001 Typ=2 Len=4: 195,2,1,2

SQL> select id,dump(id) dp from test2 order by id desc

ID DP
---------- ---------------------------------------
-1 Typ=2 Len=3: 62,100,102
-12 Typ=2 Len=3: 62,89,102
-123 Typ=2 Len=4: 61,100,78,102
-1234 Typ=2 Len=4: 61,89,67,102
-12345 Typ=2 Len=5: 60,100,78,56,102
-123456 Typ=2 Len=5: 60,89,67,45,102
-1234567 Typ=2 Len=6: 59,100,78,56,34,102
-123456789 Typ=2 Len=7: 58,100,78,56,34,12,102

SQL> select id ,dump(id) dp from test4;

ID DP
---------- ---------------------------------------
0 Typ=2 Len=1: 128
-10 Typ=2 Len=3: 62,91,102
-100 Typ=2 Len=3: 61,100,102
-1000 Typ=2 Len=3: 61,91,102
-10001 Typ=2 Len=5: 60,100,101,100,102

2、VARCHAR2：變長，一個字元佔用一位元組

SQL> select name,dump(name) dp from revenco;

NAME DP
------------------------ ----------------------------------------------
1 Typ=1 Len=1: 49
12 Typ=1 Len=2: 49,50
123 Typ=1 Len=3: 49,50,51
1234 Typ=1 Len=4: 49,50,51,52
12345 Typ=1 Len=5: 49,50,51,52,53
123456789 Typ=1 Len=9: 49,50,51,52,53,54,55,56,57

3、CHAR:定長

SQL> select name,dump(name) dp from revenco2;

NAME DP
---------- ----------------------------------------------
1 Typ=96 Len=10: 49,32,32,32,32,32,32,32,32,32
1234 Typ=96 Len=10: 49,50,51,52,32,32,32,32,32,32
1234567890 Typ=96 Len=10: 49,50,51,52,53,54,55,56,57,48

4、DATE：定長=7個位元組

SQL> select date_time,dump(date_time) dp from revenco3;

DATE_TIME DP
------------------- ----------------------------------------------
2009-11-06 11:57:18 Typ=12 Len=7: 120,109,11,6,12,58,19
2009-11-06 11:57:56 Typ=12 Len=7: 120,109,11,6,12,58,57

5、TIMESTAMP:變長(7或者11位元組)

SQL> select to_char(time_stamp,'YYYY-MM-DD HH24:MI:SS.FF') TIME ,dump(time_stamp) dp from revenco4;

TIME DP
------------------------------------------------------------ --------------------------------------------------------
2009-11-06 12:16:16.000000 Typ=180 Len=7: 120,109,11,6,13,17,17
2009-11-06 12:16:16.123456 Typ=180 Len=11: 120,109,11,6,13,17,17,7,91,202,0
2009-11-06 12:17:12.120000 Typ=180 Len=11: 120,109,11,6,13,18,13,7,39,14,0
2009-11-06 12:17:12.000056 Typ=180 Len=11: 120,109,11,6,13,18,13,0,0,218,192

6、表佔表空間

CREATE TABLE "ETL"."test5"
( "ID" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"

SQL> SELECT COUNT(*) FROM test5;

COUNT(*)
----------
0

SQL> SELECT SEGMENT_NAME,BYTES FROM USER_SEGMENTS WHERE SEGMENT_NAME='TEST5';

SEGMENT_NAME BYTES
-------------------------- ----------
TEST5 65536

備註：為test5的記錄資料為0，還有佔用65536位元組空間。因為oracle資料庫儲存最小單元為資料塊，而表在建立時就進行儲存分配空間。不管
表是否存在記錄數。所以不能想當然計算行平均大小=BYTES/COUNT(*)，但是oracle也提供計算行的平均大小。即：user_tables.avg_row_len(需
要對錶進行統計收集才有資訊的)。

7、每行平均位元組數AVG_ROW_LEN
(1)透過DBA_TABLES的AVG_ROW_LEN可以檢視TABLE每一行平均位元組數。前提是對TABLE進行了統計資訊收集。(關於統計資訊收集請檢視myblog《oracle統計資訊收集詳解》)
(2)oracle如何計算AVG_ROW_LEN，下面舉例說明：
SQL> select * from test3;

ID NAME DATE_TIME
---------- ------------ -------------------
1234567 12345678 2009-11-13 11:21:21
1234567 12345678 2009-11-13 11:21:22
1234567 12345678 2009-11-13 11:21:23
1234567 12345678 2009-11-13 11:21:24
1234567 12345678 2009-11-13 11:21:24
SQL> l
1* select dump(id) dp,dump(name) dp,dump(date_time) dp from test3
SQL> /

DP DP DP
------------------------ ------------------------ ------------------------
Typ=2 Len=5: 196,2,24,46 Typ=1 Len=8: 49,50,51,52 Typ=12 Len=7: 120,109,11
Typ=2 Len=5: 196,2,24,46 Typ=1 Len=8: 49,50,51,52 Typ=12 Len=7: 120,109,11
Typ=2 Len=5: 196,2,24,46 Typ=1 Len=8: 49,50,51,52 Typ=12 Len=7: 120,109,11
Typ=2 Len=5: 196,2,24,46 Typ=1 Len=8: 49,50,51,52 Typ=12 Len=7: 120,109,11
Typ=2 Len=5: 196,2,24,46 Typ=1 Len=8: 49,50,51,52 Typ=12 Len=7: 120,109,11

SQL> select table_name,num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len,last_analyzed from dba_tables where table_name='TEST3' and owner='ETL';

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN LAST_ANALYZED
-------------- ---------- ---------- ------------ ---------- ---------- ----------- -------------------
TEST3 5 5 0 0 0 23 2009-11-13 11:21:41

其中:AVG_ROW_LEN=((5+8+7)+3)*5/5=23