攻略1-5 恰当选择数据类型

问题描述

在创建数据表的时候,需要选用恰当的数据类型,以实现最优的性能、最佳的可扩展性以及最好的可维护性。

解决方案

在决定表中所使用数据类型时,有几个性能和可维护性方面的问题需要考虑。表1-4描述了与性能相关的属性。

enter image description here

注意 在Oracle数据库12c之前,VARCHAR2和NVARCHAR2的最大长度是4000,RAW列的最大长度是2000。从Oracle数据库12c开始,这些数据类型可以容纳的最大长度扩展为32 767。

工作原理

在创建一张表的时候,必须声明列名和相应的数据类型。作为开发者或数据库管理员,需要理解如何恰当地使用各种类型的数据。我们已经看到很多由错误的数据类型选用引起的应用问题(性能问题以及数据准确性问题)。例如,当应该使用日期类型的时候使用了字符串类型,在进行日期运算或生成报表的时候,就会产生不必要的数据类型转换。更为糟糕的是,当在生产环境中选择了错误的数据类型之后,要修改是很难的,因为这可能会使得已有的代码无法运行。一旦选择错了,就很难撤回再重新选择。很可能最终会想方设法强制错误选择的数据类型完成它不能完成的任务。

话虽如此,Oracle支持下面这些数据类型:

 字符型 
 数值 
 日期/时间 
 RAW 
 ROWID 
 LOB 
提示 LONG和LONG RAW数据类型已经被弃用。

下面对以上所列的数据类型分别作简要的介绍。

字符型

在Oracle中,有四种字符型数据可以使用:VARCHAR2、CHAR、NVARCHAR2和NCHAR。在大多数存储字符串的应用场景下,应该使用VARCHAR2数据类型。VARCHAR2数据类型会按照字符串中的字符数来分配空间。如果将包含一个字符的字符串插入到定义为VARCHAR2(30)的列中,Oracle将只会占用一个字符的空间。

当定义了一个VARCHAR2列之后,必须声明长度。有两种方法:BYTE和CHAR。BYTE声明了字符串以字节为单位的最大长度,而CHAR则声明了最大的字符个数。例如,要声明一个最多包含30字节的字符串,可以像下面这样来定义:

enter image description here

在几乎所有情况下,使用CHAR的方式是更安全的。在处理多字节字符集的时候,如果将长度声明为VARCHAR2(30字节),可能不会得到预想的结果,因为有一些字符需要超过1个字节来存储。相反,如果声明为VARCHAR2(30字符),就不存在上面这个问题。

如果有一个数据库初始创建的时候,具有单字节和固定长度的字符集合。但一段时间之后,需要在其中存储多字节字符集合数据时,就可以使用NVARCHAR2和NCHAR数据类型。

提示 Oracle确实也有另一种名为VARCHAR的数据类型。现在Oracle对VARCHAR的定义与VARCHAR2是同样的。Oracle强烈推荐使用VARCHAR2(而不是VARCHAR),因为Oracle文档中说VARCHAR可能在将来会有不同的用途。

数值

对于可能需要应用数学函数,例如SUM、AVG、MAX和MIN等的数据,应该使用数值类型。不要将数值信息存储在字符数据类型中。当将本质上是数值的数据存储为VARCHAR2类型时,就可能导致系统在将来出错。最终,需要在数值类型数据上生成报表或运行计算。如果它们不是数值类型的,就会得到不可预测的、通常也是错误的结果。

Oracle支持三种数值类型:

 NUMBER 
 BINARY_DOUBLE 
 BINARY_FLOAT 
对于大多数情况,对任何类型的数值数据,都应该使用NUMBER数据类型。其语法结构是:

enter image description here

其中scale是总的数据位数,precision(精度)指的是小数点后面保留几位。因此,如果一个数值定义为NUMBER(5, 2),就可以存储+/–999.99的值。也就是总共有5个数字,其中小数点后面是两位。

提示 Oracle允许的最多数字个数为38。这几乎可以满足任何应用对数值的需求。

经常会让开发者和数据库管理员感到困惑的是,在创建表时可以将一些列定义为INT、INTEGER、REAL、DECIMAL等。在Oracle中这些数据类型都被实现为NUMBER数据类型。例如,声明为INTEGER的列被实现为NUMBER(38)。

