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

Oracle数据库数据表物理存储结构详解

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

表类型

  • 堆组织表(heap organized table),这是常见的普通数据库表,数据以堆(随机存储)的方式管理。增加数据时会使用段中找到的第一个能放下此数据的自由空间,从表中删除数据时则允许INSERT和UPDATE重用这部分空间。
  • 索引组织表(index organized table):这些表按索引结构存储,它强制要求行本身有某种物理顺序。在堆中,只要放得下,数据可以放在任何位置;而在索引组织表(IOT)中,数据要根据主键有序地存储
  • 索引聚簇表(index clustered table):聚簇(cluster)是指一个或多个表组成的组,这些表物理地存储在相同的数据库块上,有相同聚簇键值的所有行会相邻地物理存储。这种结构可以实现两个目标。首先,多个表可以物理地存储在一起,一般情况下一个表的数据就在一个数据库块上,但是对于聚簇表,可能把多个表的数据存储在同一个块上。其次,包含相同聚簇键值(如DEPTNO=10)的所有数据会物理地存储在一起。这些数据按聚簇键值“聚簇”在一起,聚簇键使用B*树索引建立。
  • 散列聚簇表(hash clustered table):这些表类似于聚簇表,但是不使用B*树索引聚簇键来定位数据。散列聚簇将键散列到聚簇上,从而找到数据应该在哪个数据库块上。在散列聚簇中,数据就是索引(这是隐喻的说法)。如果需要频繁地通过键的相等性比较来读取数据,散列聚簇表就很适用。
  • 有序散列聚簇表(sorted hash clustered table):这种表类型是Oracle 10g中新增的,它结合了散列聚簇表的某些方面,同时兼有IOT的一些方面。其概念如下:你的行按某个键值(如CUSTOMER_ID)散列,而与该键相关的一系列记录以某种有序顺序到达(因此这些记录是基于时间戳的记录),并按这种有序顺序处理。例如,客户在你的订单输入系统中下订单,这些订单会按先进先出(first in, first out, FIFO)的方式获取和处理。在这样一个系统中,有序散列聚簇就是适用的数据结构。
  • 临时表(temporary table):这些表存储的是事务期间或会话期间的“草稿”数据。临时表要根据需要从当前用户的临时表空间分配临时区段,每个会话只能看到该会话分配的区段,不会看到其他任何会话中创建的任何数据。
  • 嵌套表(nested table):嵌套表是Oracle对象关系扩展的一部分。它们实际上就是系统生成和维护的父/子关系中的子表。嵌套表的工作类似于SCOTT模式中的EMP和DEPT。可以认为EMP是DEPT表的子表,因为EMP表有一个指向DEPT的外键DEPTNO。嵌套表与子表的主要区别是:嵌套表不像子表(如EMP)那样是“独立”表。
  • 对象表(object table):对象表基于某种对象类型创建,它们拥有非对象表所没有的特殊属性,如系统会为对象表的每一行生成REF(对象标识符)。对象表实际上是堆组织表、索引组织表和临时表的特例,还可以包含嵌套表作为其结构的一部分。
  • 外部表(external table):这些表并不存储在数据库本身中,而是放在数据库之外,即放在平常的操作系统文件中。在Oracle9i及以上版本中,利用外部表可以查询数据库之外的一个文件,就好像这个文件也是数据库中平常的表一样。外部表对于向数据库加载数据最有用(外部表是非常强大的数据加载工具)。Oracle 10g则更进一步,还引入了一个外部表卸载功能,在不使用数据库链接的情况下,这为在Oracle数据库之间移动数据提供了一种简单的方法。

