[轉]認識oracle資料類型

http://www.blogjava.net/tacy/archive/2007/09/07/143485.html

作者：tacy lee

在設計資料庫的時候，選擇正确的資料類型，往往可以避免很多的問題，正确了解資料庫的類型，對于存儲空間規劃，應用性能調整都會很有幫助，下面是我個人的一點總結：

1、 Char

定長格式字元串，在資料庫中存儲時不足位數填補空格，不建議使用，會帶來不必要的麻煩

a、  字元串比較的時候，如果不注意（char不足位補空格）會帶來錯誤

b、  字元串比較的時候，如果用trim函數，這樣該字段上的索引就失效（有時候會帶來嚴重性能問題）

c、  浪費存儲空間

2、 Varchar2/Varchar

不定長格式字元串，對于4000位元組以内的字元串，建議都用該類型

a、  網上有說char比varchar2性能好，但是如果你有興趣做測試的話，會發現沒有差別（如果發生行遷移，可以通過pctfree來調整）

b、  充分利用存儲空間

3、 Long/long raw

Oracle已經廢棄，隻是為了向下相容保留着，應該全部更新到lob

Long類型有很多限制

a、  表中隻能有一列long類型

b、  Long類型不支援分布式事務

c、  太多的查詢不能在long上使用了

4、 Number

定義Number的方法：Number(p,s)

其中p，s都是可選的：

a、  p代表精度，預設為38

b、  s代表小數位數，取值範圍-84~127，預設取值要看是否指定了p，如果制定了p，預設s為0，如果沒有指定p，預設取最大值。

幾個例子：

a、  Number(5,0)=Number(5) 取值範圍99999～-99999

b、  Number(5,2) 取值範圍999.99～-999.99

注意：其中的整數位數隻有3位，小數位數有2位，按照如下方法計算：

      整數位數<=p-s

      小數位數<=s

如果插入123.555存儲在資料庫中變成123.56 (在小數的第三位上四舍五入)，如果插入999.999，資料庫就要抛錯。

c、  Number(5,-2) 取值範圍9999900～-9999900 （整數位數<=p-s，沒有小數位數）

如果插入9999949存儲在資料庫中變成9999900（在整數的第二位上四舍五入），如果插入9999950，資料庫就要抛錯。

其他的數值類型都是number的衍生，底層都是number，比如integer/int完全映射到number(38)

性能相關：number是一種軟實作的類型，如果需要對number做複雜的運算，建議先用cast内置函數轉換number為浮點數類型

另外需要注意的一點是：number是變長類型，在計算表存儲空間的時候要切記

5、 Date

Date類型是一個7位元組的定長資料類型，沒啥好說的，一個例子：性能a>b>c

a、Where date_colum>=to_date(’01-jan-2007’,’dd-mon-yyyy’)

   and date_colum< DIV>

b、Where trunc(date_colum,’y’)=to_date(’01-jan-2007’,’dd-mon-yyyy’)

c、Where to_char(date_colum,’yyyy’)=’2007’

6、 Timestamp/timestamp with time zone/timestamp with local time zone

和date類似，隻不過它另外支援小數秒和時區。文法Timestamp(n)，n指定秒的小數位數，取值範圍0～9。可選。

7、 Lob

Clob/blob實作是比較複雜的，這裡隻提提幾個和性能相關的點，當然能不用lob盡量不用：

a、  一個lob字段包括lobindex和lobsegment

b、  Lob預設可以存放在表中（表字段），條件是：

     1.它的大小小于4kb

     2.并且在定義的時候沒有使用(disable storage inrow)字句（預設是enable）

     當lob大于4kb的時候它會被存放到lobsegment中

c、  當lob存放在表中的時候，它可以被緩存，對于它的操作效率遠遠高于存儲在lobsegment中的lob（不用lobindex）

d、  存儲在lobsegment中的lob預設不在緩沖區緩存，對于lob的讀寫都是實體IO，代價非常高，是以對于大于4kb的lob字段千萬不要頻繁更新，效率非常低

e、  存儲在lobsegment中的lob可以在定義的時候指定使用cache（預設是nocache），這對于中等大小的lob（比如幾k～幾十k）很有效果，減少實體IO