BINARY_DOUBLE和BINARY_FLOAT数据类型通常用来进行科学计算,与Java中的DOUBLE和FLOAT数据类型是相对应的。除非是在进行火箭科学计算的应用中,否则对数值应用需求都应该使用NUMBER数据类型。

注意 BINARY数据类型可能会引起在NUMBER数据类型中不会发生的取整错误,并且取决于操作系统和硬件,相应的行为也可能不同。

日期/时间

当获取并报告与日期相关的信息时,就需要使用DATE或TIMESTAMP数据类型(而不是VARCHAR2或NUMBER)。正确使用与日期相关的数据类型,使得Oracle可以进行精确的日期计算以及相应的聚集运算,并进行排序,生成报告。如果为包含日期信息的字段使用了VARCHAR2数据类型,就可能会引起将来报表的不一致,并导致不必要的数据类型转换(例如TO_DATE和TO_CHAR)。

DATE类型的数据中包含日期部分和精确到秒的时间部分。如果在插入数据的时候没有声明时间部分,则默认的时间是0点(00:00:00)。如果需要比秒更精确的时间精度,可以使用TIMESTAMP。否则都可以使用DATE。

TIMESTAMP数据类型包含日期部分和精确到几分之一秒的时间部分。在定义TIMESTAMP的时候,可以声明秒的精度。例如,如果想要在小数点后保留五位小数,可以声明为:

最大可以声明为9,默认值为6。如果将分数精度声明为0,则会得到与DATE数据类型同样的效果。

RAW

RAW数据类型可以在列中存储二进制数据。这种类型的数据有时被用来存储全局唯一标识符或少量的加密数据。如果需要存储大量(超过2000B)的二进制数据,则可以使用BLOB。

如果从RAW列中选择数据,SQL*Plus会在数据获取过程中隐式应用内置的RAWTOHEX函数。数据显示为十六进制,使用字符0-9和A-F。当往RAW列中插入数据的时候,会隐式应用内置的HEXTORAW函数。

这一点很重要,因为如果在RAW列上创建索引,优化器可能会忽略该索引,因为在SQL语句中所使用的RAW列上,SQL*Plus隐式应用了函数。常规的索引或许没有什么用,但使用RAWTOHEX的基于函数的索引,可能会带来性能上的提高。

ROWID

当开发者或数据库管理员听到ROWID的时候,通常会想到表示表中每一行数据在磁盘上物理位置的伪列,这是对的。但是,很多人没有认识到Oracle支持一种实际的ROWID数据类型,也就是说在创建表的时候,可以将其中的列定义为ROWID类型。

ROWID数据类型有几种实际的应用。其中一种正确的应用方式就是,如果启用参照完整性约束出现问题,并想要获取导致约束冲突的数据行ROWID时。在这种场景下,就可以在表中创建一列类型为ROWID的数据,并将表中导致冲突的记录的ROWID存储在其中。这为获取引起冲突的数据并解决问题提供了一种有效的方法。

不要尝试在表的主键值上使用ROWID数据类型。这是因为表中数据行的ROWID会发生变化。例如,ALTER TABLE...MOVE命令就可能会改变表中每一行的ROWID。通常,表中数据行的主键值不应该变化。由于这个原因,不要在主键上使用ROWID,但可以使用顺序生成的没有含义的数值(或者在12c中,使用自增列来填充主键列)。

LOB

Oracle支持通过LOB数据类型来将大量数据存入一列中。Oracle支持以下类型的LOB:

 CLOB 
 NCLOB 
 BLOB 
 BFILE 
如果有VARCHAR2类型中存储不了的文本数据,则需要使用CLOB来存储。CLOB可以用来存储大量的字符数据,例如文章中的文字(博客记录)以及日志文件。NCLOB与CLOB很相似,但允许存储使用数据库的国家字符集编码的信息。

BLOB存储人们通常无法读懂的大量二进制数据。典型的BLOB数据包括图片、音频、字符处理文件、PDF、工作表以及视频文件。

CLOB、NCLOB和BLOB统称为内部LOB。这是因为它们都存储在Oracle数据库里。这些类型的数据都位于与数据库相关的数据文件中。

BFILE被称为外部LOB。BFILE列存储一个指向数据库之外的,操作系统中某个文件的指针。当在数据库中存储一个很大的二进制文件不可行时,可以使用BFILE。BFILE不参与数据库事务,并且不在Oracle安全保护或备份恢复的范围内。如果需要具有这些特性,就需要使用BLOB而不是BFILE。

目录