表基本特征

  • 表最多可以有1000,Oracle在内部会把列数大于254的行存储在多个单独的行段(row piece)中,这些行段相互指向,而且必须重新组装为完整的行影像。所以,当列过多时会造成效率的下降。
  • 表的行数几乎是无限的,不过可能会遇到另外某个限制,使得这种“无限”并不实际。例如,一个表空间最多有1022个文件(除Oracle 10g中超出上述文件大小限制的BIGFILE表空间),假设每个文件大小指定为32G,则会在不使用表分区时会遇到存储上限,即使进行表分区也被限制在以分区数为倍数的存储以下,所以表的行数并不是真的无限的。
  • 一般来讲表中的列有多少种排列组合,表就可以有多少个索引。但随着基于函数的索引的出现,理论上讲说能创建的索引数是无限的!不过,同样由于存在一些实际的限制,这会影响真正能创建和维护的索引数。
  • 一个数据库中可以有无限多个表。不过,还是同样的道理,实际的限制会使数据库中的表数在一个合理的范围内。

常见段类型

  • 表段:表段保存一个数据库表的数据,是最常用的段类型,通常与索引段联合使用。
  • 索引段:索引段保存索引结构。
  • 表分区(table partition)或子分区(subpartition):在表分区时,每个分区都是至少一个段,它与表段很相似。组合分区表则由一个或多个表子分区段(table subpartition segment)组成。
  • 索引分区(index partition):类似与表分区,分区索引由一个或多个索引分区段(index partition segment)组成。
  • lob索引(lobindex)、lob(lobsegment)及Lob分区(lob partition)、lob子分区(lob subpartition):lobindex和lobsegment段保存大对象(large object或LOB)的结构。对包含LOB的表分区时,lobsegment也会分区,lob分区段(lob partition segment)正是用于此。Oracle将分区lobindex标记为一个索引分区。
  • 聚簇(cluster):这种段类型能存储表。有两种类型的聚簇:B*树聚簇和散列聚簇。聚簇通常用于存储多个表上的相关数据,将其“预联结”存储到同一个数据库块上;还可以用于存储一个表的相关信息。“聚簇”这个词是指这个段能把相关的信息物理的聚在一起。
  • 嵌套表(nested table):这是为嵌套表指定的段类型,它是主/明细关系中一种特殊类型的“子”表,这种关系随后将详细讨论。
  • 回滚段(rollback)和Type2 undo段:undo数据就存储在这里。回滚段是DBA手动创建的段。Type2 undo段由Oracle自动创建和管理。

【查看表段类型】

使用数据字典user_segments,使用语法:

select segment_name,segment_type from user_segments;

注意:一般情况下唯一约束或主键约束会创建新索引,但如果在这些约束之前有列已经建立索引,则不会再给这些键自动建索引。

段空间管理

从Oracle 9i开始,管理段空间有两种方法:

  • 手动段空间管理(Manual Segment Space Management):可手工设置FREELISTS、FREELIST GROUPS、PCTUSED和其他参数,来控制如何分配、使用和重用段中的空间。以下,我们自定义MSSM即为手段段空间管理,以方便讲解。
  • 自动段空间管理(Automatic Segment Space Management):只需控制与空间使用相关的一个参数:PCTFREE。以下,自定义为ASSM。

段要使用ASSM,就必须位于支持ASSM空间管理的表空间中。创建不同方式的空间只需要在创建表空间时指定SEGMENT SPACE MANAGEMENT auto|manual即可,具体创建表空间语法可见“Oracle表空间基础”。

段参数

高水位线(high-water mark,HWM)(段)

它是表段中使用的术语,如果把表想象成一个“平面”结构,或者想象成从左到右依次排开的一系列块,高水平线就是包含了数据的最右边的块。如下图:

oracle-table1

HWM首先位于新创建表的第一个块中,随着表中放入数据使用了越来越多的块,HWM会升高。我们删除数据时可能许多块不再包含数据,但HWM并不会降,直到重建、截除或收缩这个对象(只有当段在一个ASSM表空间中时才支持这个特性)。

HWM很重要,因为Oracle在全面扫描段时会扫描HWM之下的所有块,即使其中不包含任何数据。当HWM之下的大多数块都为空时,会影响全面扫描的性能。

