oracel分区之表分区 – 但行好事莫问前程

分区
分区（partitioning）最早在Oracle 8.0中引入，这个过程是将一个表或索引物理地分解为多个更小、更可管理的部分。就访问数据库的应用而言，逻辑上讲只有一个表或一个索引，但在物理上这个表或索引可能由数十个物理分区组成。每个分区都是一个独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。考虑使用分区原因是多方面的，可能是分区能提高数据的可用性，或者是可以减少管理员的负担，另外在某些情况下，还可能提高性能。

分区只是一个工具，对索引或表进行分区时可能发生3种情况：使用这些分区表的应用可能运行得更慢；可能运行得更快；有可能没有任何变化。

分区概述
分区有利于管理非常大的表和索引，它使用了一种“分而治之”的逻辑。分区引入了一种分区键（partition key）的概念，分区键用于根据某个区间值（或范围值）、特定值列表或散列函数值执行数据的聚集。分区有以下好处：
（1）提高数据的可用性：这个特点对任何类型的系统都适用，而不论系统本质上是OLTP还是仓库系统。
（2）由于从数据库中去除了大段，相应地减轻了管理的负担。
（3）改善某些查询的性能：主要在大型仓库环境中有这个好处，通过使用分区，可以消除很大的数据区间，从而不必考虑它们，相应地根本不用访问这些数据。但这在事务性系统中并不适用，因为这种系统本身就只是访问少量的数据。
（4）可以把修改分布到多个单独的分区上，从而减少大容量OLTP系统上的竞争：如果一个段遭遇激烈的竞争，可以把它分为多个段，这就可以成比例地减少竞争。

下面分别讨论使用分区可能带来的这些好处。

可用性的提高源自于每个分区的独立性。对象中一个分区的可用性（或不可用）并不意味着对象本身是不可用的。优化器知道有这种分区机制，会相应地从查询计划中去除未引用的分区。在一个大对象中如果一个分区不可用，你的查询可以消除这个分区而不予考虑，这样Oracle就能成功地处理这个查询。

为了展示这种可用性的提高，我们将建立一个散列分区表，其中有两个分区，分别在单独的表空间中。这里将创建一个EMP表，它在EMPNO列上指定了一个分区键（EMPNO就是我们的分区键）。在这种情况下，这个结构意味着：对于插入到这个表中的每一行，会对EMPNO列的值计算散列，来确定这一行将置于哪个分区（及相应的表空间）中
先创建分区对应的表空间:

sys@JINGYONG> create tablespace p1 datafile '/u01/app/oracle/product/10.2.0/orad
ata/jingyong/jingyong/p101.dbf' size 50M;

表空间已创建。

sys@JINGYONG> create tablespace p2 datafile '/u01/app/oracle/product/10.2.0/orad
ata/jingyong/jingyong/p201.dbf' size 50M;

表空间已创建。

sys@JINGYONG> create table emp
  2  (empno int,
  3  ename varchar2(20)
  4  )
  5  partition by hash(empno)
  6  (partition part_1 tablespace p1,
  7  partition part_2 tablespace p2
  8  )
  9  ;

表已创建。

接下来，我们向表中插入一些数据，然后使用带分区的扩展表名检查各个分区的内容：

sys@JINGYONG> insert into emp select empno,ename from scott.emp;

已创建14行。

sys@JINGYONG> select * from emp partition(part_1);

     EMPNO ENAME
---------- --------------------
      7369 SMITH
      7499 ALLEN
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7839 KING
      7876 ADAMS
      7934 MILLER

已选择8行。

sys@JINGYONG> select * from emp partition(part_2);

     EMPNO ENAME
---------- --------------------
      7521 WARD
      7566 JONES
      7788 SCOTT
      7844 TURNER
      7900 JAMES
      7902 FORD

已选择6行。

应该能注意到，数据的“摆放”有些随机。通过使用散列分区，我们让Oracle随机地（很可能均匀地）将数据分布到多个
分区上。我们无法控制数据要分布到哪个分区上；Oracle会根据生成的散列键值来确定。

下面将其中一个表空间脱机（例如，模拟一种磁盘出故障的情况），使这个分区中的数据不可用：

sys@JINGYONG> alter tablespace p1 offline;

表空间已更改。

接下来，运行一个查询，这个查询将命中每一个分区，可以看到这个查询失败了：

sys@JINGYONG> select * from emp;
select * from emp
              *
第 1 行出现错误:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/u01/app/oracle/product/10.2.0/oradata/jingyong/jingyon
g/p101.dbf'

不过，如果查询不访问脱机的表空间，这个查询就能正常工作；Oracle会消除脱机的分区而不予考虑。

sys@JINGYONG> select * from emp where empno=7844;

     EMPNO ENAME
---------- --------------------
      7844 TURNER

总之，只要优化器能从查询计划消除分区，它就会这么做。基于这一点，如果应用在查询中使用了分区键，就能提高这些应用的可用性。

分区还可以通过减少停机时间来提高可用性。例如，如果有一个200GB的表，它划分为100个2GB的分区，这样就能更快地从错误中恢复。如果某个2GB的分区遭到破坏，现在恢复的时间就只是恢复一个2GB分区所需的时间，而不是恢复一个200GB表的时间。所以从两个方面提高了可用性：
(1) 优化器能够消除分区，这意味着许多用户可能甚至从未注意到某些数据是不可用的。
(2) 出现错误时的停机时间会减少，因为恢复所需的工作量大幅减少。

