分区

分区：将一个表按照某种方式进行数据的重组，构成的多个数据段称为表的分区。

优势：

  • 增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用
  • 维护方便：如果表的某个分区出现故障，需要修复数据，只需要修复该分区即可
  • 均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能
  • 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度（当表中没有索引时才能看出效果。如果表上有索引，建分区对性能改善并不明显，甚至对于全局索引建分区还可能会影响性能）

创建分区语句：

  1. create table t_part(
  2.     object_id int,
  3.     object_name varchar2(1000)
  4. )
  5. partition by range(object_id)  -- 对表的object_id做分区，做的范围分区
  6. (
  7.     partition p1 values less than(10000),
  8.     partition p2 values less than(20000),
  9.     partition p2 values less than(30000)，
  10.     partition p4 values less than(40000),
  11.     partition pm values less than(maxvalue)
  12. )

分区可以方便数据管理，降低系统资源开销。例如对于OLAP系统，delete操作的耗时几乎是场灾难，但是做了分区之后，可以直接对这个分区进行truncate或者drop。

分区的类型

Oracle提供了三种分区：

  • 范围分区（range partition）
  • 哈希分区（hash partition）
  • 列表分区（list partition）

范围分区

范围分区在数据的过期化处理中的应用：在海量数据的数据库设计中，可能需要提前考虑数据库中数据存储的时间问题，或者叫做数据的过期化问题，它的意思是，由于数据量太大，在数据库中只保留特定时长的数据，比如1年，1年前的数据就需要做过期化（归档化）处理。

可以在创建分区时指定这个分区所在的表空间，之后使用传递表空间的方式将过期的数据所在表空间移到离线数据库。

  1. create table sale_data
  2. (
  3.     sale_id number(5),
  4.     salesman_name varchar2(30),
  5.     sales_amount number(10),
  6.     sales_date date
  7. )
  8. partition by range(sales_date)
  9. (
  10.     partition sales_2009_1 values less than(to_date('01/02/2009', 'DD/MM/YYYY')) tablespace ts_sales_2009_01,  -- 2009年1月数据
  11.     partition sales_2009_2 values less than(to_date('01/03/2009', 'DD/MM/YYYY')) tablespace ts_sales_2009_02, 
  12.     partition sales_2009_3 values less than(to_date('01/04/2009', 'DD/MM/YYYY')) tablespace ts_sales_2009_03, 
  13.     -- .....
  14.     partition sales_2010_1 values less than(to_date('01/02/2010', 'DD/MM/YYYY')) tablespace ts_sales_2010_01
  15. );

为分区表建立分区索引：

  1. create index ind_sale_data on sale_data(sales_date)
  2. local
  3. (
  4.     partition sales_2009_1 tablespace ts_sales_2009_1,
  5.     partition sales_2009_2 tablespace ts_sales_2009_2,
  6.     partition sales_2009_3 tablespace ts_sales_2009_3,
  7.     --....
  8.     partition sales_2010_1 tablespace ts_sales_2010_1
  9. );

查看表的每个分区、索引的每个分区的段对象：

  1. select * from user_segments where segment_name in ('SALE_DATA', 'IND_SALE_DATA');

如果以传递表空间方式导出表空间，oracle不能直接导出分区所在的表空间。需要满足以下条件：

  • 源和目标数据库必须使用相同的字符集和国家字符集
  • 目标数据库不能已经包含同名的表空间
  • 表空间上的对象必须是自包含的

自包含的意思是，对需要做传递表空间中的对象，不会引用到表空间之外的对象。比如：

  • 索引在这个表空间中，但是它引用的表在需要传递的表空间以外的其他表空间上
  • 分区表的部分分区在需要传递的表空间以外的其他表空间上
  • 完整性约束的参考对象在需要传递表空间以外的其他表空间上
  • 表中包含的LOB对象存储在需要传递的表空间以外的其他表空间上

