现在的位置: 首页 > 数据库 > 正文

Oracle数据库数据类型详解

2014年11月05日 数据库 ⁄ 共 7232字 暂无评论 ⁄ 阅读 1,453 次
文章目录

注意区分Oracle数据库支持的数据类型跟PL/SQL支持的数据。

字符串

Oracle中有4种基本的字符串类型,分别是CHAR、VARCHAR2、NCHAR和NVARCHAR2。在Oracle中,所有串都以同样的格式存储。在数据库块上,最全面都有一个1~3字节的长度字段,其后才是数据,如果数据为NULL,长度字段则表示一个单字节值0xFF。

如果串的长度小于或等于250(0x01~0xFA),Oracle会使用1个字节来表示长度。对于所有长度超过250的串,都会在一个标志字节0xFE后跟有两个字节来表示长度。因此,如果有一个包含“Hello World”的VARCHAR2(80),则在块中可能如下图所示:

oracle-datatype1

另一方面,如果在一个CHAR(80)中存储同样的数据,则可能如下图所示:

oracle-datatype2

二进制串:RAW类型

RAW类型是一个变长的二进制串。CHAR和VARCHAR2类型需要进行字符集转换,而二进制数据不会做这种字符集转换。因此,二进制数据类型不适合存储用户提供的文本,而适于存储加密信息,加密数据不是“文本“,而是原文本的一个二进制表示、包含二进制标记信息的字处理文档,等等。

Oracle支持3种数据类型来存储二进制数据:

  • RAW类型,这它很适合存储多达2,000字节的RAW数据。
  • BLOB类型,它支持更大的二进制数据。
  • LONG RAW类型,这是为支持向后兼容性提供的,新应用不应考虑使用这个类型。

RAW数据看上去就像是一个字符串,但是在磁盘上并不存储为字符串。

数值类型

Oracle 10g及以后的版本支持3种数值数据类型:

  • NUMBE:Oracle NUMBER类型能以极大的精度存储数值,具体来讲,精度可达38位。其底层数据格式类似一种“封包小数“表示。Oracle NUMBER类型是一种变长格式,长度为0~22字节。它可以存储小到10e-130、大到(但不包括)10e126的任何数值。这是目前最为常用的数值类型。
  • BINARY_FLOAT:这是一种IEEE固有的单精度浮点数。它在磁盘上会占用5字节的存储空间:其中4个固定字节用于存储浮点数,另外还有一个长度字节。BINARY_FLOAT能存储有6为精度、范围在~±53的数值。
  • BINARY_DOUBLE:这是一种IEEE固有的双精度浮点数。它在磁盘上会占用9字节的存储空间:其中8个固定字节用于存储浮点数,还有一个长度字节。BINARY_DOUBLE能存储有12位精度、范围在~±25的数值。

Oracle NUMBER类型比BINARY_FLOAT和BINARY_DOUBLE类型的精度大得多,但是取值范围却远远小于BINARY_DOUBLE。也就是说,用NUMBER类型可以很精确地存储数值(有很多有效数字),但是用BINARY_FLOAT和BINARY_DOUBLE类型可以存储更小或更大的数值。

NUMBER存储时会根据精度和小数位对存储的值进行四舍五入,以保证存入的数据符合NUMBER所定义的精度和小数位。

浮点数用于近似数值;它们没有前面所述的内置Oracle NUMBER类型那么精确。浮点数常用在科学计算中,由于允许在硬件(CPU、芯片)上执行运算,而不是在Oracle子例程中运算,所以在多种不同类型的应用中都很有用。因此,如果在一个科学计算应用中执行实数处理,算术运算的速度会快得多。

非固有数据类型

除了NUMBER、BINARY_FLOAT和BINARY_DOUBLE类型,Oracle在“语法上还支持”以下数值数据类型:

  • NUMERIC(p,s):完全映射至NUMBER(p,s)。如果p未指定,则默认为
  • DECIMAL(p,s)或DEC(p,s):完全映射至NUMBER(p,s)。如果p为指定,则默认为
  • INTEGER或INT:完全映射至NUMBER(38)类型。
  • SMALLINT:完全映射至NUMBER(38)类型。
  • FLOAT(b):映射至NUMBER类型。
  • DOUBLE PRECISION:映射至NUMBER类型。
  • REAL:映射至NUMBER类型。

“在语法上支持”是指CREATE语句可以使用这些数据类型,但是在底层实际上它们都只是NUMBER类型。