减少管理负担
之所以能减少管理负担，这是因为与在一个大对象上执行操作相比，在小对象上执行同样的操作从本质上讲更为容易、速度更快，而且占用的资源也更少。

例如，假设数据库中有一个100GB的索引。如果需要重建这个索引，而该索引未分区，你就必须将整个100GB的索引作为一个工作单元来重建。尽管可以在线地重建索引，但是要完全重建完整的100GB索引，还是需要占用大量的资源。至少需要在某处有100GB 的空闲存储空间来存放索引的副本，还需要一个临时事务日志表来记录重建索引期间对基表所做的修改。另一方面，如果将索引本身划分为100个1GB的分区，就可以一个接一个地单独重建各个索引分区。现在只需要原先所需空闲空间的100%。另外，各个索引的重建也更快（可能是原来的100倍），需要向新索引合并的事务修改也更少（到此为止，在线索引重建期间发生的事务修改会更少）。

另外请考虑以下情况：100GB索引的重建即将完成之前，如果出现系统或软件故障会发生什么。我们所做的全部努力都会付诸东流。如果把问题分解，将索引划分为1GB的分区，你最多只会丢掉重建工作的1%。或者，你可能只需要重建全部聚集索引的1%，例如，只是“最新”的数据（活动数据）需要重组，而所有“较旧”的数据（相当静态）不受影响。

最后，请考虑这样一种情况：你发现表中50%的行都是“移植”行（行链接或行迁移），可能想进修正。建立一个分区表将有利于这个操作。为了“修正”移植行，你往往必须重建对象，在这种情况下，就是要重建一个表。如果有一个100GB的表，就需要在一个非常大的“块”（chunk）上连续地使用ALTER TABLE MOVE来执行这个操作。另一方面，如果你有25个分区，每个分区的大小为4GB，就可以一个接一个地重建各个分区。或者，如果你在空余时间做这个工作，而且有充足的资源，甚至可以在单独的会话中并行地执行ALTER TABLE MOVE语句，这就很可能会减少整个操作所需的时间。对于一个未分区对象所能做的工作，分区对象中的单个分区几乎都能做到。你甚至可能发现，移植行都集中在一个很小的分区子集中，因此，可以只重建一两个分区，而不是重建整个表。

下面有一个小例子，展示了如何对一个有多个移植行的表进行重建。TABLE1和TABLE2都是从TABLE_T的一个10,000,000行的实例创建的.TABLE1是一个常规的未分区表，而TABLE2是一个散列分区表，有8个分区:

sys@JINGYONG> create table table1
2 ( ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
3 OBJECT_ID, DATA_OBJECT_ID,
4 OBJECT_TYPE, CREATED, LAST_DDL_TIME,
5 TIMESTAMP, STATUS, TEMPORARY,
6 GENERATED, SECONDARY )
7 tablespace table1
8 as
9 select ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
10 OBJECT_ID, DATA_OBJECT_ID,
11 OBJECT_TYPE, CREATED, LAST_DDL_TIME,
12 TIMESTAMP, STATUS, TEMPORARY,
13 GENERATED, SECONDARY
14 from table_T;
Table created.

sys@JINGYONG> create table table2
2 ( ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
3 OBJECT_ID, DATA_OBJECT_ID,
4 OBJECT_TYPE, CREATED, LAST_DDL_TIME,
5 TIMESTAMP, STATUS, TEMPORARY,
6 GENERATED, SECONDARY )
7 partition by hash(id)
8 (partition part_1 tablespace table2,
9  partition part_2 tablespace table2,
10 partition part_3 tablespace table2,
11 partition part_4 tablespace table2,
12 partition part_5 tablespace table2,
13 partition part_6 tablespace table2,
14 partition part_7 tablespace table2,
15 partition part_8 tablespace table2
16 )
17 as
18 select ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
19 OBJECT_ID, DATA_OBJECT_ID,
20 OBJECT_TYPE, CREATED, LAST_DDL_TIME,
21 TIMESTAMP, STATUS, TEMPORARY,
22 GENERATED, SECONDARY
23 from table_t;
Table created.

现在，每个表都在自己的表空间中，所以我们可以很容易地查询数据字典，来查看每个表空间中已分配的空间和空闲空间：

sys@JINGYONG> select b.tablespace_name,
2 mbytes_alloc,
3 mbytes_free
4 from ( select round(sum(bytes)/1024/1024) mbytes_free,
5 tablespace_name
6 from dba_free_space
7 group by tablespace_name ) a,
8 ( select round(sum(bytes)/1024/1024) mbytes_alloc,
9 tablespace_name
10 from dba_data_files
11 group by tablespace_name ) b
12 where a.tablespace_name (+) = b.tablespace_name
13 and b.tablespace_name in ('table1','table2')
14 /
TABLESPACE MBYTES_ALLOC MBYTES_FREE
---------- ------------ -----------
TABLE1 1596 524
TABLE2 1596 524

table1和table2的大小都大约是1.6GB，每个表空间都有524MB的空闲空间。我们想创建第一个表TABLE1：

sys@JINGYONG> alter table table1 move;
alter table table1 move
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 1024 in tablespace table1

但失败了,table1表空间中要有足够的空闲空间来放下ABLE1的完整副本，同时它的原副本仍然保留，简单地说，我们需要一个很短的时间内有大约两倍的存储空间（可能多一点，也可能少移动，这取决于重建后表的大小）。现在试图对TABLE2执行同样的操作