对于上面创建的表来说，每个表空间只存储一个表分区，就不能对单个表空间进行表空间传 递操作，因为它上面存储的对象不是自包含的。可以通过以下方式检查表空间是否符合表空间传递的方式导出：

  1. exec dbms_tts.transport_set_check('TS_SALES_2009_1', true);
  2. select * from transport_set_violations;

基于以上原因，不能直接对分区的表空间进行移植，可以使用分区交换来解决问题：

  • 创建一个临时表
  • 在临时表上创建索引
  • 将需要导出的分区数据和分区索引与临时表进行分区交换
  • 将临时表和索引所在的表空间导出

分区交换：（分区交换在oracle底层只是修改下字典表的相关属性，速度非常快）

  1. alter table sale_data exchange partition sales_2009_1 with table tmp_sale_data_2009_1 including indexes with validation;

然后进行表空间传递：

  • 将表空间设置为read only，将表空间数据文件复制到离线数据库中
  • 使用expdp传递表空间方式导出表空间的元数据，在离线数据库中使用impdp导入表空间元数据

此时在离线数据库上看到的依然是创建的临时表tmp_sale_data_2009_1，可以在离线数据库上再做一次逆向的分区交换。

列表分区

列出具体的值进行分区，用途和范围分区一样。

例如按地区进行分区：

  1. create table sales_list
  2. (
  3.     salesman_id number(15),
  4.     saleman_name varchar2(30),
  5.     sales_state varchar2(20),
  6.     sales_amount number(10),
  7.     sales_date date
  8. )
  9. partition by list(sales_state)
  10. (
  11.     partition sales_west values('Hawaii'),
  12.     partition sales_east values('New York', 'Virginia', 'Florida'),
  13.     partition sales_central values('Texas', 'Illinois')
  14. );

哈希分区

哈希分区是通过对需要分区的字段数据做一种hash函数运算，从而使数据比较均匀的分布在各个分区上面的分区形式。

通常来说，哈希分区适合于在各个分区中数据分布要求均匀的情况下使用，比如分区表空间存放于不同的物理介质上，而这些介质的容量相近，这时候使用哈希分区就能够很好的解决这个问题。

哈希分区的目的是数据的均匀化。

  1. create table t_hash
  2. partition by hash(object_id) partitions 8  -- 创建8个分区
  3. as select * from dba_objects;

此时分出来的8个分区的数据大小大致相同：

  1. select partition_name, count(*) from user_extents where segment_name='T_HASH' group by partition_name;

哈希分区无法drop、split、merge，只能合并：

  1. alter table xxxx coalesce partition;

但是可以通过truncate方式来清除哈希分区的数据：

  1. alter table xxx truncate partition sys_p48;

Hash分区的目的是使每个分区的数据分布比较均匀，这里面有一个前提条件，就是被 hash 的列上的数据应该重复率比较低，否则可能达不到预期的目的，甚至会出现数据在分区中分布严重不均匀的情况。

组合分区

适当的进行分区组合，可以最大限度的发挥分区的优势。

Oracle 10g 提供了2种分区组合：

  • Range-hash：主分区是范围分区，在范围分区的基础上再进行Hash分区
  • Range-list：主分区是范围分区，在范围分区的基础上再进行列表分区

oracle 11g增加了4种组合：

  • Range-Range
  • List-Range
  • List-Hash
  • List-List

示例：Range-Hash组合分区

  1. create table t_range_hash
  2. (
  3.     id int,
  4.     name varchar2(100)
  5. )
  6. partition by range(id)
  7. subpartition by hash(name)
  8. (
  9.     partition p1 values less than(5)
  10.     (
  11.         subpartition sp1,
  12.         subpartition sp2
  13.     ),
  14.     partition p2 values less than(10)
  15.     (
  16.         subpartition sp3,
  17.         subpartition sp4
  18.     ),
  19.     partition p3 values less than(15)
  20. );