Oracle 分区表(Partition)

在看示例库的时候，发现有一些表的DDL不仅有Column部分，还有很多Partition部分，不理解什么意思。于是搜索了一下，发现原来是Oracle的一个Feature。 以下是参考资料：（可以结合示例库中的SH下的一些表学习）

Oracle提供了分区技术以支持VLDB(Very Large DataBase)。分区表通过对分区列的判断，把分区列不同的记录，放到不同的分区中。分区完全对应用透明。

Oracle的分区表可以包括多个分区，每个分区都是一个独立的段（SEGMENT），可以存放到不同的表空间中。查询时可以通过查询表来访问各个分区中的数据，也可以通过在查询时直接指定分区的方法来进行查询。

分区提供以下优点：

由于将数据分散到各个分区中，减少了数据损坏的可能性；

可以对单独的分区进行备份和恢复；

可以将分区映射到不同的物理磁盘上，来分散IO；

提高可管理性、可用性和性能。

Oracle提供了以下几种分区类型：

范围分区（range）；

哈希分区（hash）；

列表分区（list）；

范围－哈希复合分区（range-hash）；

范围－列表复合分区（range-list）。

索引也可以进行分区，分区索引有两种类型：global和local。对于local索引，每一个表分区对应一个索引分区，当表的分区发生变化时， 索引的维护由Oracle自动进行。对于global索引，可以选择是否分区，而且索引的分区可以不与表分区相对应。当对分区进行维护操作时，通常会导致 全局索引的INVALDED，必须在执行完操作后REBUILD。Oracle9i提供了UPDATE GLOBAL INDEXES语句，可以使在进行分区维护的同时重建全局索引。

全局索引可以包含多个分区的值 局部索引比全局索引容易管理,而全局索引比较快
注意:不能为散列分区 或者 子分区创建全局索引

如果表中预期的数据量较大，通常都需要考虑使用分区表，确定使用分区表后，还要确定什么类型的分区（range partition、hash partition、list partition等）、分区区间大小等。分区的创建最好与程序有某种默契，偶曾经创建分区表，按自然月份定义分区的，但程序却在查询时默认的开始时间与 结束时间是：当前日期-30至当前日期，比如当天是9.18号，那查询条件被产生为8.18-9.18，结果分区后并不没有大幅提高性能，后来对程序的查 询日期做了调整，按自然月查询，系统的负载小了很多。

从Oracle8.0开始支持表分区（MSSQL2005开始支持表分区）。

Oracle9i 分区能够提高许多应用程序的可管理性、性能与可用性。分区可以将表、索引及索引编排表进一步划分，从而可以更精细地对这些数据库对象进行管理和访问。 Oracle 提供了种类繁多的分区方案以满足所有的业务需要。另外，由于在 SQL 语句中是完全透明的，所以分区可以用于几乎所有的应用程序。

分区表允许将数据分成被称为分区甚至子分区的更小的更好管理的块。索引也可以这么分区。每个分区可以被单独管理，可以不依赖于其他分区而单独发挥作用，因此提供了一个更有利于可用性和性能的结构。

分区可以提高可管理性、性能与可用性，从而给各种各样的应用程序带来极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外，分区 还能够在很大程度上简化日常管理任务。分区还使数据库设计人员和管理员能够解决尖端应用程序带来的最难的问题。分区是建立上亿万字节数据系统或需要极高可 用性系统的关键工具。

在多CPU配置环境下，如果打算使用并行执行，则分区提供了另一种并行的方法。通过给表或索引的不同分区分配不同的并行执行服务器，就可以并行执行对分区表和分区索引的操作。

表或索引的分区和子分区都共享相同的逻辑属性。例如表的所有分区或子分区共享相同的列和约束定义，一个索引的分区或子分区共享相同的索引选项。然而它们可以具有不同的物理属性如表空间。

尽管不需要将表或索引的每个分区或子分区放在不同的表空间，但这样做更好。将分区存储到不同的表空间能够