sys@JINGYONG> alter table table2 move;
alter table table2 move
*
ERROR at line 1:
ORA-14511: cannot perform operation on a partitioned object

这说明，Oracle在告诉我们：无法对这个“表”执行MOVE操作；我们必须在表的各个分区上执行这个操作。可以逐个地移动（相应地重建和重组）各个分区：

sys@JINGYONG> alter table table2 move partition part_1;
Table altered.
sys@JINGYONG> alter table table2 move partition part_2;
Table altered.
sys@JINGYONG> alter table table2 move partition part_3;
Table altered.
sys@JINGYONG> alter table table2 move partition part_4;
Table altered.
sys@JINGYONG> alter table table2 move partition part_5;
Table altered.
sys@JINGYONG> alter table table2 move partition part_6;
Table altered.
sys@JINGYONG> alter table table2 move partition part_7;
Table altered.
sys@JINGYONG> alter table table2 move partition part_8;
Table altered.

对于每个移动，只需要有足够的空闲空间来存放原来数据的1/8的副本！因此，假设有先前同样多的空闲空间，这些命令就能成功。我们需要的临时资源将显著减少。不仅如此，如果在移动到PART_4后但在PART_5完成“移动”之前系统失败了（例如，掉电），我们并不会丢失以前所做的所有工作，这与执行一个MOVE语句的情况不同。前4个分区仍是“移动”后的状态，等系统恢复时，我们可以从分区PART_5继续处理。也可以很容易地编写一个脚本来解决上面输入8次语句的题：

sys@JINGYONG> begin
2 for x in ( select partition_name
3 from user_tab_partitions
4 where table_name = 'TABLE2' )
5 loop
6 execute immediate
7 'alter table table2 move partition ' ||
8 x.partition_name;
9 end loop;
10 end;
11 /
PL/SQL procedure successfully completed.

关于分区和管理，还有一个因素需要考虑，这就是在维护数据仓库和归档中使用数据“滑动窗口”。在许多情况下，需要保证数据在最后N个时间单位内一直在线。例如，假设需要保证最后12个月或最后5年的数据在线。如果没有分区，这通常是一个大规模的INSERT，其后是一个大规模的DELETE。为此有相对多的DML，并且会生成大量的redo和undo。如果进行了分区，则只需做下面的工作：
（1）用新的月（或年，或者是其他）数据加载一个单独的表。
（2）对这个表充分建立索引（这一步甚至可以在另一个实例中完成，然后传送到这个数据库中）。
（3）将这个新加载（并建立了索引）的表附加到分区表的最后，这里使用一个快速DDL命令：ALTER TABLE EXCHANGE PARTITION。
（4）从分区表另一端将最旧的分区去掉。

这样一来，现在就可以很容易地支持包含时间敏感信息的非常大的对象。数据很容易地从分区表中去除，如果不再需要它，可以简单地将其删除；或者也可以归档到某个地方。新数据可以加载到一个单独的表中，这样在加载、建索引等工作完成之前就不会影响分区表.

改善语句性能
分区最后一个好处体现在改进语句（SELECT、INSERT、UPDATE、DELETE、MERGE）的性能方面。我们来看两类语句，一种是修改信息的语句，另一种是只读取信息的语句，并讨论在这种情况下可以从分区得到哪些好处。
1. 并行DML
修改数据库中数据的语句有可能会执行并行DML（parallel DML，PDML）。采用PDML时，Oracle使用多个线程或进程来执行 INSERT、UPDATE或DELETE，而不是执行一个串行进程。在一个有充足I/O带宽的多CPU主机上，对于大规模的DML操作，速度的提升可能相当显著。在Oracle9i以前的版本中，PDML要求必须分区。如果你的表没有分区，在先前的版本中就不能并行地执行这些操作。如果表确实已经分区，Oracle会根据对象所有的物理分区数为对象指定一个最大并行度。从很大程度上讲，在Oracle9i及以后版本中这个限制已经放松，只有两个突出的例外；如果希望在一个表上执行PDML，而且这个表的一个LOB列上有一个位图索引，要并行执行操作就必须对这个表分区；另外并行度就限制为分区数。不过，总的说来，使用PDML并不一定要求进行分区。

查询性能
在只读查询（SELECT语句）的性能方面，分区对两类特殊操作起作用：
分区消除（partition elimination）：处理查询时不考虑某些数据分区。我们已经看到了一个分区消除的例子。
并行操作（parallel operation）：并行全表扫描和并行索引区间扫描就是这种操作的例子。
不过，由此得到的好处很多程度上取决于你使用何种类型的系统。

OLTP系统
在OLTP系统中，不应该把分区当作一种大幅改善查询性能的方法。实际上，在一个传统的OLTP系统中，你必须很小心地应用分区，提防着不要对运行时性能产生负面作用。在传统的OLTP系统中，大多数查询很可能立即返回，而且大多数数据库获取可能都通过一个很小的索引区间扫描来完成。因此，以上所列分区性能方面可能的主要优点在OLTP系统中表现不出来。分区消除只在大对象全面扫描时才有用，因为通过分区消除，你可以避免对对象的很大部分做全面的扫描。不过，在一个OLTP环境中，本来就不是大对象全面扫描（如果真是如此，则说明肯定存在严重的设计缺陷）。即使对索引进行了分区，就算是真的能在速度上有所提高，通过扫描较小索引所得到的性能提升也是微乎其微的。如果某些查询使用了一个索引，而且它们根本无法消除任何分区，你可能会发现，完成分区之后查询实际上运行得反而更慢了，因为你现在要扫描5、10或20个更小的索引，而不是一个较大的索引。

