Concept-第18章翻译

18 分区表和分区索引

这篇文章主要描述分区表和分区索引。主要有下面的内容：

*分区的介绍

*分区的方法概述

*分区索引的概述

*利用分区技术改进系统性能

注意：只有在你采用这个分区选项，这个功能才是可获取的。

 

分区的介绍

分区是采用分解非常大的表以及大的索引为更加小的以及更加方便管理的分区来解决大表以及大索引带来的问题。为了能够访问已经分好区的表，SQL查询以及DML语句不需要作修改。但是，在分区已经定义好之后，DDL语句能够访问以及操纵单独的分区而不是整个表和整个索引。这个就是分区技术如何使大的数据库对象的管理简单化。同时，分区对于应用来说也是完全透明的。

一个表或者索引的每个分区必须有相同的逻辑属性，比如：列名，数据类型，以及约束，但是每个分区都能够有单独的物理属性，比如：PCTFREE，PCTUSED，以及表空间。

分区对于不同类型的应用来说是有用的，特别是对于那些管理大量数据量的应用。OLTP系统经常从分区的方法中获取在管理和可用性上的改进，同时对于数据仓库来说会得到性能和管理上的改进。

注意；一个被分区的对象的所有分区必须存储在只有单独的数据块的表空间中

 

分区有下面的优势：

*分区使数据管理操作比如：数据加载，索引创建和重建，以及备份和恢复是在分区级别上，而不是在整张表中。这样会显著地减少对这些操作所需要的时间；

*分区改进了查询性能。在许多案例中，查询的结果只需要访问部分分区的集合就可以，而不需要访问整张表。对于一些查询，这个技术（分区排除）能够在性能上带来几个数据量的提高；

*分区能够为维护操作显著地降低计划停机的影响。

对于分区维护操作，分区的独立性让你能够对整张表或者索引的不同分区执行并发地维护操作。你能够并发地对没有受维护操作影响的分区执行SELECT以及DML操作。

*分区增强了数据库的可用性，如果关键的表和索引被分为多个分区来减少维护窗口，恢复时间以及降低故障所带来的影响。

*分区可以不需要对你的应用程序做任何的修改就可以执行。比如：你能够不需要修改任何访问该表的select 语句或者DML语句就可以将一个非分区表转换为一个分区表。你不需要为了利用好分区而重写应用程序的代码。

图18-1 提供了一个图形化的视图来展示分区表是如何不同于非分区表的

图18-1 一个分区表的视图

 

分区键

在分区表中的每一行明确地被分配给每个分区。这个分区键一列或者多列的集合，是用来决定每一行的所属分区的。Oracle通过使用分区键来判定合适的分区，然后自动地进行直接插入，更新，以及删除操作。一个分区键：

*是由一个排好序的列表组成；

*不能包含level,rowed,或者mlslabel虚列或者是rowid类型的列；

*能够包含那些可为NULL的列

 

分区表

表可以被分区为64，000个独立的小分区。任何表都能够被分区除了那些包含LONG或者long raw数据类型的列的表。那些包含clob或者blob列的表可以被分区。

注意：为了减少磁盘和内存（特别是缓存）的使用，你可以在数据库中以压缩的形式来存储表和分区表。这样经常会对只读操作有更好的可伸缩性。表压缩也能够加速查询速度。但是这样消耗一些CPU成本。

 

分区索引组织表

你可以根据范围，列表，以及哈希算法将索引表分区。已经分区的索引表对于索引组织表的管理，可用性以及性能方面的改进是非常有用的。除此之外，使用索引组织的表的data cartridges可以利用分区的技术对其数据进行区分。Image cartridges和 interMedia cartridges可以采用此种方式进行分区。

对于一个索引组织表的分区：

*分区列必须是主键列的集合

*辅助索引能够被分区—本地的和全局的

* OVERFLOW数据段分区和表分区是相同的

 

分区方法的概述

Oracle提供了下面的分区方法：

*范围分区

*列表分区

*哈希算法分区

*复合分区

 

图18-2 提供了一个图形化的视图来展示分区的方法：

图18-2 列表，范围，以及哈希算法的分区

 

复合分区是其他分区方法的一个组合。Oracle支持范围-哈希以及范围-列表的复合分区。图18-3 提供了一个图形化的范围-哈希以及范围-列表的复合分区的视图：

图18-3 复合分区

 

范围分区

