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

Leave a Reply

Your email address will not be published.

Proudly powered by WordPress | Indrajeet by Sus Hill.