Oracle NUMBER类型是一种软件数据类型,在Oracle软件本身中实现。我们不能使用固有硬件操作将两个NUMBER类型相加,这要在软件中模拟。不过,浮点数没有这种实现。将两个浮点数相加时,Oracle会使用硬件来执行运算。所以,浮点数类型性能要优于NUMBER。

浮点数是数值的一个近似值,精度在6~12.位之间。从NUMBER类型得到的答案比从浮点数得到的答案“精确“得多。但是如果你在对科学数据执行数据挖掘或进行复杂的数值分析,这种精度损失往往是可以接受的,另外可能会得到非常显著的性能提升。

通过使用内置的CAST函数,可以对Oracle NUMBER类型执行一种实时的转换,在对其执行复杂数学运算之前先将其转换为一种浮点数类型。这样一来,所用CPU时间就与使用固有浮点类型所用的CPU时间非常接近,如:“select sum(ln(cast( column_name as binary_double ) )) from table_name;”。

LONG类型

Oracle中的LONG类型有两种:

  • LONG文本类型,能存储2GB的文本。与VARCHAR2或CHAR类型一样,存储在LONG类型中的文本要进行字符集转换。
  • LONG RAW类型,能存储2GB的原始二进制数据(不用进行字符集转换的数据)。

Oracle 8时,这种类型被LOB类型所取代。LONG/LONG RAW类型和CLOB/BLOB类型区别如下图所示:

oracle-datatype3

时间类型

Oracle固有数据类型DATE、TIMESTAMP和INTERVAL是紧密相关的。DATE和TIMESTAMP类型存储精度可变的固定日期/时间。INTERVAL类型可以很容易地存储一个时间量,如“8个小时”或“30天”。将两个日期相减,就会得到一个时间间隔(INTERVAL)。

DATE数据类型是存储日期/时间信息最为常用的类型。但是许多新应用都在使用TIMESTAMP类型,这有两个原因:一方面它支持小数秒,而DATE类型不支持;另一方面TIMESTAMP类型支持时区,这也是DATE类型力所不能及的。

DATE

DATE类型是一个7字节的定宽日期/时间数据类型。它总是包含7个属性,包括:世纪、世纪中哪一年、月份、月中的哪一天、小时、分钟和秒。

向DATE增加或减去时间

常用的技术有3种:

  • 向DATE增加一个NUMBER。把DATE加1是增加1天的一种方法。因此,向DATE增加24就是增加1个小时,依此类推。
  • 使用INTERVAL类型来增加时间单位。INTERVAL类型支持两种粒度:年和月,或日/小时/分钟/秒。也就是说,可以是几年和几个月的一个时间间隔,也可以是几天、几小时、几分钟和几秒的一个时间间隔。
  • 使用内置的ADD_MONTHS函数增加月。由于增加一个月往往不像增加28~31天那么简单,为了便于增加月,专门实现了这样一个函数。

一般建议:

  • 使用NUMTODSINTERVAL内置函数来增加小时、分钟和秒。
  • 加一个简单的数来增加天。
  • 使用ADD_MONTHS内置函数来增加月和年。

建议不要使用NUMTOYMINTERVAL函数,因为在处理月末问题时,ADD_MONTHS在增加一个月后会得到下个月的最后一天,而NUMTOYMINTERVAL得到的日期是下个月的同一天,这很有可能会造成错误。

得到两个日期之差

两个日期相减会返回表示两个日期相隔天数的一个数。另外,还可以使用内置函数MONTHS_BETWEEN,它会返回表示两个日期相隔月数的一个数(包括月小数)。最后,利用INTERVAL类型,你还能用另一个方法来查看两个日期之间的逝去时间。

若需要显示日期之间相隔的年数、月数、天数、小时数、分钟数和秒数。通过使用前述函数的一个组合,就可以实现这个目标。我们将选出两个间隔:一个是年和月间隔,另一个是日/小时/分钟/秒间隔。

我们使用MONTHS_BETWEEN内置函数来确定两个日期之间相隔的月数(包括小数),然后使用NUMTOYMINTERVAL内置函数将这个数转换为年数和月数。另外,使用TRUNC得到两个日期相隔月数中的整数部分,再使用ADD_MONTHS内置函数将dt1增加12.个月(这会得到‘28-feb-2001 01:02:03),再从两个日期中的较大者(dt2)减去这个计算得到的日期,从而得到两个日期之间的天数和小时数:

select numtoyminterval(months_between(dt2,dt1),'month') years_months,