在一个MSSM表空间中,段只有一个HWM。不过,在ASSM表空间中,除了一个HWM外,还有一个低HWM。在MSSM中,HWM推进时(例如,插入行时),所有块都会格式化并立即有效。对于ASSM,HWM推进时,Oracle并不会立即格式化所有块,只有在第一次使用这些块时才会完成格式化。在全表扫描时,Oracle会扫描至HWM,对于低HWM以下的所有块会直接读取并加以处理。而对介于低HWM和HWM之间的块,则需要参考管理这些块所用的ASSM位图信息来查看应该读取哪些块,而哪些块应该被忽略。

FREELIST(段)

ASSM使用低HWM管理高HWM之下的空闲空间,而MSSM表空间会在自由列表(freelist)中为有自由空间的对象维护HWM以下的块。注意,freelists和freelist group在ASSM表空间中根本就没有,仅MSSM表空间使用这个技术。

每个表都至少有一个相关的freelist,使用块时,可能会根据需要把块放在freelist上或者从freelist删除。需要说明的重要一点是,只有位于HWM以下的块才会出现在freelist中。仅当freelist为空时才会使用HWM之上的块,此时Oracle会推进HWM,并把这些块增加到freelist中。采用这种方式,Oracle会延迟到不得已时才增加对象的HWM。

一个表可以有多个freelist,如果预计到会有多个并发用户在一个对象上执行大量的INSERT或UPDATE活动,就可以配置多个freelist,这对性能提升很有好处(但是可能要以额外的存储空间为代价),根据需要配置足够多的freelist非常重要。

在只有一个freelist的表上,并发多会话插入会发现长时间的等待,特别是对数据块的等待(试图插入数据)。这是因为表(以及索引)上的freelist不足造成的,表中没有配置足够的freelist来应付发生的这种并发活动。只需将表创建为有多个freelist,就能轻松地消除大部分等待时间。

多freelist表可以在建表时设定,也可以通过alter修改,如下:

CREATE TABLE table_name(…) STORAGE(FREELISTS n) TABLESPACE tablespace_name;

其中tablespace_name是一个MSSM表空间名。

ALTER TABLE table_name STORAGE (FREELISTS n);

使用多个freelist时,有一个主freelist,还有一些进程freelist。如果一个段只有一个freelist,那么主freelist和进程freelist就是这同一个自由列表。如果你有两个freelist,实际上将有一个主freelist和两个进程freelist。每个进程freelist都只有很少的块,余下的自由块都在主freelist上。对于一个给定的会话,会为之指定一个进程freelist。

使用一个进程freelist时,它会根据需要从主freelist拉出一些块。如果主freelist无法满足空间需求,Oracle就会推进HWM,并向主freelist中增加空块,然后,主freelist会把其存储空间分配多个进程freelist。

每个进程只会使用一个进程freelist,即使它用尽了自己及主freelist的空间而其他进程freelist仍有空间,它也不会到其他进程freelist上寻找空间。

如果主freelist上的空间无法满足这样一个自由块请求,就会导致表推进HWM。或者如果表的HWM无法推进(所有空间都已用),就要扩展表的空间(得到另一个区段)。然后这个进程仍然只使用其freelist上的空间(现在不再为空)。

使用多个freelist时要有所权衡。一方面,使用多个freelist可以大幅度提升性能。另一方面,有可能导致表不太必要地使用稍多的磁盘空间。你必须想清楚在你的环境中哪种做法麻烦比较小。

要解决前面提到的缓冲区忙等待问题,还有一种方法,这就是使用一个ASSM管理的表空间,它不必手动地设置许多合适的关键存储参数。至于方法就是将表建在ASSM表空间中即可。

PCTFREE和PCTUSED(块)

PCTFREE参数用来告诉Oracle应该在块上保留多少空间来完成将来的更新,默认值是10%。如果自由空间的百分比高于PCTFREE中的指定值,这个块就被认为是“自由的”。

PCTUSED则告诉Oracle当前不“自由”的一个块上自由空间百分比需要达到多大才能使它再次变为自由的,默认值是40%。

使用MSSM时,这些参数设置控制着块何时放入freelist中,以及何时从freelist中取出。如果使用默认值:PCTFREE为10,PCTUSED为40,那么在块到达90%满之前(有10%以上的自由空间),这个块会一直在freelist上。一旦到达90%,就会从freelist中取出。而且直到块上的自由空间超过了块的60%时,才会重新回到freelist上,在此之前,这个块一直不在freelist上。