尽管如此，有分区的OLTP系统确实也有可能得到效率提高。例如，可以用分区来减少竞争，从而提高并发度。可以利用分区将一个表的修改分布到多个物理分区上。并不是只有一个表段和一个索引段，而是可以有10个表分区和20个索引分区。这就像有20个表而不是1个表，相应地，修改期间就能减少对这个共享资源的竞争。

至于并行操作,你可能不希望在一个OLTP系统中执行并行查询。你会慎用并行操作，事实上在一个OLTP系统中，查询已经有以下特点：即索引访问相当快，因此，分区不会让索引访问的速度有太大的高（甚至根本没有任何提高）。这并不是说要绝对避免在 OLTP系统中使用分区；而只是说不要指望通过分区来提供大幅的性能提升。尽管有些情况下分区能够改善查询的性能，但是这些情况在大多数OLTP应用中并不成立。不过在OLTP系统中，你还是可以得到另外两个可能的好处：减轻管理负担以及有更高的可用性。

数据仓库系统
在一个数据仓库/决策支持系统中，分区不仅是一个很强大的管理工具，还可以加快处理的速度。例如，你可能有一个大表，需要在其中执行一个即席查询。你总是按销售定额（sales quarter）执行即席查询，因为每个销售定额包含数十万条记录，而你有数百万条在线记录。因此，你想查询整个数据集中相当小的一部分，但是基于销售定额来索引不太可行。这个索引会指向数十万条记录，以这种方式执行索引区间扫描会很糟糕. 处理许多查询时都要求执行一个全表扫描，但是最后却发现，一方面必须扫描数百万条记录，但另一方面其中大多数记录并不适用于我们的查询。如果使用一种明智的分区机制，就可以按销售定额来聚集数据，这样在查询某个给定销售定额的数据时，就可以只对这个销售定额的数据进行全面扫描。这在所有可能的解决方案中是最佳的选择。
另外，在一个数据仓库/决策支持环境中，会频繁地使用并行查询。因此，诸如并行索引区间扫描或并行快速全面索引扫描等操作不仅很有意义，而且对我们很有好处。我们希望充分地使用所有可用的资源，并行查询就提供了这样的一种途径。因此，在数据仓库环境中，分区就意味着很有可能会加快处理速度。

表分区机制
目前Oracle中有4种对表分区的方法：
区间分区：可以指定应当存储在一起的数据区间。例如，时间戳在2005-06内的所有记录都存储在分区1中，时间戳在2005-06内的所有记录都存储在分区2中，依此类推。这可能是Oracle中最常用的分区机制。
散列分区：是指在一个列（或多个列）上应用一个散列函数，行会按这个散列值放在某个分区中。
列表分区：指定一个离散值集，来确定应当存储在一起的数据。例如，可以指定STATUS列值在（’A’,’M’,’Z’）中的行放在分区1中，STATUS值在（‘D’,’P’,’Q’）中的行放在分区2中，依此类推。
组合分区：这是区间分区和散列分区的一种组合，或者是区间分区与列表分区的组合。通过组合分区，你可以先对某些数据应用区间分区，再在区间中根据散列或列表来选择最后的分区。

区间分区
区间分区表（range partitioned table）。下面的CREATE TABLE语句创建了一个使用RANGE_KEY_COLUMN列的区间分区表。 RANGE_KEY_COLUMN值严格小于2005-01-01的所有数据要放在分区PART_1中，RANGE_KEY_COLUMN值严格小于2006-01-01的所有数据则放在分区PART_2中。不满足这两个条件的所有数据（例如，RANGE_KEY_COLUMN值为2007-01-01的行）将不能插入，因为它们无法映射到任何分区：

sys@JINGYONG> create table range_example
  2  (range_key_column date,
  3  data varchar2(20)
  4  )
  5  partition by range(range_key_column)
  6  (partition part_1 values less than
  7  (to_date('2005-01-01','yyyy-mm-dd')),
  8  partition part_2 values less than
  9  (to_date('2006-01-01','yyyy-mm-dd'))
 10  )
 11  ;

表已创建。

sys@JINGYONG> insert into range_example values(to_date('2004-11-01','yyyy-mm-dd'
),'2004');

已创建 1 行。

sys@JINGYONG> insert into range_example values(to_date('2005-11-01','yyyy-mm-dd'
),'2005');

已创建 1 行。

sys@JINGYONG> insert into range_example values(to_date('2005-01-01','yyyy-mm-dd'
),'2005');

已创建 1 行。

sys@JINGYONG> commit;

提交完成。

sys@JINGYONG> select * from range_example partition(part_1);

RANGE_KEY_COLU DATA
-------------- --------------------
01-11月-04     2004

sys@JINGYONG> select * from range_example partition(part_2);

RANGE_KEY_COLU DATA
-------------- --------------------
01-11月-05     2005
01-1月 -05     2005

如果插入的日期超出上界会怎么样呢？答案是Oracle会产生一个错误：

sys@JINGYONG> insert into range_example values(to_date('2006-01-01','yyyy-mm-dd'
),'2006');
insert into range_example values(to_date('2006-01-01','yyyy-mm-dd'),'2006')
            *
第 1 行出现错误:
ORA-14400: inserted partition key does not map to any partition

假设你想像刚才一样，将2005年和2006年的日期分别聚集到各自的分区，但是另外你还希望将所有其他日期都归入第三个分区。利用区间分区，这可以使用MAXVALUE子句做到这一点，如下所示：