范围分区根据你建立每个分区的分区键的范围来将数据对应到每个分区中。这个分区方法是最普遍的分区类型并且经常对日期类型使用。比如：你可能想对销售进行分区按照每月来分区。

当使用范围分区的时候，考虑下面的规则：

*每个分区都有一个values less than语句，这个语句为每个分区定义了上界（不包含本身）。分区键的任何一个等于或者大于此修饰符（literal）的数据将被存储到下一个分区中；

*所有的分区，除了第一个，都有一个隐含的下界限制，这个下界是由前一个分区的values less than 语句来定义的；

*可以为最大的分区定义一个MAXVALUE修饰符的数据。MAXVALUE代表一个虚拟的无限值，这个值用于识别出高于任何其他可能的分区键的值，包括null值。

一个典型的例子如下列出。这个语句创建了一张表(sales_range)，这个表是一个基于sales_date字段的范围分区表。

 

范围分区的例子

CREATE TABLE sales_range 

(salesman_id  NUMBER(5), 

salesman_name VARCHAR2(30), 

sales_amount  NUMBER(10), 

sales_date    DATE)

PARTITION BY RANGE(sales_date) 

(

PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','MM/DD/YYYY')),

PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','MM/DD/YYYY')),

PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','MM/DD/YYYY')),

PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','MM/DD/YYYY'))

);

 

列表分区

列表分区使你能够明确地控制行是如何对应到分区的。你可以通过在描述中为每个分区的分区键定义散列的值的列表来控制行是如何对应到分区的。这个不同于范围分区，范围分区是一定范围的值与分区有关，也与哈希分区不同，哈希分区是使用哈希函数来控制行与分区的对应关系。列表分区的优势是你可以对无序的以及不相关的数据进行分组整理。

 

列表分区的细节可以使用下面的例子来讲解。在这个例子中，你想按照区域来对销售表进行分区。那就意味着就象下面的例子中根据它们的地理位置按照洲来分组。

 

列表分区例子

CREATE TABLE sales_list

(salesman_id  NUMBER(5), 

salesman_name VARCHAR2(30),

sales_state   VARCHAR2(20),

sales_amount  NUMBER(10), 

sales_date    DATE)

PARTITION BY LIST(sales_state)

(

PARTITION sales_west VALUES('California', 'Hawaii'),

PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),

PARTITION sales_central VALUES('Texas', 'Illinois'),

PARTITION sales_other VALUES(DEFAULT)

);

 

通过检查该行分区列的值是否落在分区所描述的值的集合里面来将一行记录映射到一个分区表中。例如：行记录被按照下面的方式插入：

*(10, 'Jones', 'Hawaii', 100, '05-JAN-2000')对应分区sales_west

*(21, 'Smith', 'Florida', 150, '15-JAN-2000')对应分区sales_east

*(32, 'Lee', 'Colorado', 130, '21-JAN-2000')对应分区sales_other

不象范围分区和哈希分区，多列分区键对于列表分区来说是不支持的。如果一张表是通过列表来分区的，分区键只能够由一个单独的表列组成。

默认的分区使你能够通过使用默认的分区避免来为一个列表分区表定义所有可能的值，以便所有那些没有对应到任何其他分区的行不产生一个错误。

 

哈希分区

哈希分区将不适合采用范围分区或者列表分区的数据进行哈希分区。哈希分区的语法简单且易于实现。当在下面的情况下使用哈希分区比使用范围分区要好：

*对应于一个被给定的范围你预先不知道有多少的数据量；

*各个范围分区的容量会完全不一样或者很难进行人工平衡；

*范围分区会引起数据不是所想要的集中；

*性能特征比如并行DML，分区排除，以及基于分区的关联是很重要的。

分割，删除，或者合并分区的概念不适合于哈希分区。取而代之的是，哈希分区能够添加分区以及接合分区。

 

哈希分区的例子

CREATE TABLE sales_hash

(salesman_id  NUMBER(5), 

salesman_name VARCHAR2(30), 

sales_amount  NUMBER(10), 

week_no       NUMBER(2)) 

PARTITION BY HASH(salesman_id) 

PARTITIONS 4 

STORE IN (ts1, ts2, ts3, ts4);

 

上面的语句创建了一张表sales_hash，是依据salesman_id字段来进行哈希分区的。表空间名为ts1,ts2,ts3,ts4。使用这种语法，可以确保我们采用循环的方式通过指定表空间来将数据存储到创建的分区中去。

 

复合分区

