Oracle 数据类型及存储方式(袁光东 原创)
概述
通過實例，全面而深入的分析oralce 10G的基本數據類型及它們的存儲方式。從實用和優化的角度出發，討論每種數據類型的特點。從這里開始oracle之旅！
第一部份 字符類型
§1.1 char
定長字符串，會用空格來填充來達到其最大長度,最長2000個字節。
1． 新建一個測試表test_char.,只有一個char類型的列。長度為10
SQL> create table test_char(colA char(10));
Table created
2． 向這個表中插入一些數據。
SQL> insert into test_char values('a');
1 row inserted
注意：最多只能插入10個字節。否是就報錯。
SQL> insert into test_char values('aaaaaaaaaaa');
insert into test_char values('aaaaaaaaaaa')
ORA-12899: value too large for column "PUB_TEST"."TEST_CHAR"."COLA" (actual: 11, maximum: 10)
3． 使用dump函數可以查看每一行的內部存數結構。
SQL> select colA, dump(colA) from test_char;
COLA DUMP(COLA)
---------- --------------------------------------------------------------------------------
a Typ=96 Len=10: 97,32,32,32,32,32,32,32,32,32
aaaaaaaaaa Typ=96 Len=10: 97,97,97,97,97,97,97,97,97,97
注意：Typ=96 表示數據類型的ID。Oracle為每一種數據類型都進行了編號。說明char類型的編號是96.
Len =10 表示所在的內部存儲的長度(用字節表示)。雖然第一例只存了一個字符’a’，但是它還是占用了10個字節的空間。
97,32,32,32,32,32,32,32,32,32 表示內部存儲方式?？梢妎racle的內部存儲是以數據庫字符集進行存儲的。97正好是字符a的ASCII碼。可以使用chr函數把ASCII碼轉成字符。
SQL> select chr(97) from dual;
CHR(97)
-------
a
要想知道一個字符的ASCII碼，可以使用函數ascii
SQL> select ascii('a') from dual;
ASCII('A')
----------
97
32正好是空格的ascii碼值。
Char類型是定長類型。它總會以空格來填充以達到一個固定寬度。使用char類型會浪費存儲空間。
Oracle的數據類型的長度單位是字節。
SQL> select dump('漢') from dual;
DUMP('漢')
---------------------
Typ=96 Len=2: 186,186
可見一個漢字在oracle中是占用了兩個字節的。
英文字母或符號只占用一個字節。Char(10)最多可存放5個漢字.
§1.2 varchar2
是一種變長的字符類型。最多可占用4000字節的存儲空間。
1. 創建一個表,只有一列，類型為varchar2,長度為10
SQL> create table test_varchar( col varchar2(10));
Table created
2. 插入一些數據
SQL> insert into test_varchar values('a');
1 row inserted
2. 用dump函數查看每一行的內部存儲結構。
SQL> select col, dump(col) from test_varchar;
COL DUMP(COL)
---------- --------------------------------------------------------------------------------
a Typ=1 Len=1: 97
Typ=1,說明varchar2類型在oracle中的類型編號為1
Len代表了每一行數據所占用的字節數。
后面是具體的存儲值。
由此可見，varchar2是存多少就占用多少空間。比較節省空間的。不會像char那樣用空格填充。
§1.3 byte 和char
在10g中，字符類型的寬度定義時，可以指定單位。Byte就是字節。Char就是字符。
Varchar2(10 byte) 長度為10個字節。
Varchar2(10 char) 長度為10個字符所占的長度。
Char(10 byte)長度為10個字節。
Char(10 char) 長度為10個字符所占的長度。
一個字符占用多少個字節，是由當前系統采用的字符集來決定的。如一個漢字占用兩個字節。
查看當前系統采用的字符集
SQL> select * from nls_database_parameters where parameter ='NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ --------------------------------------------------------------------------------
NLS_CHARACTERSET ZHS16GBK
如果在定義類型時，不指定單位。默認是按byte，即以字節為單位的。
采用char為單位的好處是，使用多字節的字符集。
比如，在ZHS16GBK字符集中，一個漢字占用兩個字節。
把數據表的某一列長度定義為可存放10個漢字，通過下面的定義就可以了。
Create table test_varchar(col_char varchar2(10 char));
這樣相對簡單一些。在數據庫表設計時需要注意。
繼續實驗，新建一個表，包含兩列。一列采用byte為單位，一列采用char為單位
SQL> create table test_varchar2 (col_char varchar2(10 char),col_byte varchar2(10 byte));
Table created
Col_char列，定義為可存放10個字符。
Col_byte 列，定義為可存放10個字節的字符。
當前的系統采用字符集為ZHS16GBK.所以一個字符占兩個字節。
試著在表中插入一些數據
SQL> insert into test_varchar2 values('a','a');
1 row inserted
SQL> insert into test_varchar2 values('袁','a');
1 row inserted
SQL> insert into test_varchar2 values('袁袁袁袁袁袁袁袁袁袁','aaaaaaaaaa');
1 row inserted
SQL> insert into test_varchar2 values('袁袁袁袁袁袁袁袁袁袁','袁袁袁袁袁袁袁袁袁袁');
insert into test_varchar2 values('袁袁袁袁袁袁袁袁袁袁','袁袁袁袁袁袁袁袁袁袁')
ORA-12899: value too large for column "PUB_TEST"."TEST_VARCHAR2"."COL_BYTE" (actual: 20, maximum: 10)
第一次， 在兩列中都插入字符a
第二次， 在col_char列插入字符’袁’,在col_byte插入字符a
第三次， 在col_char列中插入10個中文字符’袁’,在col_byte插入10個字符a
第四次， 在兩列中都插入中文字符’袁’時，報錯了。第二列長度不夠。
再看看每一行的存儲結構
SQL> select col_char, dump(col_char) from test_varchar2;
COL_CHAR DUMP(COL_CHAR)
-------------------- --------------------------------------------------------------------------------
a Typ=1 Len=1: 97
袁 Typ=1 Len=2: 212,172
袁袁袁袁袁袁袁袁袁袁 Typ=1 Len=20: 212,172,212,172,212,172,212,172,212,172,212,172,212,172,212,172,21
當我們在col_char列插入10個漢字時，它的長度為20.
盡管我們在定義的時候是采用varchar2(10,char).
由此可見,oracle是根據當前數據庫采用的字符集，每個字符的所占字節數 X 字段長度來決定了該字段所占的字節數。
在本例中，varchar2(10,char)相當于varchar2(20).
不信，我們可以試試看。
SQL> desc test_varchar2;
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
COL_CHAR VARCHAR2(20) Y
COL_BYTE VARCHAR2(10) Y
當采用多字節的字符集時，定義字段長度還是采用char為單位指定為佳。因為可以避免字段長度的問題。
當不知道當前數據庫采用的字符集，一個字符占用多少字節時，可以使用lengthb函數。
SQL> select lengthb('袁') from dual;
LENGTHB('袁')
-------------
2
§1.4 char還是varchar
1. 新建一個表,一列為char類型，一列為varchar2類型
SQL> create table test_char_varchar(char_col char(20),varchar_col varchar2(20));
Table created
2. 向該表中的兩列都插入相關的數據
SQL> insert into test_char_varchar values('Hello World','Hello World');
1 row inserted
SQL> select * from test_char_varchar;
CHAR_COL VARCHAR_COL
-------------------- --------------------
Hello World Hello World
3. 以char_col列為條件查詢
SQL> select * from test_char_varchar where char_col ='Hello World';
CHAR_COL VARCHAR_COL
-------------------- --------------------
Hello World Hello World
4. 以varchar_col列為條件查詢
SQL> select * from test_char_varchar where varchar_col ='Hello World';
CHAR_COL VARCHAR_COL
-------------------- --------------------
Hello World Hello World
5.似乎char 和varchar類型沒有什么兩樣。再看看下面的語句。
SQL> select * from test_char_varchar where varchar_col =char_col;
CHAR_COL VARCHAR_COL
-------------------- --------------------
這已經看出他們并不一樣，這涉及到字符串比較的問題。
因為已經發生了隱式轉換,在與char列char_col進行比較時，char_col列的內容已經轉換成了char(20).在Hello World后面以空格進行填充了。而varchar_col列并沒有發生這種轉換。
如果要讓char_col列與varchar_col列相等。有兩種方法。
第一種是：使用trim把char_col列的空格去掉。
第二種是：使遙rpad把varchar_col列用空格進行填充長度為20的字符。
SQL> select * from test_char_varchar where trim(char_col) = varchar_col;
CHAR_COL VARCHAR_COL
-------------------- --------------------
Hello World Hello World
SQL> select * from test_char_varchar where char_col = rpad(varchar_col,20);
CHAR_COL VARCHAR_COL
-------------------- --------------------
Hello World Hello World
如果使用trim函數，如果char_col列上有索引，那么索引將不可用了。
此外還會在綁定變量時出現問題。
?
§1.5 NCHAR和NVARCHAR2
如果系統需要集中管理和存儲多種字符集，就需要使用這兩種字符類型。在使用NCAHR和NVARCHAR2時，文本內容采用國家字符集來存儲和管理。而不是默認字符集。
這兩種類型的長度指的是字符數，而不是字節數。
NLS國家語言支持(National Language Support)
在oracle 9i及以后的版本，數據庫的國家字符集可以是：utf-8和AL16UTF-16兩種。
Oracle 9i是utf -8, Oralce 10g是AL16UTF-16.
1.新建一個表,有兩列，類型分別為：nchar和nvarchar2.長度都為10
SQL> create table test_nvarchar(col_nchar nchar(10),col_nvarchar2 nvarchar2(10));
Table created
2.插入一些數據
SQL> insert into test_nvarchar values('袁','袁光東');
1 row inserted
SQL> insert into test_nvarchar values(N'袁',N'袁光東');
1 row inserted
(在9i之前的版本，插入時加上N時，在處理時跟普通方式有不同的方式。但是在10g的時候已經有了改變，加不加N都是一樣,這里只是為了測試)
SQL> insert into test_nvarchar values('a','b');
1 row inserted
插入一行英文字母
3. 查看每行的col_nchar列的存儲方式。
SQL> select col_nchar, dump(col_nchar) from test_nvarchar;
COL_NCHAR DUMP(COL_NCHAR)
-------------------- --------------------------------------------------------------------------------
袁 Typ=96 Len=20: 136,129,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32
a Typ=96 Len=20: 0,97,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32
袁 Typ=96 Len=20: 136,129,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32
Typ=96 與char的類型編碼一樣。
Len=20 每一行的長度都是20字節。這一點跟char一樣。都是定長的，會以空格填充。
需要注意的是：統統以兩位來表示一個字符。
136,129 表示’袁’
0,97 表示’a’
0,32 表示空格。
4. nvarchar2的儲存
SQL> select col_nvarchar2, dump(col_nvarchar2) from test_nvarchar;
COL_NVARCHAR2 DUMP(COL_NVARCHAR2)
-------------------- --------------------------------------------------------------------------------
袁光東 Typ=1 Len=6: 136,129,81,73,78,28
b Typ=1 Len=2: 0,98
袁光東 Typ=1 Len=6: 136,129,81,73,78,28
Typ=1 與varchar2一樣。
每一行的len值都不樣同。不會使用空格進行填充。
每一個字符都占有兩個字節兩進行存儲。
b 存儲為： 0, 98
袁 存儲為： 136,129
5.nchar和nvarchar2的數據定義。
SQL> desc test_nvarchar;
Name Type Nullable Default Comments
------------- ------------- -------- ------- --------
COL_NCHAR NCHAR(20) Y
COL_NVARCHAR2 NVARCHAR2(20) Y
雖然在定義nchar和nvarchar2時，指定的長度是指字符數。但是表結構的定義中，仍然是存儲著它的字節數。
在定義時nchar(10)表示可以最大存儲10個字符。
在查看數據表結構時，顯示該列最大占用的字節數。
需要注意的是：在char和nchar中對漢字的實際存儲值是不一樣的。因為采用了不同的字符集，就有了不同的字符編碼。
SQL> insert into test_varchar values('袁');
1 row inserted
SQL> select col, dump(col) from test_varchar where col='袁';
COL DUMP(COL)
---------- --------------------------------------------------------------------------------
袁 Typ=1 Len=2: 212,172
這時采用的字符集系統默認字符集ZHS16GBK。
這里很容易的把它轉換成ascii碼。
高位 * 256(2的8次方) + 低位.
212 * 256 + 172 = 54444
SQL> select chr(54444) from dual;
CHR(54444)
----------
袁
而在Nchar 和Nvarchar中，采用的是UTF-8或UTF-16的字符集。
SQL> insert into test_nvarchar values('袁','袁');
1 row inserted
SQL> select col_nvarchar2, dump(col_nvarchar2) from test_nvarchar where col_nvarchar2='袁';
COL_NVARCHAR2 DUMP(COL_NVARCHAR2)
-------------------- --------------------------------------------------------------------------------
袁 Typ=1 Len=2: 136,129
‘袁’存儲的值為：136,129
Oracle 10以上對nchar和nvarchar都采用utf-16字符集了。它的好處就是對字符采用固定長度的字節存儲(2字節),支持多國字符，在操作效率上會更高。但是它卻無法兼容于ascii碼。
§1.6 RAW
RAW與CHAR和VARCHAR2相比。RAW屬于二進制數據，更可以把它稱為二進制串。在對CHAR和VARCHAR2類型進行存儲時，會進行字符集轉換。而對二進制數據進行存儲則不會進行字符集轉換。
SQL> create table test_raw (col_chr varchar2(10), col_raw raw(10));
Table created
SQL> insert into test_raw values('aa','aa');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test_raw;
COL_CHR COL_RAW
---------- --------------------
aa AA
SQL> select col_chr,dump(col_chr) from test_raw;
COL_CHR DUMP(COL_CHR)
---------- --------------------------------------------------------------------------------
aa Typ=1 Len=2: 97,97
SQL> select col_raw,dump(col_raw) from test_raw;
COL_RAW DUMP(COL_RAW)
-------------------- --------------------------------------------------------------------------------
AA Typ=23 Len=1: 170
通過上面的分析，雖然我們通過select查詢得到的結果，raw列顯示為插入的字符。但是我們通過dump函數得知到raw并不是以字符的方式存儲。它是把插入的字符認為是16進制的值。
比如本例，我們向raw列插入aa,但是它占用的空間為1個字節。值為170.
170轉為16進制正好是aa
向raw列插入數據時會發生一個隱式轉換HEXTORAW
從raw列讀取數據時會發生一個隱式轉換RAWTOHEX
如果向raw列插入值不是有效的十六進制值時，會報錯的。
SQL> insert into test_raw values('h','h');
insert into test_raw values('h','h')
ORA-01465: invalid hex number
第二部分 數值類型
§ 2.1 number
Number類型是oralce的數值類型，存儲的數值的精度可以達到38位。Number是一種變長類型,長度為0-22字節。取值范圍為:10e-130 – 10e 126(不包括)
Number(p,s)
P和s都是可選的。
P指精度(precision)，即總位數。默認情況下精度為38。精度的取值范圍為1~38.
S指小數位(scale).小數點右邊的位數。小數點位數的合法值為-48~127。小數位的默認值由精度來決定。如果沒有指定精度，小數位默認為最大的取值區間.如果指定了精度，沒有指定小數位。小數位默認為0(即沒有小數位).
精度和小數位不會影響數據如何存儲，只會影響允許哪些數值及數值如何舍入。
1.新建一個表
SQL> create table test_number(col_number number(6,2));
Table created
2.插入一些不同的數據
SQL> insert into test_number values(-1);
1 row inserted
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(2);
1 row inserted
SQL> insert into test_number values(11.00);
1 row inserted
SQL> insert into test_number values(11.11);
1 row inserted
SQL> insert into test_number values(1234.12);
1 row inserted
SQL> insert into test_number values(-0.1);
1 row inserted
SQL> insert into test_number values(-11.11);
1 row inserted
SQL> insert into test_number values(-1234.12);
1 row inserted
SQL> commit;
Commit complete
3.查看結果
SQL> select * from test_number;
COL_NUMBER
----------
-1.00
0.00
1.00
2.00
11.00
11.11
1234.12
-0.10
-11.11
-1234.12
10 rows selected
5. 查看存儲結構
SQL> select col_number, dump(col_number) from test_number;
COL_NUMBER DUMP(COL_NUMBER)
---------- --------------------------------------------------------------------------------
-1.00 Typ=2 Len=3: 62,100,102
0.00 Typ=2 Len=1: 128
1.00 Typ=2 Len=2: 193,2
2.00 Typ=2 Len=2: 193,3
11.00 Typ=2 Len=2: 193,12
11.11 Typ=2 Len=3: 193,12,12
1234.12 Typ=2 Len=4: 194,13,35,13
-0.10 Typ=2 Len=3: 63,91,102
-11.11 Typ=2 Len=4: 62,90,90,102
-1234.12 Typ=2 Len=5: 61,89,67,89,102
10 rows selected
由此可見：
Number類型的內部編碼為：2
根據每一行的len值可以看出，number是一個變長類型。不同的數值占用不同的空間。
如果指定了精度，顯示結果與精度相關。
就像我插入語句寫為
insert into test_number values(0);
但是顯示結果為：0.00
如果數值是負數，在最后一位上填充一個補碼102.即表示該數值為負數。
0是一個特殊的值，它在oracle中存儲為128.
第一位為標志位。以128為比較。如果數值大于128,則它大于0。如果小于128小于0。
-1的內部存儲為：
-1.00 Typ=2 Len=3: 62,100,102
最后一位是102，是一個負數。
第一位小于128，所以小于10.
除了第一位標志位外，其它的都是數值為了。
如果該值是一個正數。每一位的存儲值減1為每一位的實際值。
1.0的存儲結構為：
1.00 typ=2 Len=2: 193,2
實值上1.00的存儲結果與1相同。
第一位193為標志位，大于128，大于0.
第二位為數值為，因為是正數，實際值為存儲值減1。2-1 = 1。
如是該值是一個負數，每一位的實際值為101 減去存儲的值。
-1.00的存儲結構為：
-1.00 Typ=2 Len=3: 62,100,102
最后一位102為補位。
第一位62為標志位，小于128。實際值小于0.
第二位為數值為，因為是負數。實際值為:101 – 100 =1.
§2.2 小數位在哪里？
從上面的存儲結果看，對小數存儲時，它并沒有一個小數的標志位。但是它實際上是由第一位標志位，和數值位(第二位)來決定的。
當存儲的數是一個正數,該數值的前幾位為：第一位 * power(100 , (標志位 - 193));
當存儲的數是一個負數，該數值的前幾位為：第一位 * power(100,(62 – 標志位));
11.11的存儲結果為：
11.11 Typ=2 Len=3: 193,12,12
第一位數值位為：12 實際數值為11
標志位為：193
12 * power(100, (193- 193);
100的零次方為1.
12 乘1 等于12.
所以這個數的前幾位為：12。從這后面就是小數了。
1234.12的存儲結構為：
1234.12 Typ=2 Len=4: 194,13,35,13
第一位數值位為：13,實際值為12
標志位為：193
13 * power(100,(194-193)) = 1300
所以前四位為整數位，后面的為小數位。
-0.10的存儲結構為：
-0.10 Typ=2 Len=3: 63,91,102
標志位為：63
第一位數值為：91 ，實際值為：10
91 * (100,(62-63)) =-9100.
所以小數位在91之前。
-1234.12的存儲結構為：
-1234.12 Typ=2 Len=5: 61,89,67,89,102
標志位為：61
第一位數值為：89
89*(100,(62-61)) =8900
所以小數位在67之后。
§2.3 number的精度和小數位
Number類型的精度最多可是38位。小數位-84--127位。
SQL> create table test_number1(col_number number(39));
create table test_number1(col_number number(39))
ORA-01727: numeric precision specifier is out of range (1 to 38)
指定小數位時，精度只能是1-38。不能是0
SQL> create table test_number1(col_number number(0,127));
create table test_number1(col_number number(0,127))
ORA-01727: numeric precision specifier is out of range (1 to 38)
SQL> create table test_number1(col_number number(1,128));
create table test_number1(col_number number(1,128))
ORA-01728: numeric scale specifier is out of range (-84 to 127)
精度與小數位的關系。精度并不是小數位加整數位之和。
我們先看看小數位為0的情況。
SQL> create table test_number1(col_char varchar2(200), col_num number(10));
Table created
Number(10).只定義了精度，小數位為0.
看看它可以存放的數據。
SQL> insert into test_number1 values('9999999999',9999999999);
1 row inserted
插入了10個9，沒有問題，再插入多一位看看
SQL> insert into test_number1 values('99999999991',99999999991);
insert into test_number1 values('99999999991',99999999991)
ORA-01438: value larger than specified precision allowed for this column
報錯了，精度不夠。
再看看能不能再插入小數？
SQL> insert into test_number1 values('0.9',0.9);
1 row inserted
SQL> select * from test_number1;
Col_char COL_NUM
-------------------- --------------
9999999999 9999999999
0.9 1
注意插入數值0.9后，存儲為1.這就是小數位的作用。在哪里進行舍入。
帶小數位和精度的情況。
SQL> create table test_number2(col_char varchar(20),col_num number(1,3));
Table created
精度是1，小數位是3.
可見，精度不是小數位加整數位了。但是精度和小數位倒底什么關系呢？
SQL> insert into test_number2 values('0.111',0.111);
insert into test_number2 values('0.111',0.111)
ORA-01438: value larger than specified precision allowed for this column
插入3位小數,0.111竟然報錯了，說精度不夠。
SQL> insert into test_number2 values('0.001',0.001);
1 row inserted
插入0.001時，成功了。
SQL> insert into test_number2 values('0.001',0.0015);
1 row inserted
插入0.0015也成功了。
看看插入的值。
SQL> select * from test_number2;
COL_CHAR COL_NUM
-------------------- -------
0.001 0.001
0.0015 0.002
需要注意的是0.0015被舍入為0.002
精度大于小數位
SQL> create table test_number3 (col_char varchar(20), col_number number(5,3));
Table created
SQL> insert into test_number3 values('99.899',99.899);
1 row inserted
SQL> insert into test_number3 values('99.999',99.999);
1 row inserted
SQL> insert into test_number3 values('99.9999',99.9999);
insert into test_number3 values('99.9999',99.9999)
ORA-01438: value larger than specified precision allowed for this column
注意，當插入99.9999時，系統報錯。因為小數位為3位。第四位小數位是9,于是往前入。最終變成100.000.就已經超過了精度。
Number(5,3)可存儲的數值最大為99.999.
現在終于有點明白小數位與精度的關系了。
number(38,127)
可以存儲的最大小數為：127位小數，最后38為9.
即：0.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000099999999999999999999999999999999999999
小數位為負數。
我們從前面知道，小數位的取值為-48 ~127
為什么小數位會為負數？這有點怪異了。像上面的number(5,3)將值舍入為最接近0.001
Number(5,-3)就是將值舍入為最接近的1000
SQL> create table test_number5 (col_char varchar(20), col_num number(5,-3));
Table created
插入值10999
SQL> insert into test_number5 values('10999',10999);
1 row inserted
查看一下結果
SQL> select * from test_number5;
COL_CHAR COL_NUM
-------------------- -------
10999 11000
存儲的結果為：11000
當小數部分為負數時，是對小數部分進行舍入。
那么精度在這時起到什么作用呢？與小數位又有什么關系？
SQL> insert into test_number5 values('111111111',111111111);
insert into test_number5 values('111111111',111111111)
ORA-01438: value larger than specified precision allowed for this column
插入9個1時，報錯精度不夠。
SQL> insert into test_number5 values('11111111',11111111);
1 row inserted
插入8個1時，正確插入。
我們看看它的結果，看它是怎么舍入的。
SQL> select * from test_number5;
COL_CHAR COL_NUM
-------------------- -------
11111111 11111000
結果是1111100而不是1111100
無限接近1000，就是從百位開始進行四舍五入，后面的值全部為0。
所以看出number(5,-3)可存儲的最大值為：99999000
SQL> insert into test_number5 values('99999499.999999',99999499.999999);
1 row inserted
SQL> select * from test_number5;
COL_CHAR COL_NUM
-------------------- -------
99999999 99999000
99999499.999999 99999000
現在應該明白了精度和小數位的關系了吧。
小數位告訴系統保留多少位小數，從哪里開始舍入。
精度舍入后，從舍入的位置開始，數值中允許有多少位。
§2.4 binary_float 和binary_double
這兩種類型是oracle 10g新引進的數值類型。在oracle 10g之前是沒有這兩種類型的。
Number類型是由oracle軟件支持的類型。而浮點數用于近似數值。但是它浮點數允許由在硬盤上(CPU,芯片)上執行運行。而不是在oracel進程中運算。如果希望在一個科學計算中執行實數處理，依賴于硬件的算術運算速度要快得多。但是它的精度卻很小。如果希望用來存儲金融數值，則必須用number.
BINARY_FLOAT是一種IEEE固有的單精度浮點數?？纱鎯?位精度，取值范圍在~±1038.25的數值。
BINARY_DOUBLE是一種IEEE固有的雙精度浮點數?？纱鎯?2位精度。取值范圍在~±10308.25的數值
SQL> create table test_floatdouble(col_number number, col_float binary_float, col_double binary_double);
Table created
SQL> insert into test_floatdouble values(9876543210.0123456789,9876543210.0123456789,9876543210.0123456789);
1 row inserted
2 SQL> select to_char(col_number), to_char(col_float), to_char(col_double) from test_floatdouble;
3
4 TO_CHAR(COL_NUMBER) TO_CHAR(COL_FLOAT) TO_CHAR(COL_DOUBLE)
5 ---------------------------------------- ---------------------------------------- ----------------------------------------
6 9876543210.0123456789 9.87654349E+009 9.8765432100123463E+009
由此可見，binary_float無法表示這個數。Binary_float和binary_double無法用于對精度要求高的數據。
SQL> select dump(col_float)from test_floatdouble;
DUMP(COL_FLOAT)
--------------------------------------------------------------------------------
Typ=100 Len=4: 208,19,44,6
BINARY_FLOAT 類型編碼為100
Len=4 占用4個字節。它是采用固定字節進行存儲的。
SQL> select dump(col_double)from test_floatdouble;
DUMP(COL_DOUBLE)
--------------------------------------------------------------------------------
Typ=101 Len=8: 194,2,101,128,183,80,25,73
BINARY_DOUBLE 類型編碼為101
Leng= 8 占用8個字節。也是采用固定字節進行存儲。
注意：number 類型使用的CPU時間是浮點數類型的50倍。浮點數是數值的一個近似值，精度在6-12位之間。從Number類型得到的結果要比從浮點數得到的結果更精確。但在對科學數據進行數據挖掘和進行復雜數值分析時，精度的損失是可以接受的，還會帶來顯著的性能提升。
這時需要使用內置CAST函數，對NUMBER類型執行一種實時的轉換，在執行復雜數學運算之前先將其轉換為一種浮點數類型。CPU使用時間就與固有浮點類型使用的CPU時間非常接近了。
Select ln(cast(number_col as binary_double)) from test_number.
§2.5 Oracle在語法上還支持的數值數據類型
NUMERIC(p,s):完全映射到NUMBER(p,s)。如果p未指定，則默認為38.
DECIMAL(p,s)或DEC(p,s):同NUMERIC(p,s).
INTEGER或int:完全映射至NUMBER(38)
SMALLINT:完全映射至NUMBER(38)
FLOAT(b):映射至NUMBER
DOUBLE PRECISION:映射到NUMBER
REAL:映射到NUMBER.
第三部分 日期時間類型
§3.1 DATE
Date類型Oralce用于表示日期和時間的數據類型。固定占用7個字節。
包括七個屬性：
世紀
世紀中的年份
月份
月份中的哪一天
小時
分
秒
SQL> create table test_date(col_date date);
Table created
SQL> insert into test_date values(to_date('2008-06-27 10:35:00','yyyy-mm-dd hh24:mi:ss'));
1 row inserted
SQL> select to_char(col_date,'yyyy-mm-dd hh24:mi:ss'),dump(col_date) from test_date;
TO_CHAR(COL_DATE,'YYYY-MM-DDHH DUMP(COL_DATE)
------------------------------ --------------------------------------------------------------------------------
2008-06-27 10:35:00 Typ=12 Len=7: 120,108,6,27,11,36,1
Date類型的內部編碼為12
長度:占用7個字節
數據存儲的每一位到第七位分別為：世紀，年，月，日，時，分，秒。
世紀：采用”加100”表示法來存儲。即世紀+100來存儲。120 – 100 = 20
年:跟世紀一樣采用”加100”表示法來存儲。108 – 100 = 08(采用兩位表示)
月:自然存儲.6
日:自然存儲，不做修改，27
時：（時，分，秒都采用“加1”法存儲）11 -1= 10
分:36 -1 = 35
秒:1 -1 = 0
為什么世紀和年份要用加100法存儲呢？是為了支持BC和AD日期。
BC即為公元前。
AD即為公元。
如果世紀 – 100為一個負數，那么就是一個BC日期。
插入一個公元前日期
SQL> insert into test_date values(to_date('-4712-01-01','syyyy-mm-dd hh24:mi:ss'));
1 row inserted
SQL> select to_char(col_date,'bc yyyy-mm-dd hh24:mi:ss'),dump(col_date) from test_date;
TO_CHAR(COL_DATE,'BCYYYY-MM-DD DUMP(COL_DATE)
------------------------------ --------------------------------------------------------------------------------
公元 2008-06-27 10:35:00 Typ=12 Len=7: 120,108,6,27,11,36,1
公元前 4712-01-01 00:00:00 Typ=12 Len=7: 53,88,1,1,1,1,1
我們已經了解了日期的存儲結構。當要對日期進行截取時，比如去掉時，分，秒。只需要把最后的三個字節設為：12 12 1就可以了。
SQL> create table test_date1 (col_char varchar2(12), col_date date);
Table created
SQL> insert into test_date1 values('full',to_date('2008-06-27 12:01:00','yyyy-mm-dd hh24:mi:ss'));
1 row inserted
SQL> insert into test_date1(col_char,col_date) select 'minute', trunc(col_date,'mi') from test_date1
2 union all
3 select 'day', trunc(col_date,'dd') from test_date1
4 union all
5 select 'month',trunc(col_date,'mm') from test_date1
6 union all
7 select 'year',trunc(col_date,'y') from test_date1
8 ;
4 rows inserted
SQL> select col_char, col_date,dump(col_date) from test_date1;
COL_CHAR COL_DATE DUMP(COL_DATE)
------------ ----------- --------------------------------------------------------------------------------
full 2008-6-27 1 Typ=12 Len=7: 120,108,6,27,13,2,1
minute 2008-6-27 1 Typ=12 Len=7: 120,108,6,27,13,2,1
day 2008-6-27 Typ=12 Len=7: 120,108,6,27,1,1,1
month 2008-6-1 Typ=12 Len=7: 120,108,6,1,1,1,1
year 2008-1-1 Typ=12 Len=7: 120,108,1,1,1,1,1
要把一個日期截取，只取到年。數據庫只是把最后5個字節置上1。這是非常快的。
當我們對一個Date字段進行操作，需要截取到年份進行比較時，我們經常使用to_char函數。通過會這樣寫。
Select * from test_date1 where to_char(col_date ,’yyyy’) = ‘2008’
而不是
Select * from test_date1 where trunc(col_date,’y’) = to_date(‘2008-01-01’,’yyyy-mm-dd’)
使用trunc會占用更少的資源，性能更優。
使用to_char所有的CPU時間與trunc相差一個數量級，差不多10倍。因為to_char必須把日期轉換成一個串，并利用當前系統所采用的NLS來完成，然后執行一個串與串的比較。而TRUNC只需要把后5個字節設置為1，然后將兩個7位的字節的二進行數進行比較就搞定了。所要截取一個DATE列葉，應該避免使用to_char.
另外，要完全避免對DATE列應用函數。比如我們要查詢2008年的所有數據，并且這一列上也有索引，我們希望能夠用上這個索引。
SQL> select count(col_date) from test_date1 where col_date >= to_date('2008-01-01','yyyy-mm-dd') and col_date < to_date('2009-01-01','yyyy-mm-dd');
COUNT(COL_DATE)
---------------
5
§3.2 向Date類型增加或減時間
怎么向Date類型增加時間，例如：向Date增加1天，或1小時，或1秒，一月等。
常有的辦法有幾個方法：
a.向date增加一個NUMBER值。因為Date 加減操作是以天為單位。1秒就是 1/24/60/60。依此類推。
b.使用INTERVAL類型。后續會介紹
c.使用內置函數add_months增加月。增加月不像增加天那么簡單，所以需要使用內置函數來處理。
3.2.1 增加秒
SQL> create table test_date2(id varchar2(10), operate_time date);
Table created
SQL> insert into test_date2 values('1',sysdate);
1 row inserted
SQL> select id, to_char(operate_time, 'yyyy-mm-dd hh24:mi:ss') from test_date2 where id=1;
ID TO_CHAR(OPERATE_TIME,'YYYY-MM-
---------- ------------------------------
1 2008-06-27 13:35:35
SQL> update test_date2 set operate_time = operate_time + 1/24/60/60 where id=1;
1 row updated
SQL> select id, to_char(operate_time, 'yyyy-mm-dd hh24:mi:ss') from test_date2 where id=1;
ID TO_CHAR(OPERATE_TIME,'YYYY-MM-
---------- ------------------------------
1 2008-06-27 13:35:36
3.2.2 增加分
SQL> update test_date2 set operate_time = operate_time + 1/24/60 where id=1;
1 row updated
SQL> select id, to_char(operate_time, 'yyyy-mm-dd hh24:mi:ss') from test_date2 where id=1;
ID TO_CHAR(OPERATE_TIME,'YYYY-MM-
---------- ------------------------------
1 2008-06-27 13:36:36
3.2.3 增加小時
SQL> update test_date2 set operate_time = operate_time + 1/24 where id=1;
1 row updated
SQL> select id, to_char(operate_time, 'yyyy-mm-dd hh24:mi:ss') from test_date2 where id=1;
ID TO_CHAR(OPERATE_TIME,'YYYY-MM-
---------- ------------------------------
1 2008-06-27 14:36:36
3.2.4 增加天
SQL> update test_date2 set operate_time = operate_time + 1 where id=1;
1 row updated
SQL> select id, to_char(operate_time, 'yyyy-mm-dd hh24:mi:ss') from test_date2 where id=1;
ID TO_CHAR(OPERATE_TIME,'YYYY-MM-
---------- ------------------------------
1 2008-06-28 14:36:36
3.2.4 增加周
SQL> update test_date2 set operate_time = operate_time + 1 * 7 where id=1;
1 row updated
SQL> select id, to_char(operate_time, 'yyyy-mm-dd hh24:mi:ss') from test_date2 where id=1;
ID TO_CHAR(OPERATE_TIME,'YYYY-MM-
---------- ------------------------------
1 2008-07-05 14:36:36
3.2.5 增加月
SQL> update test_date2 set operate_time = add_months(operate_time,1) where id=1;
1 row updated
SQL> select id, to_char(operate_time, 'yyyy-mm-dd hh24:mi:ss') from test_date2 where id=1;
ID TO_CHAR(OPERATE_TIME,'YYYY-MM-
---------- ------------------------------
1 2008-08-05 14:36:36
3.2.6 增加年
SQL> update test_date2 set operate_time = add_months(operate_time,1 * 12) where id=1;
1 row updated
SQL> select id, to_char(operate_time, 'yyyy-mm-dd hh24:mi:ss') from test_date2 where id=1;
ID TO_CHAR(OPERATE_TIME,'YYYY-MM-
---------- ------------------------------
1 2009-08-05 14:36:36
另外可以使用一個非常有用的函數NUMTODSINTERVAL來新增加小時，分鐘，秒。
SQL> update test_date2 set operate_time = operate_time + numtodsinterval(1,'second') where id=1;
1 row updated
SQL> select id, to_char(operate_time, 'yyyy-mm-dd hh24:mi:ss') from test_date2 where id=1;
ID TO_CHAR(OPERATE_TIME,'YYYY-MM-
---------- ------------------------------
1 2009-08-05 14:36:37
Numtodsinterval(n, 'second') 獲得秒的時間間隔
Numtodsinterval(n, 'minute') 獲得分的時間間隔
Numtodsinterval(n, 'month') 獲得月的時間間隔
Numtodsinterval(n, 'year') 獲得月的時間間隔
增加月份時要非常的小心，應該使用add_months函數。為什么呢？
比如當前日期為2000-2-29日。增加一個月得到的日期就應該是2000-3-31
如果只是簡單的加30天或加31天,是無法實現的。所以必須使用add_months函數，它會自動來處理這種月末問題。對年份進行增加也會出現類似的問題
§3.3 TIMESTAMP
TIMESTAMP是支持小數秒和時區的日期/時間類型。對秒的精確度更高。
3.3.1 TIMESTAM語法
TIMESTAMP(n)
N的取值為0~9.表示指定TIMESTAMP中秒的小數位數。N為可選。如果n為0，timestamp與date等價。
SQL> create table test_timestamp(col_date date, col_timestamp timestamp(0));
Table created
SQL> insert into test_timestamp values(sysdate,systimestamp);
1 row inserted
SQL> select dump(col_date) from test_timestamp;
DUMP(COL_DATE)
--------------------------------------------------------------------------------
Typ=12 Len=7: 120,108,6,27,17,8,37
SQL> select dump(col_timestamp) from test_timestamp;
DUMP(COL_TIMESTAMP)
--------------------------------------------------------------------------------
Typ=180 Len=7: 120,108,6,27,17,8,38
如果指定了保留小數位數，那情況就大不一樣了。
SQL> create table test_timestamp1 (col_date date, col_timestamp timestamp(9));
Table created
SQL> insert into test_timestamp1 values(sysdate, systimestamp);
1 row inserted
SQL> select dump(col_date) from test_timestamp1;
DUMP(COL_DATE)
--------------------------------------------------------------------------------
Typ=12 Len=7: 120,108,6,27,17,36,40
SQL> select dump(col_timestamp) from test_timestamp1;
DUMP(COL_TIMESTAMP)
--------------------------------------------------------------------------------
Typ=180 Len=11: 120,108,6,27,17,36,40,17,249,15,24
現在可以看到timestamp(9)占用了11個字節的空間。后面額外的四個字節包括了小數秒數。
3.3.2 TIMESTAMP 相減
將兩個Date相減的結果是一個number.而將兩個timestamp相減的結果是一個INTERVAL值
SQL> create table test_timestamp2(time1 timestamp(9), time2 timestamp(9));
Table created
SQL> insert into test_timestamp2 values(to_timestamp('2008-06-29 01:02:01.100000','yyyy-mm-dd hh24:mi:ss.ff'),to_timestamp('2008-07-29 02:03:02.000000','yyyy-mm-dd hh24:mi:ss.ff'))
2 ;
1 row inserted
SQL> select time2 - time1 from test_timestamp2;
TIME2-TIME1
---------------------------------------
+000000030 01:01:00.900000000
結果表示兩個時間之間相隔的天數,小時數,分數,秒數.
相差30天1小時1分0.9秒
有時我們需要得到兩個時間之前相關的年數和月數.
SQL> select numtoyminterval(months_between(time2,time1),'month') years_months, time2 -
2 add_months(time1 , trunc(months_between(time2,time1))) days_hours from test_timestamp2;
YEARS_MONTHS DAYS_HOURS
--------------------------------------- ---------------------------------------
+000000000-01 +000000000 01:01:01.000000000
在計算時,分,秒間隔時我們注意到,使用add_months之后,小數秒就丟掉了.
如果要保留集小數秒,我們就需要使用numtoyminterval函數
SQL> select numtoyminterval(months_between(time2,time1),'month') years_months, time2 -(time1 + numtoyminterval(trunc(months_between(time2,time1)),'month')) day_hours from test_timestamp2;
YEARS_MONTHS DAY_HOURS
--------------------------------------- ---------------------------------------
+000000000-01 +000000000 01:01:00.900000000
§3.4 TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH TIME ZONE類型是TIMESTAMP的子類型，增加了時區支持。
SQL> create table test_timezone(col_ts timestamp, col_tz timestamp with time zone);
Table created
SQL> insert into test_timezone values(systimestamp, systimestamp);
1 row inserted
SQL> select dump(col_tz) from test_timezone;
DUMP(COL_TZ)
--------------------------------------------------------------------------------
Typ=181 Len=13: 120,108,6,27,9,55,24,43,209,96,112,28,60
SQL> select dump(col_ts) from test_timezone;
DUMP(COL_TS)
--------------------------------------------------------------------------------
Typ=180 Len=11: 120,108,6,27,17,55,24,43,209,96,112
占用13字節的存儲空間，最后兩位用于保存時區信息。
在timestamp類型中，對時，分，秒的存儲采用了加1法。
在timestamp with time zone上執行timestamp運算時，oracle自動把兩個類型首先轉換為UTC時間，然后再執行運算。
§3.5 TIMESTAMP WITH LOCAL TIME ZONE
這個類型保存進數據庫時會先轉換成數據庫時區再進行保存.
SQL> create table test_timeltz(col_date date, timetz timestamp with time zone, timeltz timestamp with local time zone);
Table created
SQL> insert into test_timeltz values(timestamp'2008-06-29 12:03:22.111 US/Pacific',timestamp'2008-06-29 12:03:22.111 US/Pacific',timestamp'2008-06-29 12:03:22.111 US/Pacific');
1 row inserted
SQL> select dbtimezone from dual;
DBTIMEZONE
----------
+08:00
SQL> select * from test_timeltz;
COL_DATE TIMETZ TIMELTZ
----------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
2008-6-29 1 2008-06-29 12:03:22.111000 US/PACIFIC 2008-06-30 03:03:22.111000
SQL> select dump(col_date), dump(timetz), dump(timeltz) from test_timeltz;
DUMP(COL_DATE) DUMP(TIMETZ) DUMP(TIMELTZ)
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
Typ=12 Len=7: 120,108,6,29,13,4,23 Typ=181 Len=13: 120,108,6,29,20,4,23,6,157,185,192,137,156 Typ=231 Len=11: 120,108,6,30,4,4,23,6,157,185,192
請注意:
第一列,類型為date,只存儲了日期和時間.時區和小數秒已經丟失了.不會執行時區轉換.
第二列:類型為timestamp with time zone.保留了時區信息.并規范化了該時區相應的UTC時間.
第三列:類型為timestamp with local time zone.進行了轉換,把插入的時間轉為了數據庫時區的時間.
timestamp with local time zone也是不需要記錄時區信息的.它占用7-11個字節.
一旦你的數據表中有一列使用了timestamp with local time zone,你就不能再改變數據庫時區.
通用協調時(UTC, Universal Time Coordinated) ,UTC與格林尼治平均時(GMT, Greenwich Mean Time)一樣，都與英國倫敦的本地時相同. 北京時區是東八區，領先UTC八個小時
§3.6 INTERVAL
用于表示一段時間或一個時間間隔的方法.在前面有多次提過.INTERVAL有兩種類型.
YEAR TO MONTH 能存儲年或月指定的一個時間段.
DATE TO SECOND存儲天,小時,分鐘,秒指定的時間段.
在前面用到的兩個函數numtoyminterval 和numtodsinterval就是創建interval最好的方法.
另外extract 函數可以很容易的獲得時間的每個部分.
SQL> select extract(day from time2-time1) day, extract(hour from time2 - time1) hour,
2 extract (minute from time2 - time1) minute,extract (second from time2 - time1) second from
3 test_timestamp2;
DAY HOUR MINUTE SECOND
---------- ---------- ---------- ----------
30 1 1 0.9
3.6.1 Interval year to month
語法:
Interval year(n) to month
N表示年數的位數.取值:0~9 .默認為2,表示年數為0 ~ 99
如果要產生一個1年2個月的時間段.用numtoyminterval是最方便的.
SQL> select (numtoyminterval(1,'year') + numtoyminterval(2,'month')) yminterval from dual;
YMINTERVAL
---------------------------------------
+000000001-02
或者是:
SQL> select numtoyminterval(1 * 12 + 2,'month') yminterval from dual;
YMINTERVAL
---------------------------------------
+000000001-02
另外可以使用 to_yminterval(‘1-2’)函數來進行轉換.
SQL> create table test_intervarym(col_interval interval year to month);
Table created
SQL> insert into test_intervarym values ( numtoyminterval(1 * 12 + 2,'month'));
1 row inserted
SQL> select * from test_intervarym;
COL_INTERVAL
---------------------------------------
+01-02
SQL> select dump(col_interval) from test_intervarym;
DUMP(COL_INTERVAL)
--------------------------------------------------------------------------------
Typ=182 Len=5: 128,0,0,1,62
INTERVAL year to month 采用固定5個字節進行存儲.最后一位為天數值.采用加60算法.所以計算是需要減去60.
第一位為標志位,標志是否為正負數.
第二到第四位表示年數.
第五位表示日數
3.6.2 INTERVAL DAY TO SECOND
定義如下:
INTERVAL DAY(n) to second(m)
N為可選位數,表示天數的位數.可取值0~9,默認為2位.
M是秒字段小時的位數.取值0~9,默認為6
SQL> create table test_intervalds(col_ds interval day(9) to second(9));
Table created
SQL> insert into test_intervalds values(numtodsinterval(1,'second'));
1 row inserted
SQL> insert into test_intervalds values(numtodsinterval(1.000000001,'second'));
1 row inserted
SQL> select col_ds, dump(col_ds) from test_intervalds;
COL_DS DUMP(COL_DS)
--------------------------------------- --------------------------------------------------------------------------------
+000000000 00:00:01.000000000 Typ=183 Len=11: 128,0,0,0,60,60,61,128,0,0,0
+000000000 00:00:01.000000001 Typ=183 Len=11: 128,0,0,0,60,60,61,128,0,0,1
可見,這種類型也是采用固定11個字節來存儲的.
第一位為標志位,區分正負數
第二到第四位表示天數.
第五位表示小時數.時,分,秒采用加60算法
第六位表示分鐘數,
第七位表示秒數.
最后四位表示小數秒數.
第四部分 LOB類型
§ 4.1 LOB類型
4.1.1 LOB類型分類
CLOB:字符LOB.用于存儲大量的文本信息.采用默認字符集存儲
NCLOB:用于存儲字符LOB,采用數據庫的國家字符集來存儲字符.而不是數據庫的默認字符集.
BLOB:二進制LOB,存儲二進大量的二進制信息.存儲時不會進行字符集轉換.
CLOB和BLOG在ORACLE 10G中可存儲8TB字節.
BFILE:二進制文件LOB,只是一個文件指針.具體的文件存儲在操作系統中.
4.1.2 LOB類型存儲方式
我們把CLOB,NCLOB,BLOB存儲在數據庫的內部稱為內部LOB.這些存儲方式都相似,所以可以一起進行討論.
SQL> create table test_lob (id int primary key,remark clob);
Table created
對于LOB列的創建有非常多的選項.可以查ORACLE文檔.
最簡單的就是使用dbms_metadata來獲得它的完整的腳本.
select dbms_metadata.get_ddl('TABLE','TEST_LOB') from dual;
得到如下結果
CREATE TABLE "YUAN"."TEST_LOB"
( "ID" NUMBER(*,0),
"REMARK" CLOB,
PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE
) 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"
LOB ("REMARK") STORE AS (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
LOB列的定義可以有以下屬性.
存儲的表空間,本例為USER.也就是說可以為LOB單獨指定表空間.
ENABLE STORAGE IN ROW 默認的一個屬性
CHUNK 屬性
PCTVERSION 屬性
NOCACHE 屬性.
一個完整的STORAGE語句.
可見,LOB類型之前介紹的數據類型相比要復雜得多了.
當我們創建了一個帶的LOB列的表后,我們可以從USER_SEGMENTS查到,數據庫增加了幾個段對象.
SQL> select segment_name,segment_type from user_segments;
SEGMENT_NAME SEGMENT_TYPE
--------------------------------- ------------------
BIN$nZwCJWDmQM+ygfB1U8tcIw==$0 TABLE
BIN$0jfW0nNQR/2JEQmbAmfcRQ==$0 TABLE
TEST_TIMESTAMP TABLE
TEST_TIMESTAMP2 TABLE
TEST_TIMESTAMPWZ TABLE
TEST_TIMELTZ TABLE
TEST_INTERVARYM TABLE
TEST_INTERVALYM2 TABLE
TEST_INTERVALDS TABLE
TEST_LOB TABLE
SYS_LOB0000043762C00002$$ LOBSEGMENT
SYS_IL0000043762C00002$$ LOBINDEX
SYS_C004324 INDEX
后面四個段空間對象.新增了四個物理段.普通表只會新增一個或兩個段對象.類型為TABLE和INDEX.
而LOB列則額外新增了兩個段對象,類型為LOBSEGMENT和LOBINDEX.
SYS_C004324是一個索引段,因為我們有一列為主鍵.
作為普通字段,數據就存放在表段中.索引就放在索引段中.
而對于LOB數據,數據并不是存在表段中,而是存放在LOBSEGMENT段中.(有些情況下是存放在表test_lob中的.后面會講)
LOBINDEX用于指向LOB段,找出其中的某一部分.
所以存儲在表中的LOB存儲的是一個地址,或者說是一個指針,也可以說是一個LOB定位器(LOB locator).
存儲在LOBindex中的應該是每一個LOB行的地址.數據是具體存儲在LOBSEGMENT中的.
我們先從TEST_LOB的LOB列中找到一個地址,然后在LOBINDEX中來查找這些字節存儲在哪里.然后再訪問LOBSEGMENT.由此我們可以把lobindex和lobsegment想成是一個主/細表的關系.
實際上lob列中存的是一個地址段.然后在lobindex找到所有的地址段.然后在lobSegment中把所有地址段的值都讀取了來
4.1.3 LOB類型存儲參數介紹
在此,我們已經基本了解了LOB是怎么存儲的.我們也從腳本中看到了LOB類型的參數.現在我們就來了解這些參數
1. LOB表空間
LOB ("REMARK") STORE AS (
TABLESPACE "USERS"
在test_lob表中的create語句中包含上面的語句.這里指定的表空間指的是存儲lobindex 和lobsegment的表空間.也就是說,存放lob數據與LOB列所在的表是可以在不同的表空間的.
數據表和LOB存放在不同的表空間.
為什么LOB數據會放在不同的表空間呢?這主要還是管理和性能的問題.
LOB數據類型代表了非常巨大的容量.在ORACLE 10G之前,LOB列可以存放4GB字節的數據.在ORACLE 10G 中LOB類型可以存放8TB字節的數據.這是非常龐大的數據.
所以就有必要為LOB數據使用一個單獨的表空間,對于備份和恢復以及空間管理.你甚至可以讓LOB數據使用另外一個區段大小,而不是普通表數據所用的區段大小.
另外從I/O性能的角度考慮.LOB是不在緩沖區緩存中進行緩存.因此每個LOB的讀寫,都會產生物理I/O.正因為如此,如果我們很清楚在實際的用戶訪問中,有些對象會比大部分其它對象需要花費更多的物理I/O,那么就需要把這些對象分離到其它的磁盤.
另外,lobindex 和lobsegment是在同一個表空間中的.不可以把lobindex和lobsegment放在不同的表空間中.在oracle 8i之前版本,允許將lobindex和lobsegment放在不同的表空間中.
2. IN ROW 語句
LOB ("REMARK") STORE AS (
TABLESPACE "USERS" ENABLE STORAGE IN ROW
我們已經了解了LOB類型的存儲結構,但是這種結構會帶來額外的磁盤訪問.不管是讀還是寫都會比普通數據類型要慢及帶來更多的物理I/O.
針對這種情況,ORALCE作出了個改進就是IN ROW 語句.
使用ENABLE STORAGE IN ROW從字面上理解就是允許行內存儲.當LOB的內容小于4000字節時,就把數據存儲在數據表中的,即LOB數據與數據表都是同一個表空間中.這里的LOB就相當于VARCHAR2一樣,這里LOB列的數據還可以進入緩沖區進行存儲.當LOB內容超過了4000字節后,就會把數據移到lobsegment中去.
當定義一個LOB列時,它的大小一般都是小于4000字節的,啟用IN ROW 是非常重要的.
如果要禁用IN ROW ,就使用DISALBE STORAGE IN ROW
3. CHUNK 參數
LOB ("REMARK") STORE AS (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
CHUNK 意為大塊,塊.是指LOB存儲的單位.指向LOB數據的索引會指向各個數據塊.CHUNK是邏輯上連續的一組數據塊.CHUNK是指LOB的最小分配單元.而數據庫的最小內存分配單元是數據塊(BLOCK).CHUNK大小必須是ORACLE塊大小的整數倍.
我們先來了解一下LOB與CHUNK的關系.
1. 每一個LOB實例(即每一行的LOB值)會至少占用一個CHUNK.
用我們本節的數據表test_lob為例,remark列為LOB類型.
假設該表有1000行數據,每一行的remark列的值大小都為7KB.
這樣數據庫就會分配1000個CHUNK.如果CHUNK的大小設置是64KB,就會分配1000個64KB的CHUNK.如果CHUNK的大小為8KB,就分配1000個8KB的CHUNK.
重要的一點就是一個CHUNK只能由一個LOB對象使用.這有一點像CHAR這種定長類型.如果把CHUNK設為64KB,而實際上我們每一個LOB對象只有7KB的大小,每一列浪費57KB的空間.1000列就浪費了55M的空間.而把CHUNK設為8KB,1000列大約浪費1M的空間.
我們還知道lobindex,且于指向各個塊.它會記錄每個塊的地址.所以當塊越多時,索引就越大,索引越大時,讀寫就會更慢.整體的性能就會降低.
比如每個列的LOB字段實際值大約8M,使用8KB的CHUNK.那么就需要1024個CHUNK.那么在lobindex中就會有1024條記錄,用來指向這些CHUNK.
指定CHUNK值,影響到性能和空間.
如果CHUNK過大,就會白白浪費存儲空間,如果CHUNK過小,就會降低性能.
所以我們需要在空間和性能上進行取舍和折中.
4. PCTVERSION 語句
LOB ("REMARK") STORE AS (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
PCTVERSION用于控制LOB的讀一致性.普通字段都會有UNDO記錄的.而lobsegment是沒有undo記錄的.而是直接在lobsegment本身中維護停息的版本.lobindex會像其它段一樣生成undo記錄.但是lobsegment不會.
修改一個LOB對象時,oracle會分配一個新的CHUNK,而來的CHUNK會被保留下來.如果事務正常的提交了,lobindex就像指向新的CHUNK.如果事務被回滾了,lobindex就再指回原來的CHUNK.所以undo維護是在LOB段自身中實現的.
這樣一來,就會有非常多的無用的CHUNK被開銷了.這也是非常大的空間損耗.這些CHUNK指的是數據的舊版本信息.那如何來控制這些舊版本數據占用的空間呢?這就是PCTVERSION的作用.也就是說用多少額外的空間來存儲舊版本數據.我們可以看到默認的值是10%.如果你確實經常修改LOB,那么就需要把它設為10%就不夠了,需要增加這個值.
5. CACHE參數
LOB ("REMARK") STORE AS (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE
除了NOCACHE外,這個選項還可是CACHE和CACHE READS.這個參數控制lobsegment數據是否存儲在緩沖區的緩存中.默認為NOCACHE,也就是每次訪問都是從磁盤直接讀取寫.
CACHE READS允許緩存從磁盤讀的LOB數據.但是寫入LOB數據是直接寫進磁盤的.
CACHE則是允許讀和寫都能緩存LOB數據.
有些情況下,LOB字段只有幾KB大小,進行緩存就非常有用了.如果不緩存,當用戶更新LOB字段時,還必須進行等待,從磁盤直接讀數據和寫數據.
如果要修改緩存設置可以用下面的語句
ALTER TABLE test_lob modify LOB(remark) (CACHE);
ALTER TABLE test_lob modify LOB(remark) (NOCACHE);
ALTER TABLE test_lob modify LOB(remark) (CACHEREADS);
但是對于大數據量的LOB讀寫,比如超過了20M.是沒有理由把它放進緩存的
§ 4.2 BFILE
BFILE類型只是操作系統上一個文件的指針.用于對存儲在操作系統中的文件提供只讀訪問.
使用BFILE時,還可以使用一個DIRECTORY 對象.DIRECTORY 是將一個操作系統目錄映射到數據庫的一個串.以便于提供可移值性.
SQL> create table test_bfile(id int primary key, moviefile bfile);
Table created
SQL> create or replace directory movie_directory as 'D:/movie';
Directory created
SQL> insert into test_bfile values(1,bfilename('movie_directory','英雄.dat'));
1 row inserted
對BFILE的操作需要使用DBMS_LOB包來進行.提供了一系統方法和函數
第五部分 LONG類型
LONG是一種已經被棄用的數據類型,LOB類型是它的替代品.所以留在LOB之后進行討論.
我們只需要簡單的了解即可.為什么ORACLE還保留這種類型,只是為了向后兼容,在新的數據庫設計是,不要再使用LONG類型列.
LONG類型有兩種:
LONG :能存儲2GB的字符
LONG RAW:能存儲最多2GB的二進制數據.
我們只需要對LONG類型的限制進行了解即可.
LONG/LONG RAW 類型 CLOB/BLOB類型
一個表只能有一個LONG/LONG RAW列 一個表可以有最多1000個LOB類型列
不能用于用戶自定義類型 可以用于用戶自定義類型
WHERE中不能引用LONG類型 可以
除了NOT NULL,完整性約束中不能引用 可以
不支持分布式事務 支持
不能使用基本或高級復制技術 可以
不能在GROUP BY,ORDER BY,CONNECT BY,DISTINCT,UNIQUE,INTERSECT,MINUS,UNION中使用 可以通過函數來轉換成一個標量SQL類型來支持
PL/SQL函數和過程中不能作為參數 可以
不能應用于內置函數,如SUBSTR 可以
CREATE TABLE AS SELECT不能使用LONG類型 支持
在有LONG類型的表中不能進行移動表空間 可以
總之一句話,新系統不應該再使用LONG類型.
老系統如果有的表的某些字段是LONG類型,要注意它的限制
第六部分 ROWID
ROWID 就是數據庫中一行的地址，用于記錄數據存儲的一些屬性，包括：記錄存儲所在的數據文件(file#),所屬的數據庫對象(obj#),所在的數據塊號(block_no#),以及在表中的行號。這些屬性就構成了Oracle 的ROWID.
我們需要注意的是在數據表中并沒有一列來專門記錄ROWID。
另外還有一個UROWID，它用于表，是行主鍵的一個表示，基于主鍵生成.一般是索引組織表在使用。索引組織表是沒有ROWID的。
不管是ROWID還是UROWID，數據表都沒有專門的一列來記錄。
我們把這兩種類型稱為偽列。
SQL> create table test_rowid (id number(38));
Table created
SQL> insert into test_rowid values(1);
1 row inserted
SQL> select rowid, id from test_rowid;
ROWID ID
------------------ ---------------------------------------
AAAKsAAAEAAAAC+AAA 1
因為ROWID可以唯一的標識一條記錄，所以索引中存儲了ROWID值，通過索引訪問記錄，其實也就是通過從索引獲得ROWID，再根據ROWID定位數據表中的記錄。
但是當對表進行分區移動之后，索引就需要重建，因為存儲位置已經發生了變化，索引中的ROWID已經不能再定位到新的數據了。
ORACLE 的ROWID一直在不斷變化。
在ORACLE 6中，ROWID使用6bit來表示文件號。
在ORACLE 8，ROWID的組成是FFFF.BBBBBBBB.RRRR。占用6個字節。
10bit 的file#,22bit的block#,16bit的row #
在ORACLE 9中，Oracle 為ROWID引入了數據對象號的概念dataobj#.
現在ROWID格式變為OOOOOO.FFF.BBBBBB.RRR。最新的ROWID采用Base64編碼，一共有18位，代表80位二進制數，其中：O為數據對象號，F是文件號，B是塊號，R是行號
32 bit dataobj#+10bit rfile#,+22 bit block# +16bit row#
在以前ROWID是保持不變的,但現在ROWID是會發生改變的。如：
把一行從一個分區移到另一個分區
使用閃回表(flashback table)命令將一個數據表恢復到以前的某個時間點
對分區進行操作，如：移動，分解和合并
對段進行收縮
這些操作都會使ROWID發生變化，所以我們不應該把ROWID來作為唯一標識。而是使用一個單獨的列為主鍵用來作數據行的唯一標識。另外主鍵約束可以實現引用完整性。而ROWID是無法做到的。
筆者曾經使用ROWID排序來實現按數據的寫入順序來顯示數據。這在大多數情況下是可以做的，但是如果以后因為維護數據庫，對分區進行操作后，這樣做是不可行的。
所以應該使用單獨的列來記錄數據的寫入順序。
ROWID類型的主要用途是與數據庫進行交互時，可以快速的指向某一行。比如使用ROWID更新某一行等?？梢圆煌ㄟ^索引而快速的找到某行記錄。并且可以很快的進行行數據的驗證。
?
轉載于:https://www.cnblogs.com/askjacklin/archive/2012/06/04/2534580.html
總結
以上是生活随笔為你收集整理的Oracle 数据类型及存储方式(袁光东 原创)的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Spherical Harmonics
- 下一篇： java 数据库连接池