sys@JINGYONG> CREATE TABLE range_example
2 ( range_key_column date ,
3 data varchar2(20)
4 )
5 PARTITION BY RANGE (range_key_column)
6 ( PARTITION part_1 VALUES LESS THAN
7 (to_date('01/01/2005','dd/mm/yyyy')),
8 PARTITION part_2 VALUES LESS THAN
9 (to_date('01/01/2006','dd/mm/yyyy'))
10 PARTITION part_3 VALUES LESS THAN
11 (MAXVALUE)
12 )
13 /
Table created.

现在，向这个表插入一个行时，这一行肯定会放入三个分区中的某一个分区中，而不会再拒绝任何行，因为分区PART_3可以接受不能放在PART_1或PART_2中的任何RANG_KEY_COLUMN值（即使RANGE_KEY_COLUMN值为null，也会插入到这个新分区中）。

散列分区
对一个表执行散列分区（hash partitioning）时，Oracle会对分区键应用一个散列函数，以此确定数据应当放在N个分区中的哪一个分区中。Oracle建议N是2的一个幂（2、4、8、16等），从而得到最佳的总体分布.

散列分区如何工作
散列分区设计为能使数据很好地分布在多个不同设备（磁盘）上，或者只是将数据聚集到更可管理的块（chunk）上，为表选择的散列键应当是惟一的一个列或一组列，或者至少有足够多的相异值，以便行能在多个分区上很好地（均匀地）分布。如果你选择一个只有4个相异值的列，并使用两个分区，那么最后可能把所有行都散列到同一个分区上，这就有悖于分区的最初目标！
在这里，我们将创建一个有两个分区的散列表。在此使用名为HASH_KEY_COLUMN的列作为分区键。Oracle会取这个列中的值，并计算它的散列值，从而确定这一行将存储在哪个分区中：

sys@JINGYONG> create table hash_example
  2  (hash_key_column date,
  3  data varchar2(20)
  4  )
  5  partition by hash(hash_key_column)
  6  (partition part_1 tablespace p1,
  7  partition part_2 tablespace p2
  8  )
  9  ;

表已创建。


sys@JINGYONG> insert into hash_example values(to_date('2004-01-01','yyyy-mm-dd'
),'2004');

已创建 1 行。

sys@JINGYONG> insert into hash_example values(to_date('2007-01-01','yyyy-mm-dd'
),'2007');

已创建 1 行。

sys@JINGYONG> insert into range_example values(to_date('2005-01-01','yyyy-mm-dd'
),'2005');

已创建 1 行。

sys@JINGYONG> insert into range_example values(to_date('2006-01-01','yyyy-mm-dd'
),'2006');

已创建 1 行。

sys@JINGYONG> commit;

sys@JINGYONG> select * from hash_example partition(part_1);

HASH_KEY_COLUM DATA
-------------- --------------------
01-1月 -04     2004
01-1月 -07     2007

sys@JINGYONG> select * from hash_example partition(part_2);

HASH_KEY_COLUM DATA
-------------- --------------------
01-1月 -06     2006
01-1月 -05     2005

前面已经提到过，如果使用散列分区，你将无从控制一行最终会放在哪个分区中。Oracle会应用散列函数，并根据散列的结果来确定行会放在哪里。如果你由于某种原因希望将某个特定行放在分区PART_1中，就不应该使用散列分区，实际上，此时也不能使用散列分区。行会按散列函数的“指示”放在某个分区中，也就是说，散列函数说这一行该放在哪个分区，它就会放在哪个分区中。如果改变散列分区的个数，数据会在所有分区中重新分布（向一个散列分区表增加或删除一个分区时，将导致所有数据都重写，因为现在每一行可能属于一个不同的分区）。
如果你有一个大表,而且你想对它“分而治之”，此时散列分区最有用。你不用管理一个大表，而只是管理8或16个较小的“表 ”。从某种程度上讲，散列分区对于提高可用性也很有用，临时丢掉一个散列分区，就能访问所有余下的分区。也许有些用户会受到影响，但是很有可能很多用户根本不受影响，但是很有可能很多用户根本不受影响。另外，恢复的单位现在也更小了。你不用恢复一个完整的大表；而只需恢复表中的一小部分。

列表分区
列表分区（list partitioning）是Oracle9i Release 1的一个新特性。它提供了这样一种功能，可以根据离散的值列表来指定一行位于哪个分区。如果能根据某个代码来进行分区（如州代码或区代码），这通常很有用。例如，你可能想把Maine州（ME）、New Hampshire州（NH）、Vermont州（VT）和Massachusetts州（MA）中所有人的记录都归至一个分区中，因为这些州相互之间挨得很近，而且你的应用按地理位置来查询数据。类似地，你可能希望将Connecticut州（CT）、Rhode Island州（RI）和New York州（NY）的数据分组在一起。对此不能使用区间分区，因为第一个分区的区间是ME到VT，第二个区间是CT到RI。这两个区间有重叠。而且也不能使用散列分区，因为这样你就无法控制给定行要放到哪个分区中；而要由Oracle提供的内置散列函数来控制。利用列表分区，我们可以很容易地完成这个定制分区机制：

sys@JINGYONG> create table list_example
  2  (state_cd varchar2(2),
  3  data varchar2(20)
  4  )
  5  partition by list(state_cd)
  6  (partition part_1 values('ME','NH','VT','MA'),
  7  partition part_2 values('CT','RI','NY')
  8  )
  9  ;