复合分区使用范围分区方式对数据进行分区，并且对于每个分区，由使用哈希分区或者列表分区的方式来进行进一步的分区，产生子分区。复合的范围—哈希分区既具有范围分区的管理优势由具有哈希分区的数据分布，条带化以及并行化的优势。符合的范围—列表分区既具有范围分区的可管理性优化，由具有列表分区的显式控制能力。复合分区支持历史操作，比如添加新的范围分区，但是也通过子分区为DML操作提供更高的并行度并且对数据分布进行精细的控制。

 

复合的范围-哈希分区的例子

CREATE TABLE sales_composite 

(salesman_id  NUMBER(5), 

 salesman_name VARCHAR2(30), 

 sales_amount  NUMBER(10), 

 sales_date    DATE)

PARTITION BY RANGE(sales_date) 

SUBPARTITION BY HASH(salesman_id)

SUBPARTITION TEMPLATE(

SUBPARTITION sp1 TABLESPACE ts1,

SUBPARTITION sp2 TABLESPACE ts2,

SUBPARTITION sp3 TABLESPACE ts3,

SUBPARTITION sp4 TABLESPACE ts4)

(PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','MM/DD/YYYY'))

 PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','MM/DD/YYYY'))

 PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','MM/DD/YYYY'))

 PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','MM/DD/YYYY'))

 PARTITION sales_may2000 VALUES LESS THAN(TO_DATE('06/01/2000','MM/DD/YYYY')));

 

这个语句是创建一张表sales_composite是基于sales_date范围分区的并且是依据salesman_id字段来哈希子分区的。当你使用一个模板，oracle通过连接分区名加下划线加来自模板的子分区名给子分区命名。Oracle将子分区存储在在模板中指定的表空间中。在前一个语句中，sales_jan2000_sp1被创建并且存储在TS1表空间中，同时sales_jan2000_sp4被创建并且存储在TS4表空间中。以同样的方式，sales_apr2000_sp1也是被创建并且放置在表空间TS1中同时sales_apr2000_sp4也被创建并且被放置在表空间TS4中。图18-4 提供了一个前一个例子的图形化的视图。

图18-4 复合的范围哈希分区

 

复合的范围-列表分区例子

CREATE TABLE bimonthly_regional_sales

(deptno NUMBER, 

 item_no VARCHAR2(20),

 txn_date DATE, 

 txn_amount NUMBER, 

 state VARCHAR2(2))

PARTITION BY RANGE (txn_date)

SUBPARTITION BY LIST (state)

SUBPARTITION TEMPLATE(

SUBPARTITION east VALUES('NY', 'VA', 'FL') TABLESPACE ts1,

SUBPARTITION west VALUES('CA', 'OR', 'HI') TABLESPACE ts2,

SUBPARTITION central VALUES('IL', 'TX', 'MO') TABLESPACE ts3)

(

PARTITION janfeb_2000 VALUES LESS THAN (TO_DATE('1-MAR-2000','DD-MON-YYYY')),

PARTITION marapr_2000 VALUES LESS THAN (TO_DATE('1-MAY-2000','DD-MON-YYYY')),

PARTITION mayjun_2000 VALUES LESS THAN (TO_DATE('1-JUL-2000','DD-MON-YYYY'))

);

 

这个语句创建一张表bimonthly_regional_sales是根据txn_date字段来进行范围分区的并且根据state字段来进行列表子分区。当你使用一个模板时，oracle通过连接分区名，下划线以及来自模板的子分区名来给子分区命名。Oracle将子分区存储在模板中指定的表空间中。在前一个例子中，janfeb_2000_east被创建并且存储在TS1表空间中同时janfeb_2000_central被创建并且被存储在TS3表空间中。以同样的模式，mayjun_2000_east被存储在表空间TS1中同时mayjun_2000_central被存储在表空间TS3中。图18-5 提供了表bimonthly_regional_sales的一个图形化的视图并且该分区的9个单独的子分区。

图18-5 复合的范围-列表分区

 

什么时候使用分区表

对于什么时候使用分区表这里给了一些建议：

*大于2G的表应该使用考虑使用分区表

*包含历史数据的表，在该表中新的数据被加进新的分区中。一个典型的例子是历史数据表因为在历史数据表中只有当前月的数据是可更新的并且其他11个月的数据是只读的。

 

分区索引的概述