l 减少数据在多个分区中冲突的可能性

l 可以单独备份和恢复每个分区

l 控制分区与磁盘驱动器之间的映射对平衡I/O 负载是重要的

l 改善可管理性可用性和性能

分区操作对现存的应用和运行在分区表上的标准DML 语句来说是透明的。但是可以通过在DML 中使用分区扩展表或索引的名字来对应用编程，使其利用分区的优点。

可以使用SQL*Loader、Import 和Export 工具来装载或卸载分区表中的数据。这些工具都是支持分区和子分区的。

分区的方法Oracle9i 提供了如下5种分区方法：

l 范围分区Range

l 散列分区Hash

l 列表分区List

l 组合范围-散列分区Range-Hash

l 组合范围-列表分区Range-List

可对索引和表分区。全局索引只能按范围分区，但可以将其定义在任何类型的分区或非分区表上。通常全局索引比局部索引需要更多的维护。

一般组建局部索引，以便反映其基础表的结构。它与基础表是等同分区的，即它与基础

表在同样的列上分区，创建同样数量的分区或子分区，设置与基础表相对应的同样的分区边界。对局部索引而言，当维护活动影响分区时，会自动维护索引分区。这保证了索引与基础表之间的等同分区。

关于范围分区Range：

要想将行映射到基于列值范围的分区，就使用范围分区方法。当数据可以被划分成逻辑范围时如年度中的月份，这种类型的分区就有用了。当数据在整个范围中能被均等地划分时性能最好。如果靠范围的分区会由于不均等的划分而导致分区在大小上明显不同时，就需要考虑其他的分区方法。

关于散列分区Hash：

如果数据不那么容易进行范围分区，但为了性能和管理的原因又想分区时，就使用散列分区方法。散列分区提供了一种在指定数量的分区中均等地划分数据的 方法。基于分区键的散列值将行映射到分区中。创建和使用散列分区会给你提供了一种很灵活的放置数据的方法，因为你可以通过在I/O 驱动器之间播撒(摘掉)这些均等定量的分区，来影响可用性和性能。

关于列表分区List：

当你需要明确地控制如何将行映射到分区时，就使用列表分区方法。可以在每个分区的描述中为该分区列指定一列离散值，这不同于范围分区，在那里一个范 围与一个分区相关，这也不同于散列分区，在那里用户不能控制如何将行映射到分区。列表分区方法是特意为遵从离散值的模块化数据划分而设计的。范围分区或散 列分区不那么容易做到这一点。进一步说列表分区可以非常自然地将无序的和不相关的数据集进行分组和组织到一起。

与范围分区和散列分区所不同，列表分区不支持多列分区。如果要将表按列分区，那么分区键就只能由表的一个单独的列组成，然而可以用范围分区或散列分区方法进行分区的所有的列,都可以用列表分区方法进行分区。

关于组合范围-散列分区：

范围和散列技术的组合，首先对表进行范围分区，然后用散列技术对每个范围分区再次分区。给定的范围分区的所有子分区加在一起表示数据的逻辑子集。

关于组合范围-列表分区：

范围和列表技术的组合，首先对表进行范围分区，然后用列表技术对每个范围分区再次分区。与组合范围-散列分区不同的是，每个子分区的所有内容表示数据的逻辑子集，由适当的范围和列表分区设置来描述。

创建或更改分区表时可以指定行移动子句，即ENABLE ROW MOVEMENT 或者DISABLE ROW MOVEMENT ，当其键被更改时，该子句启用或停用将行迁移到一个新的分区。默认值为DISABLE ROW MOVEMENT。本产品(项目)使用ENABLE ROW MOVEMENT子句。

分区技术能够提高数据库的可管理性：

使用分区技术，维护操作可集中于表的特定部分。例如，数据库管理员可以只对表的一部分做备份，而不必对整个表做备份。对整个数据库对象的维护操作，可以在每个分区的基础上进行，从而将维护工作分解成更容易管理的小块。