表已创建。

就像区间分区一样，如果我们想插入列表分区中未指定的一个值，Oracle会向客户应用返回一个合适的错误。换句话说，没有 DEFAULT分区的列表分区表会隐含地施加一个约束（非常像表上的一个检查约束）：

sys@JINGYONG> insert into list_example values('VA','data');
insert into list_example values('VA','data')
            *
第 1 行出现错误:
ORA-14400: inserted partition key does not map to any partition

如果想像前面一样把这个7个州分别聚集到各自的分区中，另外把其余的所有州代码放在第三个分区中（或者，实际上对于所插入的任何其他行，如果STATE_CD列值不是以上7个州代码之一，就要放在第三个分区中），就可以使用VALUES（DEFAULT）子句。在此，我们将修改表，增加这个分区（也可以在CREATE TABLE语句中使用这个子句）：

sys@JINGYONG> alter table list_example add partition part_3 values(default);

表已更改。

sys@JINGYONG> insert into list_example values('VA','data');

已创建 1 行。

sys@JINGYONG> select * from list_example partition(part_3);

ST DATA
-- --------------------
VA data

值列表中未显式列出的所有值都会放到这个（DEFAULT）分区中。关于DEFAULT的使用，有一点要注意：一旦列表分区表有一个 DEFAULT分区，就不能再向这个表中增加更多的分区了：

sys@JINGYONG> alter table list_example
  2  add partition part_4 values('CA','NM');
alter table list_example
            *
第 1 行出现错误:
ORA-14323: cannot add partition when DEFAULT partition exists

此时必须删除DEFAULT分区，然后增加PART_4，最后再加回DEFAULT分区。原因在于，原来DEFAULT分区可以有列表分区键值为CA 或NM的行，但增加PART_4之后，这些行将不再属于DEFAULT分区。

组合分区
最后我们会看到组合分区（composite partitioning）的一些例子，组合分区是区间分区和散列分区的组合，或者是区间分区与列表分区的组合。
在组合分区中，顶层分区机制总是区间分区。第二级分区机制可能是列表分区或散列分区（在Oracle9i Release 1及以前的版本中，只支持散列子分区，而没有列表分区）。有意思的是，使用组合分区时，并没有分区段，而只有子分区段。分区本身并没有段（这就类似于分区表没有段）。数据物理的存储在子分区段上，分区成为一个逻辑容器，或者是一个指向实际子分区的容器。
在下面的例子中，我们将查看一个区间-散列组合分区。在此对区间分区使用的列集不同于散列分区使用的列集。并不是非得如此，这两层分区也可以使用同样的列集：

sys@JINGYONG> create table composite_example
  2    (range_key_column date,
  3     hash_key_column int,
  4     data varchar2(20)
  5    )
  6    partition by range(range_key_column)
  7     subpartition by hash(hash_key_column) subpartitions 2
  8    (
  9      partition part_1
 10      values less than(to_date('2005-01-01','yyyy-mm-dd'))
 11      (subpartition part_1_sub_1,
 12       subpartition part_1_sub_2
 13      ),
 14      partition part_2
 15      values less than(to_date('2006-01-01','yyyy-mm-dd'))
 16      (
 17       subpartition part_2_sub_1,
 18       subpartition part_2_sub_2
 19      )
 20     );

表已创建。

在区间-散列组合分区中，Oracle首先会应用区间分区规则，得出数据属于哪个区间。然后再应用散列函数，来确定数据最后要放在哪个物理分区中