使用ASSM时,PCTFREE仍然会限制能否将一个新行插入到一个块中,但是它不会控制一个块是否在freelist上,因为ASSM根本不使用freelist。在ASSM中,PCTUSED将被忽略。

行迁移

行迁移(row migration)是指在更新行时由于某一行变大,块中已经放不下这一行,这就要求这一行离开原来的块。Oracle会移动或迁移这一行,不过,Oracle不能简单地移动这一行,它必须留下一个“转发地址”(指针),指示这一行实际上在什么位置。如下图,对某行数据的迁移示意图:

oracle-table2

行迁移会影响性能。当通过一个索引来读这一行,索引会指向原来的块,那个块再指向这个新块,要得到具体的行数据需要多执行一个I/O。

这会造成访问这些数据的速度开始变慢(额外的I/O以及与I/O相关的闩定都会增加访问时间)。缓冲区缓存的效率开始下降(需要缓存两个块,而如果行没有迁移就只需要缓存一个块),另外表的大小好复杂性都有所增加。

当未来的某一时间再次更新这个行,新迁移的块若又出现空间不足,则会把这一行迁移回原来的块,如果有足够的空间,仍放回原地(这么一来,这一行可能变得“未迁移”)。如果没有足够的空间,Oracle会把这一行迁移到另外的某个块上,并修改原来块上的转发地址。

在设置PCTFREE和PCTUSED时,一方面,你要使用这些参数来避免迁移过多的行。另一方面,要使用这些参数避免浪费太多的空间。一般来讲,“高PCTFREE,低PCTUSED”适合未来会更新大量数据且会增加行大小的表;“低PCTFREE,高PCTUSED”适合只完成INSERT或DELETE,不会有大量UPDATE造成行增大的表。

堆组织表

执行CREATE TABLE语句时,默认得到的表类型就是堆组织表。如果你想要任何其他类型的表结构,就需要在CREATE语句本身中指定它。

全部扫描时,会按命中的顺序来获取数据,而不是以插入的顺序。数据库表本质上是无序的数据集合。

如果你的查询需要按插入的顺序来获取数据,就必须向表中增加一个辅助列,以便获取数据时使用这个列对数据排序。例如,这可以是一个数字列,有一个递增的序列(使用Oracle SEQUENCE对象)。只需使用一个SELECT,其ORDER BY子句对这个列完成排序,这样就可以模拟插入顺序。但是,需要注意的是order by使用ascii码排序,所以纯数字55会排在6之前,这在使用时需要注意。

对于堆表来说,ASSM有3个重要选项,MSSM,重要选项有5个。

PCTFREE:ASSM和MSSM都适用。在INSERT过程中,会测量块的充满程度。根据块当前充满的程度,这个参数用于控制能否将一行增加到一个块上。这个选项还可以控制因后续更新所导致的行迁移,要根据将如何使用表来适当地设置。

INITRANS:ASSM和MSSM都适用。为块初始分配的事务槽数,如果这个选项设置得太低(默认值为2,这也是最小值),可能导致多个用户访问的一个块上出现并发问题。如果一个数据块几乎已满,而且事务表无法动态扩展,会话就会排队等待这个块,因为每个并发事务都需要一个事务槽。如果你认为会对同样的块完成多个并发更新,就应该考虑增大这个值。

COMPRESS/NOCOMPRESS:ASSM和MSSM都适用。NOCOMPRESS反过来就是COMPRESS N,在此N是一个整数,表示要压缩的列数。

FREELISTS:仅适用于MSSM。每个表都会在一个freelist上管理堆中分配的块。一个表可以有多个freelist。如果有多个并发用户对表执行大量的插入,配置多个freelist可能会大大地改善性能(可能要以额外的存储空间为代价)。

PCTUSED仅适用于MSSM。度量一个块多空才允许再次插入行。如果块中已用的空间小于PCTUSED,就可以插入新行了。同样地,类似于PCTFREE,必须考虑你将如何使用表,从而适当地设置这个选项。