分区技术提高可管理性的一个典型用法是支持数据仓库中的‘滚动视窗’加载进程。假设数据库管理员每周向表中加载新数据。该表可以是范围分区，以便每 个分区包含一周的数据。加载进程只是简单地添加新的分区。添加一个新分区的操作比修改整个表效率高很多，因为数据库管理员不需要修改任何其他分区。从分区 后的表中去除数据也是一样。你只要用一个很简便快捷的数据字典操作删掉一个分区，而不必发出使用大量资源和调动所有要删除的数据的 ‘DELETE’ 命令。

分区技术能够提高数据库的性能:

由于减少了所检查或操作的数据数量，同时允许并行执行，Oracle9i 的分区功能提供了性能上的优势。这些性能包括：

l 分区修整：分区修整是用分区技术提高性能的最简单最有价值的手段。分区修整常常能够将查询性能提高几个数量级。例如，假定应用程序中有包含定单历史记录的 定单表，该表用周进行了分区。查询一周的定单只需访问该定单表的一个分区。如果该定单表包含两年的历史记录，这个查询只需要访问一个而不是一百零四个分 区。该查询的执行速度因为分区修整而有可能快一百倍。分区修整能与所有其他 Oracle 性能特性协作。Oracle 公司将把分区修整技术与索引技术、连结技术和并行访问方法一起联合使用。

l 分区智能联接：分区功能可以通过称为分区智能联接的技术提高多表联接的性能。当两个表要联接在一起，而且每个表都用联接关键字来分区时，就可以使用分区智 能联接。分区智能联接将大型联接分解成较小的发生在各个分区间的联接，从而用较少的时间完成全部联接。这就给串行和并行的执行都能带来显著的性能改善。

l 更新和删除的并行执行：分区功能能够无限地并行执行 UPDATE、DELETE 与 MERGE 语句。当访问分区或未分区的数据库对象时Oracle 将并行处理 SELECT 与 INSERT 语句。当不使用位图索引时，也可以对分区或未分区的数据库对象并行处理 UPDATE、DELETE 和 MERGE 语句。为了对有位图索引的对象并行处理那些操作，目标表必须先分区。这些 SQL 语句的并行执行可以大大提高性能，特别是提高 UPDATE 与 DELETE 或 MERGE 操作涉及大量数据时的性能。

分区技术提高可用性：

分区的数据库对象具有分区独立性。该分区独立性特点可能是高可用性战略的一个重要部分，例如，如果分区表的分区不能用，但该表的所有其他分区仍然保 持在线并可用。那么这个应用程序可以继续针对该分区表执行查询和事务处理，只要不是访问那个不可用的分区，数据库操作仍然能够成功运行。 数据库管理员可以指定各分区存放在不同的表空间里，从而让管理员独立于其它表分区针对每个分区进行备份与恢复操作。 还有，分区功能可以减少计划停机时间。性能由于分区功能得到了改善，使数据库管理员在相对较小的批处理窗口完成大型数据库对象的维护工作。

Oracle最大允许存在多少个分区呢?

这使最为常用也是最简单方法，具体例子如下： 字串5 　　
    create table emp
　　(empno number(4),
　　ename varchar2(30),
　　sal number)
　　partition by range(empno)
　　(partition e1 values less than (1000) tablespace emp1,
　　partition e2 values less than (2000) tablespace emp2,
　　partition e3 values less than (maxvalue) tablespace emp3);
　　
　　insert into emp values (100,'Tom',1000);
　　insert into emp values (500,'Peter',2000);
　　insert into emp values (1000,'Scott',3000);
　　insert into emp values (1999,'Bill',4000);
　　insert into emp values (5000,'Gates',6000);
　　commit; 字串6

 

　　从emp表中选择全部纪录如下： 字串8