numtodsinterval(dt2-add_months(dt1,trunc(months_between(dt2,dt1))),'day') days_hours

from (select to_date('29-feb-2000 01:02:03','dd-mon-yyyy hh24:mi:ss') dt1,

to_date('12.-mar-2001 12.:22:33','dd-mon-yyyy hh24:mi:ss') dt2

from dual);

TIMESTAMP类型

TIMESTAMP类型与DATE非常类似,只不过另外还支持小数秒和时区。

声明语法:TIMESTAMP(n)

这里n是可选的,用于指定TIMESTAMP中秒分量的小数位数,可以取值为0~9。如果指定0,TIMESTAMP在功能上则与DATE等价,它们实际上会以同样的方式存储相同的值。

向TIMESTAMP增加或减去时间

如果直接使用数字加减,则小数秒没有了,另外时区信息也没有了。所以,对此使用INTERVAL更有意义。

得到两个TIMESTAMP之差

这正是DATE和TIMESTAMP类型存在显著差异的地方。尽管将DATE相减的结果是一个NUMBER,但TIMESTAMP相减的结果却是一个INTERVAL。

TIMESTAMP WITH TIME ZONE类型

TIMESTAMP WITH TIME ZONE类型继承了TIMESTAMP类型的所有特点,并增加了时区支持。TIMESTAMP WITH TIME ZONE类型占13字节的存储空间,在此有额外的2个字节用于保留时区信息。它在结构上与TIMESTAMP的差别只是增加了这2个字节。

INTERVAL类型

INTERVAL类型是表示一段时间或一个时间间隔的一种方法,有两个INTERVAL类型:其中一个是YEAR TO MONTH类型,它能存储按年和月指定的一个时段;另一个类型是DATE TO SECOND类型,它能存储按天、小时、分钟和秒(包括小数秒)指定的时段。

INTERVAL YEAR TO MONTH

要创建一个5年2个月的时间间隔,可以使用以下命令:

select numtoyminterval(5,'year')+numtoyminterval(2,'month') from dual;

或者,利用1年有12个月这个事实,可以使用一个调用,并使用以下命令:

select numtoyminterval(5*12.+2,'month') from dual;

还可以用另一个函数TO_YMINTERVAL将一个串转换为一个年/月INTERVAL类型:

select to_yminterval( '5-2' ) from dual;

LOB类型

Oracle中支持4种类型的LOB:

  • CLOB:字符LOB。这种类型用于存储大量的文本信息,如XML或者只是纯文本。这个数据类型需要进行字符集转换,也就是说,在获取时,这个字段中的字符会从数据库的字符集转换为客户的字符集,而在修改时会从客户的字符集转换为数据库的字符集。
  • NCLOB:这是另一种类型的字符LOB。存储在这一列中的数据所采用的字符集是数据库的国家字符集,而不是数据库的默认字符集。
  • BLOB:二进制LOB。这种类型用于存储大量的二进制信息,如字处理文档,图像和你能想像到的任何其他数据。它不会执行字符集转换。应用向BLOB中写入什么位和字节,BLOB就会返回什么为和字节。
  • BFILE:二进制文件LOB。这与其说是一个数据库存储实体,不如说是一个指针。带BFILE列的数据库中存储的只是操作系统中某个文件的一个指针。这个文件在数据库之外维护,根本不是数据库的一部分。BFILE提供了文件内容的只读访问。

创建一个LOB列时,一般来说,存储在行中的这是一个指针(pointer),或LOB定位器(LOB locator)。我们的应用所获取的就是这个LOB定位器。表中的LOB实际上只是指向lobindex,lobindex再指向LOB本身的各个部分。

保存LOB数据的表空间可能不同于保存实际表数据的表空间,主要原因与管理和性能有关。

从管理的角度看,LOB数据类型表示一种规模很大的信息。如果表有数百万行,而每行有一个很大的LOB,那么LOB就会极为庞大。为LOB数据单独使用一个表空间有利于备份和恢复以及空间管理,单从这一点考虑,将表与LOB数据分离就很有意义。

另一个原因则出于I/O性能的考虑。默认情况下,LOB不在缓冲区缓存中进行缓存(有关内容将在后面再做说明)。因此,默认情况下,对于每个LOB访问,不论是读还是写,都会带来一个物理I/O(从磁盘直接读,或者向磁盘直接写)。

lobindex和lobsegment总是会在同一个表空间中。不能将lobindex和lobsegment放在不同的表空间中。