sys@JINGYONG> insert into composite_example values(to_date('2004-12-01','yyyy-mm
-dd'),2004,'2004');

已创建 1 行。

sys@JINGYONG> insert into composite_example values(to_date('2005-01-01','yyyy-mm
-dd'),2005,'2005');

已创建 1 行。



sys@JINGYONG> insert into composite_example values(to_date('2004-01-01','yyyy-mm
-dd'),2006,'2004');

已创建 1 行。

sys@JINGYONG> insert into composite_example values(to_date('2005-11-01','yyyy-mm
-dd'),2006,'2004');

已创建 1 行。

sys@JINGYONG> commit;

提交完成。



sys@JINGYONG> select * from composite_example partition(part_1);

RANGE_KEY_COLU HASH_KEY_COLUMN DATA
-------------- --------------- --------------------
01-12月-04                2004 2004
01-1月 -04                2006 2004

sys@JINGYONG> select * from composite_example subpartition(part_1_sub_1);

RANGE_KEY_COLU HASH_KEY_COLUMN DATA
-------------- --------------- --------------------
01-12月-04                2004 2004

sys@JINGYONG> select * from composite_example subpartition(part_1_sub_2);

RANGE_KEY_COLU HASH_KEY_COLUMN DATA
-------------- --------------- --------------------
01-1月 -04                2006 2004


sys@JINGYONG> select * from composite_example partition(part_2);

RANGE_KEY_COLU HASH_KEY_COLUMN DATA
-------------- --------------- --------------------
01-1月 -05                2005 2005
01-11月-05                2006 2004

sys@JINGYONG> select * from composite_example subpartition(part_2_sub_1);

未选定行

sys@JINGYONG> select * from composite_example subpartition(part_2_sub_2);

RANGE_KEY_COLU HASH_KEY_COLUMN DATA
-------------- --------------- --------------------
01-1月 -05                2005 2005
01-11月-05                2006 2004

因此，利用组合分区，你就能把数据先按区间分解，如果认为某个给定的区间还太大，或者认为有必要做进一步的分区消除，可以再利用散列或列表将其再做分解。有意思的是，每个区间分区不需要有相同数目的子分区；例如，假设你在对一个日期列完成区间分区，以支持数据净化（快速而且容易地删除所有就数据）。在2004年，CODE_KEY_COLUMN值为“奇数”的数据量与 CODE_KEY_COLUMN值为“偶数”的数据量是相等的。但是到了2005年，你发现与奇数吗相关的记录数是偶数吗相关的记录数的两倍，所以你希望对应奇数码有更多的子分区。只需定义更多的子分区，就能相当容易地做到这一点：

sys@JINGYONG> create table composite_range_list_example
  2  (range_key_column date,
  3  code_key_column int,
  4  data varchar2(20)
  5  )
  6  partition by range(range_key_column)
  7  subpartition by list(code_key_column)
  8  (
  9   partition part_1
 10    values less than(to_date('2005-01-01','yyyy-mm-dd'))
 11    (subpartition part_1_sub_1 values(1,3,5,7),
 12     subpartition part_1_sub_2 values(2,4,6,8)
 13    ),
 14   partition part_2
 15    values less than(to_date('2006-01-01','yyyy-mm-dd'))
 16    (subpartition part_2_sub_1 values(1,3),
 17     subpartition part_2_sub_2 values(5,7),
 18     subpartition part_2_sub_3 values(2,4,6,8)
 19    )
 20  );

表已创建。
在此，最后总共有5个分区：分区PART_1有两个子分区，分区PART_2有3个子分区。

sys@JINGYONG> insert into composite_range_list_example values(to_date('2004-01-0
1','yyyy-mm-dd'),1,'data');

已创建 1 行。

sys@JINGYONG> insert into composite_range_list_example values(to_date('2004-11-0
1','yyyy-mm-dd'),2,'data');

已创建 1 行。

sys@JINGYONG> insert into composite_range_list_example values(to_date('2005-11-0
1','yyyy-mm-dd'),2,'data');

已创建 1 行。

sys@JINGYONG> insert into composite_range_list_example values(to_date('2005-11-0
1','yyyy-mm-dd'),1,'data');

已创建 1 行。

sys@JINGYONG> insert into composite_range_list_example values(to_date('2005-11-0
1','yyyy-mm-dd'),7,'data');

已创建 1 行。

sys@JINGYONG> commit;

提交完成。

sys@JINGYONG> select * from composite_range_list_example partition(part_1);

RANGE_KEY_COLU CODE_KEY_COLUMN DATA
-------------- --------------- --------------------
01-1月 -04                   1 data
01-11月-04                   2 data

sys@JINGYONG> select * from composite_range_list_example partition(part_2);

RANGE_KEY_COLU CODE_KEY_COLUMN DATA
-------------- --------------- --------------------
01-11月-05                   1 data
01-11月-05                   7 data
01-11月-05                   2 data

sys@JINGYONG> select * from composite_range_list_example subpartition(part_1_sub
_1);

RANGE_KEY_COLU CODE_KEY_COLUMN DATA
-------------- --------------- --------------------
01-1月 -04                   1 data

sys@JINGYONG> select * from composite_range_list_example subpartition(part_1_sub
_2);

RANGE_KEY_COLU CODE_KEY_COLUMN DATA
-------------- --------------- --------------------
01-11月-04                   2 data

sys@JINGYONG> select * from composite_range_list_example subpartition(part_2_sub
_1);

RANGE_KEY_COLU CODE_KEY_COLUMN DATA
-------------- --------------- --------------------
01-11月-05                   1 data

sys@JINGYONG> select * from composite_range_list_example subpartition(part_2_sub
_2);

RANGE_KEY_COLU CODE_KEY_COLUMN DATA
-------------- --------------- --------------------
01-11月-05                   7 data

sys@JINGYONG> select * from composite_range_list_example subpartition(part_2_sub
_3);

RANGE_KEY_COLU CODE_KEY_COLUMN DATA
-------------- --------------- --------------------
01-11月-05                   2 data

行移动
你可能想知道，在前面所述的各种分区机制中，如果用于确定分区的列有修改会发生什么。需要考虑两种情况：
修改不会导致使用一个不同的分区；行仍属于原来的分区。这在所有情况下都得到支持。
修改会导致行跨分区移动。只有当表启用了行移动时才支持这种情况；否则，会产生一个错误。
这些行为很容易观察。在前面的例子中，我们向RANGE_EXAMPLE表的PART_1插入了三行：

sys@JINGYONG> insert into range_example values(to_date('2004-11-01','yyyy-mm-dd'
),'2004');

已创建 1 行。

sys@JINGYONG> insert into range_example values(to_date('2005-11-01','yyyy-mm-dd'
),'2005');

已创建 1 行。

sys@JINGYONG> insert into range_example values(to_date('2005-01-01','yyyy-mm-dd'
),'2005');

已创建 1 行。

sys@JINGYONG> commit;

提交完成。

sys@JINGYONG> select * from range_example partition(part_1);

RANGE_KEY_COLU DATA
-------------- --------------------
01-11月-04     2004

sys@JINGYONG> select * from range_example partition(part_2);

RANGE_KEY_COLU DATA
-------------- --------------------
01-11月-05     2005
01-1月 -05     2005

取其中一行，并更新其RANGE_KEY_COLUMN值，不过更新后它还能放在PART_1中：
sys@JINGYONG> update range_example
  2  set range_key_column=trunc(range_key_column)
  3  where range_key_column=to_date('2005-01-01','yyyy-mm-dd');

已更新 1 行。

sys@JINGYONG> commit;

提交完成。

sys@JINGYONG> select * from range_example partition(part_2);

RANGE_KEY_COLU DATA
-------------- --------------------
01-11月-05     2005
01-1月 -05     2005

不出所料，这会成功：行仍在分区PART_2中。接下来，再把RANGE_KEY_COLUMN更新为另一个值，但这次更新后的值将导致它属于分区PART_1：

sys@JINGYONG> update range_example
  2  set range_key_column=to_date('2004-12-01','yyyy-mm-dd')
  3  where range_key_column=to_date('2005-01-01','yyyy-mm-dd');
update range_example
       *
第 1 行出现错误:
ORA-14402: updating partition key column would cause a partition change

这会立即产生一个错误，因为我们没有显式地启用行移动。在Oracle8i及以后的版本中，可以在这个表上启用行移动（row movement），以允许从一个分区移动到另一个分区。注意 Oracle 8.0中没有行移动功能；在这个版本中，你必须先删除行，再重新将其插入。不过，要注意这样做有一个小小的副作用；行的ROWID会由于更新而改变：

sys@JINGYONG> select rowid
  2  from range_example
  3  where range_key_column=to_date('2005-01-01','yyyy-mm-dd');

ROWID
------------------
AAAM++AABAAAPAqAAB

sys@JINGYONG> alter table range_example enable row movement;

表已更改。

sys@JINGYONG> update range_example
  2  set range_key_column=to_date('2004-12-01','yyyy-mm-dd')
  3  where range_key_column=to_date('2005-01-01','yyyy-mm-dd');

已更新 1 行。

sys@JINGYONG> select rowid
  2  from range_example
  3  where range_key_column=to_date('2004-12-01','yyyy-mm-dd');

ROWID
------------------
AAAM+9AABAAAPAiAAB

既然知道执行这个更新时行的ROWID会改变，所以要启用行移动，这样才允许更新分区键。
注意在其他一些情况下，ROWID也有可能因为更新而改变。更新IOT的主键可能导致ROWID改变，该行的通用ROWID（UROWID）也会改变。Oracle 10g的FLASHBACK TABLE命令可能改变行的ROWID，此外Oracle 10g的ALTER TABLE SHRINK命令也可能使行的 ROWID改变。

要知道，执行行移动时，实际上在内部就好像先删除了这一行，然后再将其重新插入。这会更新这个表上的索引，删除旧的索引条目，再插入一个新条目。此时会完成DELETE再加一个INSERT的相应物理工作。不过，尽管在此执行了行的物理删除和插入，在 Oracle看来却还是一个更新，因此，不会导致INSERT和DELETE触发器触发，只有UPDATE触发器会触发。另外，由于外键约束可能不允许DELETE的子表也不会触发DELETE触发器。不过，还是要对将完成的额外工作有所准备；行移动的开销比正常的UPDATE昂贵得多。

表分区机制小结
一般来讲，如果将数据按某个（某些）值逻辑聚集，区间分区就很有用。基于时间的数据就是这方面经典的例子，如按“销售定额”、“财政年度”或“月份”分区。在许多情况下，区间分区都能利用分区消除，这包括使用完全相等性和区间（小于、大于、介于…之间等）。

如果不能按自然的区间进行分区，散列分区就很合适。例如，如果必须加载一个表，其中装满与人口普查相关的数据，可能无法找到一个合适的属性来按这个属性完成区间分区。不过，你可能还是想得到分区提供的管理、性能和可用性提升等诸多好处。在此，只需选择惟一的一个列或几乎惟一的一个列集，对其计算散列。这样一来，无论有多少个分区，都能得到均匀的数据分布。使用完全相等性或IN（value,value,…）时，散列分区对象可以利用分区消除，但是使用数据区间时，散列分区则无法利用分区消除。

如果数据中有一列有一组离散值，而且根据应用使用这一列的方式来看，按这一列进行分区很有意义（例如，这样一来，查询中可以轻松地利用分区消除），这种数据就很适合采用列表分区。列表分区的经典例子包括按州或区域代码分区，实际上，一般来讲许多“代码”性属性都很适合应用列表分区。

如果某些数据逻辑上可以进行区间分区，但是得到的区间分区还是太小，不能有效地管理，就可以使用组合分区。可以先应用区间分区，再进一步划分各个区间，按一个散列函数或使用列表来分区。这样就能将I/O请求分布到任何给定大分区中的多个磁盘上。另外，现在可以得到3个层次的分区消除。如果在区间分区键上查询，Oracle就能消除任何不满足条件的区间分区。如果向查询增加散列或列表键，Oracle可以消除该区间中其他的散列或列表分区。如果只是在散列或列表键上查询（而不使用区间分区键），Oracle就只会查询各个区间分区中的这些散列或列表子分区。

如果可以按某个属性自然地对数据完成区间分区，就应该使用区间分区，而不是散列分区或列表分区。散列和列表分区能提供分区的许多突出优点，但是在分区消除方面都不如区间分区有用。如果所得到的区间分区太大，不能很好地管理；或者如果你想使用所有PDML功能或对一个区间分区使用并行索引扫描，则建议在区间分区中再使用散列或列表分区。

参考:Oracle.Database.Architecture.9i.and.10g.Programming.Techniques.and.Solutions

发表评论 取消回复

发表评论取消回复