索引组织表

索引组织表(index organized table,IOT)就是存储在一个索引结构中的表。存储在堆中的表是无组织的(只要有可用的空间,数据可以放在任何地方),IOT中的数据则按主键存储和排序。IOT对信息获取、空间应用和OLAP应用特别有用。

虽然堆组织表会浪费空间(产生索引开销),然而,索引是一个复杂的数据结构,需要大量的工作来管理和维护,而且随着存储的行宽度有所增加,维护的需求也会增加。另一方面,相比之下,堆管理起来则很容易,在某些方面的效率要比IOT高。

如果大多数情况下,所需要数据都在堆组织表的索引上,此时非常适合采用IOT。如果只通过主键来访问表,也非常适合采用IOT。或者希望数据以某种特定的顺序物理存储,IOT就是一种合适的结构。如果经常在一个主键或惟一键上使用BETWEEN查询,数据有序地物理存储能提升这些查询的性能,就特别适合采用IOT。

建表时将表建为索引组织表,只需要在最后添加关键字“ORGANIZATION INDEX”即可。

索引组织表,有利于数据的聚集,根据键值将散列的行集中在同一个块中,在select时会减少I/O,增加效率。其具体优势如下:

IOT优势

  • 提高缓冲区缓存效率,因为给定查询在缓存中需要的块更少。
  • 减少缓冲区缓存访问,这会改善可扩缩性。
  • 获取数据的工作总量更少,因为获取数据更快。
  • 每个查询完成的物理I/O更少,因为对于任何给定的查询,需要的块更少,而且对地址记录的一个物理I/O很可能可以获取所有地址(而不只是其中一个地址,但堆表实现就只是获取一个地址)。

索引是一个复杂的数据结构,它不像堆那样随机组织,所以数据必须按部就班地存放到它该去的地方去。在堆中,块只是有时能插入新行,而索引则不同,块总是可以插入新的索引条目。如果每个数据(根据它的值)属于一个给定块,在总会放在那个块上,而不论这个块多满或者多空。

使用ANALYZE INDEX index_name VALIDATE STRUCTURE命令会填写一个名为INDEX_STATS的动态性能视图,其中最多只包含一行。

使用sqlplus查看一个表的索引名可使用如下命令(需要注意的是,表名需要大写):

select index_name,index_type from user_indexes where table_name = 'TABLE_NAME';

【分析索引举例】

analyze index iot_pk validate structure;

select lf_blks, br_blks, used_space,opt_cmpr_count, opt_cmpr_pctsave from index_stats;

其结果如图:

oracle-table3

由此显示出,我们的索引目前使用了429个叶子块(即数据所在的块),并使用了3个分支块(Oracle在索引结构中导航所用的块)来找到这些叶子块。使用的空间大约是3MB(3,081,451字节)。OPT_CMPR_COUNT(最优压缩数)说明如果你把这个索引置为COMPRESS 2,就会得到最佳的压缩。OPT_CMPR_PCTSAVE(最优的节省压缩百分比)则是说明,如果执行COMPRESS 2,就能节省大约1/3的存储空间,索引只会使用现在2/3的磁盘空间。

IOT是表,但是只是有其名而无其实,IOT段实际上是一个索引段。IOT本身可以有一个索引,就像在索引之上再加索引,这称为二次索引(secondary index)。与常规表相比,IOT上的索引效率稍低,不过我们可以使用非主键列提供IOT数据的快速、高效访问。

【插回顾】

查看表的建表语句,借助DBMS_METADATA.GET_DDL,如下:

select dbms_metadata.get_ddl('TABLE','DEPT',’SCOTT’) from dual;

此时,在sqlplus中会出现,没有显示全。这是因为默认pagesize显示行数为14行,只要翻下一页看就好了。也可以通过set pagesize=0设置不分页,同时,通过linesize设置每行显示字符数。

当然,最好在输出到txt里看。这时可以用spool path.filename指定一个输出文件,特别需要注意的是!只有当spool off出现后,内容才会真正被写入文件!