IN ROW子句

LOB数据有时可以与表一同存储,而不用单独放在lobsegment中。如果设置了ENABLE STORAGE IN ROW,而不是DISABLE STORAGE IN ROW,小LOB(最多4,000字节)就会像VARCHAR2一样存储在表本身中。只有当LOB超过了4,000字节时,才会“移出”到lobsegment中。默认行为是启用行内存储(ENABLE STORAGE IN ROW),而且一般来讲,如果你知道LOB总是能在表本身中放下,就应该采用这种默认行为。

如果使用一个CLOB,而且很多串都能在“行内”放下(也就是说,小于4,000字节),那么使用默认的ENABLE STORAGE IN ROW设置比OUT_ROW使用更少的I/O,而且可以进行缓存以避免直接的物理I/O。

CHUNK子句

LOB存储在块(chunk)中,指向LOB数据的索引会指向各个数据块。块(chunk)是逻辑上连续的一组数据库块(block),这也是LOB的最小分配单元,而通常数据库的最小分配单元是数据库块。CHUNK大小必须是Oracle块大小的整数倍,只有这样才是合法值。

选择CHUNK大小时不能过大,也不能过小。

每个行外存储的LOB值会占用至少一个CHUNK,一个CHUNK只能有一个LOB使用(两个LOB不会使用同一个CHUNK)。如果选择了一个CHUNK大小,但不符合你期望的LOB大小,最后就会浪费大量的空间。例如,如果表中的LOB平均有7KB,而你使用的CHUNK大小为32KB,对于每个LOB实例你都会“浪费”大约25KB的空间,另一方面,倘若使用8KB的CHUNK,就能使浪费减至最少。

lobindex用于指向各个块,块越多,索引就越大。如果有一个4MB的LOB,并使用8KB的CHUNK,你就至少需要512个CHUNK来存储这个消息。这也说明,至少需要512个lobindex条目指向这些CHUNK。这会影响获取性能,因为与读取更少但更大的CHUNK相比,现在要花更长的数据来读取和管理许多小CHUNK。

CACHE子句

这个子句选可以是NOCACHE、CACHE或CACHE READS,它控制了lobsegment数据是否存储在缓冲区缓存中。默认的NOCACHE指示,每个访问都是从磁盘的一个直接读,类似地,每个写/修改都是对大盘的一个直接写。CACHE READS允许缓存从磁盘读的LOB数据,但是LOB数据的写操作必须直接写至磁盘。CACHE则允许读和写时都能缓存LOB数据。

在许多情况下,默认设置可能对我们并不合适。如果你只有小规模或中等规模的LOB(例如,使用LOB来存储只有几KB的描述性字段),对其缓存就很有意义。如果不缓存,当用户更新描述字段时,还必须等待I/O将数据写指磁盘(将执行一个CHUNK大小的I/O,而且用户要等待这个I/O完成)。如果你在执行多个LOB的加载,那么加载每一行时都必须等待这个I/O完成。所以启用执行LOB缓存很合理。

对于一个规模很多的初始加载,启用LOB的缓存很有意义,这允许DBWR在后台将LOB数据写至磁盘,而你的客户应用可以继续加载更多的数据。对于频繁访问或修改的小到中等规模的LOB,缓存就很合理,可以部门让最终用户实时等待物理I/O完成。不过,对于一个大小为50MB的LOB,把它放在缓存中就没带道理了。

ROWID/UROWID类型

ROWID是数据库中一行的地址。ROWID中编入了足够多的信息,足以在磁盘上找到行,以及标识ROWID所指向的对象(表等)。ROWID有一个“近亲”UROWID,它用于表,如IOT和通过异构数据库网关访问的没有固定ROWID表。UROWID是行主键值的一个表示,因此,其大小不定,这取决于它指向的对象。

每个表中的每一行都有一个与之关联的ROWID或UROWID。从表中获取时,把它们看作为伪列(pseudo column),这说明它们并不真正存储在行中,而是行的一个推导属性。ROWID基于行的物理位置生成;它并不随行存储。

除了IOT中的行之外,所有行都有ROWID。

声明:该笔记转载自《Oracle Database编程艺术:深入数据库体系结构》,仅用于个人学习,若侵犯到您的版权敬请告知!

» 声明:本站文章源于个人经验总结或书籍、互联网转载,内容仅用于个人学习,请勿转载,否则后果自负!
【上篇】
【下篇】

给我留言

留言无头像?