就象分区表一样，分区索引也能够对管理，可用性，性能以及可伸缩性方面有所改进。它们可以被独立的被划分出来（全局索引）或者自动地连接到表的分区中（局部索引）。一般而言，你应该为OLTP应用程序使用全局索引，为数据仓库或者DSS应用程序使用局部索引。但是，不管什么时候，你应该尽可能的尝试使用本地索引因为它们更容易被管理。当你决定使用哪一种分区的时候，你应该按照顺序考虑下面的向导：

1、  如果表分区的列是索引键的一个子集，使用局部索引。如果是这样的情况的话，就结束；如果不是索引键的一个子集，则继续走向导2；

2、  如果索引是唯一索引，使用全局索引。如果是这样的，就结束；如果不是唯一索引，则继续向导3；

3、  如果你的优先考虑是易管理，使用被地索引。如果是这样的话，就结束；如果不是，则继续向导4；

4、  如果应用是一个OLTP系统并且用户需要快速的响应时间，使用全局索引。如果应用是一个DSS系统并且用户对数据的吞吐量更加关注，则使用局部索引。

 

局部分区索引

局部索引是比其他类型的分区索引更易于管理。局部索引也提供高可用性并且在DSS环境中是常用的。使用局部索引的原因是局部索引是与所在的分区是采用相同的分区方式：每个局部索引的分区是和表中的一个分区紧密联系的。这样就能使oracle自动地将索引分区和表分区保持同步，并且使每个表--索引分区对相对独立。任何使一个分区的数据无效或者不可用的行为只影响一个单独的分区。

当有一张表的分区或者子分区的维护操作的话，局部分区索引支持更好的可用性。一个索引叫局部非前缀的索引对于历史数据库是非常有用的。这种类型的索引，分区技术不依赖索引列的左前缀。

你不能显式地对局部索引添加一个分区。取而代之的是，只有当你向表中添加一个分区时，新的分区才能够被添加到局部索引中。同样地，你不能显式地从一个局部索引中删除一个分区。但是，只有在当你从一个表中删除一个分区时，局部索引才能够被删除。

 

一个局部索引可以是唯一的。但是，为了能够使局部索引唯一，表中的分区键必须是索引键列的一部分。唯一局部索引对于OLTP系统来说是非常有用的。

 

图18-6 提供了局部分区索引的图形化的视图

 

全局分区索引

Oracle提供两种类型的全局分区索引：范围分区索引以及哈希分区索引

 

全局范围分区索引

全局范围区分索引是非常灵活的因为分区的程度以及分区键是独立于表的分区方式的。全局范围分区索引一般是用在OLTP环境中并且对任何单独的记录提供高效的访问。

全局索引的最高分区必须有个分区界限，所有的全局所有都有MAXVALUE。这样确保在表中的所有行都能够在索引中找到。全局前缀索引能够是唯一的或者是非唯一的。

你不能为一个全局索引添加一个分区因为最后一个分区总是以MAXVALUE作为分区的边界如果你期望添加一个新的最高分区，使用alter index split partition 语句。如果一个全局索引分区是空的，你可以通过alter index drop partiton语句来显式地删除该分区。如果一个全局索引分区包含数据的话，删除该索引分区会导致下一个最高分区被标识为不可用。你不能删除在全局索引中的最高分区。

 

全局哈希分区索引

对于单调增长的索引可以通过使用全局哈希分区索引来将数据分散开来改进性能。换句话说，索引的大多数插入操作是只发生在索引的右边界。

 

全局分区索引的维护

默认地，对全局索引所在的堆表（heap-organized table）进行以下操作将导致索引被标识为不可用：

ADD (HASH)

COALESCE (HASH)

DROP

EXCHANGE

MERGE

MOVE

SPLIT

TRUNCATE

 

这些索引可以通过添加update indexes语句到上面操作的SQL语句中的方式就可以对索引进行维护操作的。维护全局索引的优势如下：

*在整个操作过程中索引是保持可用并且保持是联机的；

*索引在上面的操作之后不需要重建。

 

例子：

Alter table drop partition p1 update indexes;

 

注意：这个特性只支持堆组织的表

 

图18-7 提供了全局分区索引的图形化视图：

图18-7 全局分区索引

 

 

全局非分区索引

全局非分区索引的行为特性类似于一个非分区的索引。在OLTP系统中是经常被使用的并且能够对任何单独的记录提供高效的访问。

图18-8 提供一个全局非分区索引的图形化视图

图18-8 全局非分区索引

 

在分区表上创建索引的其他信息

你能够在分区表上创建一个位图索引，但有一个限制就是位图索引对于分区表来说必须只能是局部索引。位图索引不能是全局索引。