索引聚簇表

聚簇(cluster)是指:如果一组表有一些共同的列,则将这样一组表存储在相同的数据库块中,聚簇还表示把相关的数据存储在同一个块上。传统表和索引聚簇表的区别可见下图:

oracle-table4

什么时候要使用聚簇呢?可能反过来回答什么时候不应该使用聚簇会更容易一些:

  • 如果预料到聚簇中的表会大量修改:必须知道,索引聚簇会对DML的性能产生某种负面影响(特别是INSERT语句)。管理聚簇中的数据需要做更多的工作。
  • 如果需要对聚簇中的表执行全表扫描:不只是必须对你的表中的数据执行全面扫描,还必须对(可能的)多个表中的数据进行全面扫描。由于需要扫描更多的数据,所以全表扫描耗时更久。
  • 如果你认为需要频繁地TRUNCATE和加载表:聚簇中的表不能截除。这是显然的,因为聚簇在一个块上存储了多个表,必须删除聚簇表中的行。
  • 因此,如果数据主要用于读(这并不表示“从来不写”;聚簇表完全可以修改),而且要通过索引来读(可以是聚簇键索引,也可以是聚簇表上的其他索引),另外会频繁地把这些信息联结在一起,此时聚簇就很适合。

临时表

临时表(Temporary table)用于保存事务或会话期间的中间结果集,从当前登录用户的临时表空间分配存储空间。

不要在运行时在存储过程中创建表,DDL是一种代价昂贵的操作需要全力避免在运行时执行这种操作。一个应用的临时表应该在应用安装期间创建,绝对不要在运行时创建。

用PL/SQL在运行时动态创建全局临时表(或普通表)时可能遇到以下问题:

  • 在运行时执行DDL。DDL代价极其昂贵,包括数百个递归SQL,涉及大量串行化(一次执行一个,排队进行)。
  • 必须使用动态SQL使用,这会失去静态编译时SQL的所有好处。
  • 不能同时运行存储过程的两个副本。如,两者都试图创建或删除会引发冲突。
  • 过程出错时需要手动清除。

临时表可以有永久表的许多属性,它们可以有触发器、检查约束、索引等。但永久表的某些特性在临时表中并不支持,这包括:

  • 不能有引用完整性约束。临时表不能作为外键的目标,也不能在临时表中定义外键。
  • 不能有NESTED TABLE类型的列。在Oracle 9i及以前版本中,VARRAY类型的列也不允许;不过Oracle 10g中去掉了这个限制。
  • 不能是IOT。
  • 不能在任何类型的聚簇中。
  • 不能分区。
  • 不能通过ANALYZE表命令生成统计信息。

在所有数据库中,临时表的缺点之一是优化器不能正常地得到临时表的真实统计。使用基于代价的优化器(cost-based optimizer,CBO)时,有效的统计对于优化器的成败至关重要。如果没有统计信息,优化器就只能对数据的分布、数据量以及索引的选择性作出猜测。如果这些猜测是错的,为查询生成的查询计划(大量使用临时表)可能就不是最优的。在许多情况下,正确的解决方案是根本不使用临时表,而是使用一个INLINE VIEW(要看INLINE VIEW的例子,可以查看前面运行的SELECT,它就有两个内联视图)。采用这种方式,Oracle可以访问一个表的所有相关统计信息,而且得出一个最优计划。

如果应用中需要临时存储一个行集由其他表处理(可能对应一个会话,也可能对应一个事务),临时表就很有用。不要把临时表作为一个分解大查询的方法,即拿到一个大查询,把它“分解”为几个较小的结果集,然后再把这些结果集合并在一起(这看来是其他数据库中最常见的临时表用法)。实际上,你会发现,在几乎所有的情况下。Oracle中如果将一个查询分解为较小的临时表查询,与原来的一个查询相比,只会执行得更慢。

索引聚簇表、散列聚簇表、有序散列聚簇表、嵌套表及对象表现工作中并非涉及,却本处不予以记录!

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

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

给我留言

留言无头像?