　　SQL> select * from emp;
　　
　　EMPNO ENAME SAL
　　---------- ------------------------------ ----------
　　100 Tom 1000
　　500 Peter 2000
　　1000 Scott 3000
　　1999 Bill 4000
　　5000 Gates 6000

 

　　还可以按照分区进行选择： 字串6

　　SQL> select * from emp partition (e1);
　　EMPNO ENAME SAL
　　---------- ------------------------------ ----------
　　100 Tom 1000
　　500 Peter 2000
　　
　　SQL> select * from emp partition (e2)
　　EMPNO ENAME SAL
　　---------- ------------------------------ ----------
　　1000 Scott 3000
　　1999 Bill 4000
　　
　　SQL> select * from emp partition (e3)
　　EMPNO ENAME SAL
　　---------- ------------------------------ ----------
　　5000 Gates 6000

 

　　使用了分区，还可以单独针对指定分区进行truncate操作： 字串7

　　alter table emp truncate partition e2;

 

　　2 对表进行多列范围分区：

 

　　多列范围分区主要是基于表中多个列值范围对数据进行分区，例如： 字串1

　　drop table emp;
　　create table emp
　　(empno number(4),
　　ename varchar2(30),
　　sal number,
　　day integer not null,
　　month integer not null)
　　partition by range(month,day)
　　(partition e1 values less than (5,1) tablespace emp1,
　　partition e2 values less than (10,2) tablespace emp2,
　　partition e3 values less than (maxvalue,maxvalue) tablespace emp3);
　　
　　SQL> insert into emp values (100,'Tom',1000,10,6);
　　SQL> insert into emp values (200,'Peter',2000,3,1);
　　SQL> insert into emp values (300,'Jane',3000,23,11);

第二种 Hash分区： 字串3

　　hash分区最主要机制是根据hash算法来计算具体某条纪录应该插入到哪个分区中,hash算法中最重要是hash函数，Oracle中如果你要使用hash分区，只需指定分区数量即可。建议分区数量采用2n次方，这样可以使得各个分区间数据分布更加均匀。

 

　　具体例子如下： 字串1

　　drop table emp;
　　create table emp (
　　empno number(4),
　　ename varchar2(30),
　　sal number)
　　partition by hash (empno)
　　partitions 8
　　store in (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8); 字串6

 

　　怎么样？很方便吧！

 

　　第三种 复合分区：

 

　　这是一种将前两种分区综合在一起使用方法，例如：

 

　　drop table emp;
　　create table emp (
　　empno number(4),
　　ename varchar2(30),
　　hiredate date)
　　partition by range (hiredate)
　　subpartition by hash (empno)
　　subpartitions 2
　　(partition e1 values less than (to_date('20020501','YYYYMMDD')),
　　partition e2 values less than (to_date('20021001','YYYYMMDD')),
　　partition e3 values less than (maxvalue)); 字串9

 

　　上面例子中将雇员表先按照雇佣时间hiredate进行了范围分区，然后再把每个分区分为两个子hash分区。例子中一共将产生6个分区。

 

　　第四种 列表分区： 字串9

　　这是Oracle 9i新特性，有了这种分区使得我们可以方便按照值来将数据分为更小片断。 字串1

　　例如：

 

　　drop table emp;
　　create table emp (
　　empno number(4),
　　ename varchar2(30),
　　location varchar2(30))
　　partition by list (location)
　　(partition e1 values ('北京'),
　　partition e2 values ('上海','天津','重庆'),
　　partition e3 values ('广东','福建'));

 

　　这里说明一下，列表分区不能有maxvalue，当你试图insert列表中不存在值时候，Oracle会拒绝这条纪录（ORA-14400）。

 

　　怎么样？看出列表分区很有用了吧？上面列出了Oracle9i中使用分区四种方法，其中例子很简单，真正工作中具体使用那种分区方法要参考你具体需求。

 

　　注：例子中使用e1,e2,e3,e4等是分区名称，emp1,emp2,emp3,emp4等是表空间名称