全局索引能够是唯一的。局部索引只有在分区键是索引键的一部分的时候才能够是唯一的索引。

 

在OLTP应用中使用分区索引

对于OLTP系统应用有下面一些向导：

*全局索引以及唯一局部索引会比非唯一局部索引提供更好的性能，因为全局索引以及唯一局部索引会使检查索引分区的数量最小化；

*局部索引在当有对该分区表有分区或者子分区维护操作时会提供更好的可用性；

*当索引是单调增长时，哈希分区的全局索引通过均匀分布索引的方式来提高性能。换句话说，大多数索引的插入操作发生在一个索引的右边界。

 

在数据仓库和DSS应用中使用分区索引

对于数据仓库和DSS应用来说有一些向导：

*局部索引是更可取的因为局部索引在数据加载过程中和在分区维护操作过程中更易于管理；

*局部索引能够改进性能因为许多索引分区能够通过索引键的范围查询来并行地扫描索引分区；

 

复合分区的分区索引

对于复合分区表的分区索引的使用有下面几点需要记住：

*子分区索引总是局部索引并且默认情况下和表的子分区存储在一起；

*用户可以为整个索引分区设置表空间，或者为子索引分区社顶表空间；

 

利用分区技术提高系统性能

分区技术能够帮助你改进性能和改进管理。因为下面的原因而使用分区技术有几点内容需要明记在心：

*分区剪除

*基于分区的关联

*并行DML操作

 

分区剪除

oracle数据库服务显式地识别出分区和子分区。然后数据库服务优化SQL语句来标识那些需要被访问的区分或者子分区并且剪除那些不需要被SQL语句访问的分区或者子分区。换句话说，分区剪除是在查询中跳过不必要访问的索引和数据分区或者子分区。

对于每个SQL语句，依据所指定的查询标准，不需要的分区或者子分区能够被排除。例如；如果一个查询只包括三月的销售数据，然后就没有必要获取剩下的11个月的数据。这样智能的剪除减少了数据量，因而产生的是在查询性能上的真实的改进。

如果优化器确定那些被访问的分区或者子分区的所有记录满足用来分区剪除的查询标准，那么优化器将会在评估阶段为了改进性能从谓词列表（where 语句）中删除了那些标准。但是，如果SQL语句对分区键应用了一个函数（使用to_date函数除外），那么优化器就不能剪除分区。同样地，如果SQL语句中对一个索引列上应用了函数，除了是基于函数的索引，优化器也不能使用该索引。

只有当索引和表是依据不同的列来进行分区的，这样即使当在表分区不能够进行分区剪除时，也能够对索引分区进行索引剪除。你可以通过创建分区索引来减少你的SQL语句需要访问或者修改的数据量的方式，来改进对于大表操作的性能。

对于等于，范围，LIKE，以及IN-列表谓词可以考虑使用范围或者列表分区来分区剪除，并且等于以及IN-列表谓词也可以考虑使用哈系分区来进行分区剪除。

 

分区剪除的例子

你有一张分区表叫cust_orders。cust_orders的分区键是order_date。假设cust_orders有6个月的数据，1月到6月，每个月使用一个分区。如果进行下面的查询：

SELECT SUM(value)

FROM cust_orders

WHERE order_date BETWEEN '28-MAR-98' AND '23-APR-98';

分区剪除是可以通过下面方式实现的：

*首先，分区剪除了1月，2月，5月以及6月的数据分区。然后

*3月和4月的数据分区使用索引扫描是因为该索引的高筛选率

  或者

*如果索引是低筛选率，则使用对3月和4月的数据分区的全表扫描。

 

基于分区的关联

基于分区的关联是指将两张分区表都使用分区键来进行连表的的连表优化措施。使用基于分区的关联方式，连表操作可以被分解为多个小的连表，这样可以采用串行或者并行的执行方式。可以从另一个角度来待基于分区的关联所做的优化工作，通过考虑数据分布来在并行连表的执行过程中减少并行关联进程之间数据交换的数据量。

 

并行DML

并行执行在与决策支持系统和数据仓库系统相关的大数据库上的大数据量操作时，动态地减少了响应时间。除了传统数据表之外，你也可以对范围分区和哈系分区的表使用并行查询和并行DML操作。通过这样做，你可以提高批处理操作的可伸缩性和性能。

不管用户是够使用索引组织的表，对于并行DML操作的语法和限制是相同的。