【收藏】表分区
ORACLE 表分区
表分区的好处和事处理
表分区描述
表分区（partition）:表分区技术是在超大型数据库(VLDB)中将大表及其索引通过分区（patition）的形式分割为若干较小、可管理的小块，并且每一分区可进一步划分为更小的子分区（sub partition）。而这种分区对于应用来说是透明的。Oracle的表分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外,分区还可以极大简化常见的管理任务，分区是构建千兆字节数据系统或超高可用性系统的关键工具。
分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。每个分区都是一个独立的段（SEGMENT），可以存放到相同（不同）的表空间中。从数据库管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但是，从应用程序的角度来看，分区后的表与非分区表完全相同，使用 SQL DML 命令访问分区后的表时，无需任何修改。(对于高效率查询是有影响,主要差别是对某一分区数据时行查询时和对整体数据进行查询)
表分区的好处
通过对表进行分区，可以获得以下的好处：
1) 增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
2) 维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；
3) 均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能；
4) 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
分区表事务操作的时候，指定了分区，系统在执行的时候则只操作该分区的记录，提高了数据处理的速度。不要指定分区直接操作数据也是可以的。在分区表上建索引及多索引的使用和非分区表一样。此外，因为在维护分区的时候可能对分区的索引会产生一定的影响，可能需要在维护之后重建索引，相关内容请参考分区表索引部分的文档
缺点
分区表相关：已经存在的表没有方法可以直接转化为分区表。不过 Oracle 提供了在线重定义表的功能。
什么时候使用分区表
1、表的大小超过2GB。
2、表中包含历史数据，新的数据被增加都新的分区中。
各版本表分区的特性区别
下面的表列出了每个版本中包括的分区特性：
|
Oracle数据库版本 |
分区特性 |
|
8.0.5 |
引入范围分区 |
|
8i |
引入哈希和混合范围-哈希分区 |
|
9i |
引入列表分区，混合范围-列表分区 |
|
10g |
引入范围，列表和索引组织表的哈希分区，还引入了其他混合分区类型(range-hash，range-list) |
|
11g |
引用分区、间隔分区、虚拟列分区以及扩展的组合分区 引入分区扩展： l 间隔分区 l REF分区 l 基于列的虚拟分区 l 分区顾问 (range-range,list-range,list-list,list-hash) |
分区别的类型
ü 范围分区：将表按某一字段或若干个字段的取值范围分区。
ü hash分区：将表按某一字段的值均匀地分布到若干个指定的分区。
ü 复合分区：结合了前面两种分区类型的优点，首先通过值范围将表进行分区，然后以hash模式将数据进一步均匀分配至物理存储位置。
ü 列表分区：数据分布是通过分区键的一串值定义的，这对不连续的列表非常有用，如：区域、状态等。(9I 以上支持)
ü 混合分区：有两个数据分布办法用于创建混合分区，表首先通过第一个数据分布办法进行初始化分区，然后每个分区再通过第二个办法分成子分区，下面列出了可用的混合分区类型：
范围-哈希，范围-列表，范围-范围，列表-范围，列表-列表，列表-哈希。
在10g中索引组织表（表的索引和数据存储在一起）支持通过范围、列表或哈希进行分区，然而，混合分区在索引组织表上不受支持。
ü 间隔分区：在11g中才引入，间隔分区是对范围分区的扩展，为等距范围分区提供了自动化，分区创建为元数据，只有分区开始部分是不变的，附加的段是当数据抵达时才分配的，附加分区和本地索引是自动创建的。
|
SQL>CREATE TABLE SALES_PART (TIME_ID NUMBER, REGION_ID NUMBER, ORDER_ID NUMBER, ORDER_DATE DATE, SALES_QTY NUMBER(10,2), SALES_AMOUNT NUMBER(12,2) ) PARTITION BY RANGE (ORDER_DATE) INTERVAL (NUMTOYMINTERVAL(1,'month') (PARTITION p_first VALUES LESS THAN ('01-JAN-2006'); |
numtoyminterval函数转换一个数字为间隔一年至一个月的文字（年或月），间隔分区表可以有传统的范围和自动间隔部分，范围分区表可以通过在ALTER TABLE命令中使用SET INTERVAL选项被扩展为间隔分区表。
ü REF分区：这个分区方案假设关联表能从相同的分区策略中受益，子表通过PK-FK（主键-外键）关系继承主表的策略，它不需要分区键存储在子表中，通过PARTITION BY REFERENCE关键字指定，子表继承主表的分区策略。
ü 基于列的虚拟分区：在Oracle以前的版本中，只有分区键物理存在于表中才能对表进行分区，在11g中引入一个新的特性“虚拟列”移除了这个限制，允许分区键通过使用一个或多个表的列的表达式进行定义，虚拟列仅作为元数据存储。如：向表ACCOUNTS添加一个虚拟列：
|
SQL>CREATE TABLE ACCOUNTS (acc_no number(10) not null, acc_name varchar2(50) not null, acc_loc varchar2(5), acc_branch number(2) generated always as (to_number(substr(to_char(acc_no),1,2))); 使用虚拟列作为分区键： SQL>CREATE TABLE accounts (acc_no number(10) not null, acc_name varchar2(50) not null, acc_loc varchar2(5), acc_branch number(2) generated always as (to_number(substr(to_char(acc_no),1,2))) partition by list (acc_branch); |
ü 分区顾问
Oracle 11g也提供了分区顾问，它支持生成分区建议，与10g中为物理视图、物理视图日志和索引提供建议类似，实际上，分区顾问是Oracle 11g中SQL访问顾问的一部分，这个顾问帮助生成建议，它将预先收集实施分区后的性能，它还生成创建高效分区的脚本，可以手动通过SQL*plus或通过企业管理器队列提交给Oracle。
分区表的创建
范围分区
将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期。
当使用范围分区时，请考虑以下几个规则：
1、每一个分区都必须有一个VALUES LESS THEN子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。
2、所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。
3、在最高的分区中，MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，也可以理解为高于任何分区中指定的VALUE LESS THEN的值，同时包括空值。
范围分区就是对数据表中的某个值的范围进行分区，根据某个值的范围，决定将该数据存储在哪个分区上。如根据序号分区，根据业务记录的创建日期进行分区等。
根据序号分区建表：
|
SQL> create table
dinya_test |
根据交易的序号，交易ID在三千万以下的记录将存储在第一个表空间dinya_space01中，分区名为:par_01，在三千万到六千万之间的记录存储在第二个表空间：dinya_space02中，分区名为：par_02，而交易ID在六千万以上的记录存储在第三个表空间dinya_space03中，分区名为par_03.
根据交易日期分区建表：
|
SQL> create table
dinya_test |
这样我们就分别建了以交易序号和交易日期来分区的分区表。每次插入数据的时候，系统将根据指定的字段的值来自动将记录存储到制定的分区（表空间）中。当然，我们还可以根据需求，使用两个字段的范围分布来分区，如partition by range ( transaction_id ,transaction_date), 分区条件中的值也做相应的改变。
Hash分区（散列分区）
是在列值上使用散列算法，以确定将行放入哪个分区中。当列的值没有合适的条件时，建议使用散列分区。
散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，使得这些分区大小一致。散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，使得这些分区大小一致。如将物料交易表的数据根据交易ID散列地存放在指定的三个表空间中：
|
SQL> create table
dinya_test |
hash分区最主要的机制是根据hash算法来计算具体某条纪录应该插入到哪个分区中,hash算法中最重要的是hash函数，Oracle中如果你要使用hash分区，只需指定分区的数量即可。建议分区的数量采用2的n次方，这样可以使得各个分区间数据分布更加均匀.
复合分区
有时候我们需要根据范围分区后，每个分区内的数据再散列地分布在几个表空间中，这样我们就要使用复合分区。复合分区是先使用范围分区，然后在每个分区内再使用散列分区的一种分区方法，如将物料交易的记录按时间分区，然后每个分区中的数据分三个子分区，将数据散列地存储在三个指定的表空间中：
|
SQL> create table
dinya_test |
该例中，先是根据交易日期进行范围分区，然后根据交易的ID将记录散列地存储在三个表空间中。
列表分区(9I 以上)
该分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区。
CREATE TABLE PROBLEM_TICKETS
(
PROBLEM_ID NUMBER(7) NOT NULL PRIMARY KEY,
DESCRIPTION VARCHAR2(2000),
CUSTOMER_ID NUMBER(7) NOT NULL,
DATE_ENTERED DATE NOT NULL,
STATUS VARCHAR2(20)
)
PARTITION BY LIST (STATUS)
(
PARTITION PROB_ACTIVE VALUES ('ACTIVE') TABLESPACE PROB_TS01,
PARTITION PROB_INACTIVE VALUES ('INACTIVE') TABLESPACE PROB_TS02
）
组合范围散列分区
这种分区是基于范围分区和列表分区，表首先按某列进行范围分区，然后再按某列进行列表分区，分区之中的分区被称为子分区。
CREATE TABLE SALES
(
PRODUCT_ID VARCHAR2(5),
SALES_DATE DATE,
SALES_COST NUMBER(10),
STATUS VARCHAR2(20)
)
PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS)
(
PARTITION P1 VALUES LESS THAN(TO_DATE('2003-01-01','YYYY-MM-DD'))TABLESPACE rptfact2009
(
SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009,
SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009
),
PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE rptfact2009
(
SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009,
SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009
)
)
复合范围散列分区：
这种分区是基于范围分区和散列分区，表首先按某列进行范围分区，然后再按某列进行散列分区。
create table dinya_test
(
transaction_id number primary key,
item_id number(8) not null,
item_descriptionvarchar2(300),
transaction_date date
)
partition by range(transaction_date)subpartition by hash(transaction_id) subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03)
(
partition part_01 values less than(to_date(‘2006-01-01’,’yyyy-mm-dd’)),
partition part_02 values less than(to_date(‘2010-01-01’,’yyyy-mm-dd’)),
partition part_03 values less than(maxvalue)
);
扩展的组合分区
使用组合分区 — Oracle8i 数据库中引入的方案 — 您可以在分区中创建子分区，从而进一步细分表。但在该情况下，您只能通过散列子分区对已按范围分区的表进行再次分区。Oracle9i 中对组合分区进行了扩展，使之包括范围-列表子分区。
这些方案满足了大多数环境（但并非所有环境）的需要。例如，假设您有一个名为 SALES 的包含多列的表，其中包括两个特殊的列，它们是分区的候选列：state_code，它存储一个表示销售状态的两位代码，表面上用于计算销售税；以及 product_code，一个用于识别销售记录所销售的产品的三位数字。用户通过对两列进行同等的筛选对该表进行查询，存档要求也基于这两列。应用分区决策的原则时，您会发现这两列都是合适的分区键候选者。
在 Oracle 数据库 11g 中，您可以相当轻松地解决此问题。在该版本中，并不局限于范围-散列和范围-列表组合分区。您的选择实际上没有任何限制；您可以使用任何组合创建组合分区。
在这个示例中，您可以决定根据 product_code（因为该列具有更多离散的值）对表进行列表分区，然后根据 state_code 再次进行列表分区。下面的代码示例显示了如何实现该操作：
create table sales
(
sales_id number,
product_code number,
state_codevarchar2(2)
)
partition by list (product_code)
subpartition by list (state_code)
(
partition p101 values (101)
(
subpartition p101_ct values ('CT'),
subpartition p101_ny values ('NY'),
subpartition p101_def values (default)
),
partition p201 values (201)
(
subpartition p201_ct values ('CT'),
subpartition p201_ny values ('NY'),
subpartition p201_def values (default)
)
)
选择并不仅限于此处显示的方法。您还可以创建列表-范围组合分区。在上面的示例中，假设产品代码不是离散的，而是在一个范围内。您将希望根据 state_code 进行列表分区，然后再根据 product_code 划分子分区。下面是实现该操作的代码示例。
create table sales1
(
sales_id number,
product_code number,
state_codevarchar2(2)
)
partition by list (state_code)
subpartition by range (product_code)
(
partition CT values ('CT')
(
subpartition ct_100 values less than (101),
subpartition ct_200 values less than (201)
),
partition NY values ('NY')
(
subpartition NY_100 values less than (101),
subpartition NY_200 values less than (201)
)
)
您也可以创建范围-范围组合分区，如果您有两个日期域，该方法将非常方便。例如，考虑一个用于销售处理系统的表，该表包括一个交易日期和一个交货日期。您可能希望根据一个日期进行范围分区，然后再根据另一个日期进行子范围分区。该方案允许您根据日期进行备份、存档和清除。
数据库 11g 中可以创建以下类型的组合分区：
- 范围-范围
- 范围-散列
- 范围-列表
- 列表-范围
- 列表-散列
- 列表-列表
引用分区（11G）
引用分区通过从父表继承分区键（而非复制键列），从而可以在逻辑上均分具有父子关系的表。分区键通过现有的父子关系解析，由现行的主键或外键约束实施。逻辑相关性还可以自动级联分区维护操作，从而使应用程序开发更轻松且更不易出错。
下面是设计分区方案过程中的一个典型问题：并非所有表都具有您需要根据其进行分区的列。假设您要创建一个销售系统，该系统包括两个简单的表（sales 和 customers）：
create table customers
(
cust_id number primary key,
cust_namevarchar2(200),
rating varchar2(1) not null
)
partition by list (rating)
(
partitionpA values ('A'),
partitionpB values ('B')
);
sales 表的创建如下所示。它是 customers 表的一个子表。
create table sales
(
sales_id number primary key,
cust_id number not null,
sales_amt number,
constraint fk_sales_01
foreign key (cust_id)
references customers
);
理想情况下，您希望用相同的方式对 sales 表和 customers 表分区：根据 rating 列进行列表分区。但有一个严重问题：sales 表没有名为 rating 的列！那么如何根据一个不存在的列进行分区呢？
在 Oracle 数据库 11g 中，您可以使用一个称为引用分区的新特性。下面的示例显示了如何将该特性应用于 sales 表：
create table sales
(
sales_id number primary key,
cust_id number not null,
sales_amt number,
constraint fk_sales_01
foreign key (cust_id)
references customers
)
partition by reference (fk_sales_01);
这段代码创建了与父表 customers 中相同的分区。注意，虽然没有名为 rating 的列，但仍根据该列对表进行了分区。partition by reference (fk_sales_01) 子句包括了分区定义中的外键名。该语句指示 Oracle 数据库 11g 确认通过父表（在该示例中为 customers）中使用的方案进行了分区。注意 cust_id 列的 NOT NULL 约束；这是引用分区所必需的。
如果您检查 sales 表中分区的分区边界：
SQL> select partition_name, high_value
2 fromuser_tab_partitions
3 wheretable_name = 'SALES';
PARTITION_NAME HIGH_VALUE
--------------- -------------------------------
PA
PB
高值为空，这意味着此处的边界派生自父表。分区的名称与父表中的名称相同。您可以通过查询 user_part_tables 视图来检查分区的类型。一个名为 ref_ptn_constraint_name 的特殊列显示了外键约束名称。
SQL> select table_name, partitioning_type, ref_ptn_constraint_name
2 fromuser_part_tables
3 wheretable_name in ('CUSTOMERS','SALES');
TABLE_NAME PARTITION REF_PTN_CONSTRAINT_NAME
------------------------------ --------- --------------------------
CUSTOMERS LIST
SALES REFERENCE FK_SALES_01
如果您希望按照父表分区的方式对子表进行分区，但没有相同的列，您又不想仅仅为了分区而引入这些列，此时引用分区将非常方便。而且，您不必针对每个子表显式声明一个很长的分区子句。
间隔分区(11G)
范围分区允许您根据分区键列的值的范围创建分区。下面是一个按范围分区的表的示例：
create table sales6
(
sales_id number,
sales_dt date
)
partition by range (sales_dt)
(
partition p0701 values less than (to_date('2007-02-01','yyyy-mm-dd')),
partition p0702 values less than (to_date('2007-03-01','yyyy-mm-dd'))
);
您在此处仅针对 2007 年 1 月和 2007 年 2 月定义了分区，如果表中插入一条sales_dt在 2007 年 3 月的记录，会发生什么情况？插入将失败，并显示以下错误：
ORA-14400: inserted partition key does not map to any partition
显然，您需要针对 2007 年 3 月添加一个分区，然后才能插入一条记录。但通常说起来容易做起来难。您通常无法容忍事先创建大量分区，但其中很少一部分可能会产生此错误。
如果 Oracle 以某种方式自动察觉到对新分区的需要，然后创建它们，这样不是更好吗？Oracle 数据库 11g 可以，它可以使用一个称为间隔分区的特性。此时，您不必定义分区及它们的边界，只需定义一个定义了每个分区边界的间隔。下面是使用间隔分区的示例：
create table sales6
(
sales_id number,
sales_dt date
)
partition by range (sales_dt)
interval (numtoyminterval(1,'MONTH'))
(
partition p0701 values less than (to_date('2007-02-01','yyyy-mm-dd'))
);
注意子句：interval 后面跟着时间间隔。您在此处指示 Oracle 为每个月份创建一个时间间隔。您已经为 2007 年 1 月的数据创建了名为 p0701 的初始分区。现在，假设您插入了一条包括 2007 年 6 月数据的记录：
SQL> insert into sales6 values (1,'01-jun-07');
1 row created.
Oracle 不会返回错误，而是成功执行该语句。那么这条记录将转向何处？p0701 分区不能包括该记录，我们没有为 2007 年 6 月定义分区。但此时，如果您检查该表的分区：
SQL> select partition_name, high_value
2 fromuser_tab_partitions
3 wheretable_name = 'SALES6';
PARTITION_NAME HIGH_VALUE
--------------- ----------------------------------------------------------------
P0701 TO_DATE(' 2007-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C
ALENDAR=GREGORIA
SYS_P41 TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C
ALENDAR=GREGORIA
注意名为 SYS_P1、高值为 2007 年 7 月 1 日的分区，它最多可以容纳到 6 月底的数据。该分区是由 Oracle 动态创建的，并具有一个系统生成的名称。
现在，假设您输入一个小于最高值的值，如 2007 年 5 月 1 日。理想情况下，它应该具有自己的分区，因为您的分区时间间隔是一个月。
SQL> insert into sales6 values (1,'01-may-07');
1 row created.
SQL> select partition_name, high_value
2 fromuser_tab_partitions
3 wheretable_name = 'SALES6';
PARTITION_NAME HIGH_VALUE
--------------- ----------------------------------------------------------------
P0701 TO_DATE(' 2007-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C
ALENDAR=GREGORIA
SYS_P41 TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C
ALENDAR=GREGORIA
SYS_P42 TO_DATE(' 2007-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C
ALENDAR=GREGORIA
注意新分区 SYS_P42，其上限为 6 月 1 日，因此该分区可以保留 2006 年 5 月的数据。该分区是通过拆分 SYS_P41 分区创建的（针对 6 月份）。因此，当您定义一个间隔分区方案时，Oracle 会自动创建和维护分区。
如果您希望将分区存储在特定表空间中，可以使用 store in 子句执行该操作：
interval (numtoyminterval(1,'MONTH'))
store in (TS1,TS2,TS3)
该子句以循环方式将分区存储在表空间 TS1、TS2 和 TS3 中。
应用程序开发人员如何定位特定分区？一种方法是知道名称，这种方法可能不可行，即使您知道名称，这种方法也非常容易出错。为了便于访问特定分区，Oracle 数据库 11g 为分区 SQL 提供了一个新语法：
SQL> select * from sales6 partition for (to_date('15-may-2007','dd-mon-yyyy'));
SALES_ID SALES_DT
---------- ---------
1 01-MAY-07
注意新子句 for (值)，它允许您直接引用分区，而不必通过它们的确切名称进行显式调用。如果您希望截断或删除一个分区，可以调用这个扩展的分段语法。
以此方式创建表之后，DBA_PART_TABLES 视图中的 PARTITIONING_TYPE 列会显示时间间隔。
根据虚拟列分区(11G)
我们来看另一个常见问题。在名为 sales 的表中，您具有以下列：
SQL>desc sales
Name Null? Type
----------------------------------------- -------- ------
SALES_ID NOT NULL NUMBER
CUST_ID NOT NULL NUMBER
SALES_AMT NUMBER
假设您希望按照某个允许您进行清除的方案对该表进行分区，并且基于销售额进行存档。以下是销售的四个类别：
|
如果 sale_amt 为 |
且 cust_id 为 |
则 sale_category 为 |
|
0-10000 |
任何内容 |
LOW |
|
10001-100000 |
0-100 |
LOW |
|
10001-100000 |
101-200 |
MEDIUM |
|
10001-100000 |
>200 |
HIGH |
|
100001-1000000 |
0-100 |
MEDIUM |
|
100001-1000000 |
101-200 |
HIGH |
|
100001-1000000 |
>200 |
ULTRA |
|
>1000000 |
任何内容 |
ULTRA |
您希望根据 sale_category 列对该表进行分区，但有一个问题：没有名为 sale_category 的列。这是您从 sale_amt 列派生的列。那么您如何对该表进行分区呢？
在 Oracle 的早期版本中，您可能已经在表中插入了名为 sale_category 的新列，并使用一个触发器用表中所示的逻辑填充该列。但是由于触发器，这个新列的存在可能会导致其他性能影响。
在 Oracle 数据库 11g 中，一个称为虚拟列的新特性使您能够创建一个并不存储在表中的列，但在运行时将计算该列。您还可以根据该列进行分区。使用此特性，对该表进行分区就变得轻而易举。
create table sales
(
sales_id number,
cust_id number,
sales_amt number,
sale_categoryvarchar2(6)
generated always as
(
case
whensales_amt<= 10000
then 'LOW'
whensales_amt> 10000
andsales_amt<= 100000
then case
whencust_id< 101 then 'LOW'
whencust_id between 101 and 200 then 'MEDIUM'
else 'MEDIUM'
end
whensales_amt> 100000
andsales_amt<= 1000000
then case
whencust_id< 101 then 'MEDIUM'
whencust_id between 101 and 200 then 'HIGH'
else 'ULTRA'
end
else 'ULTRA'
end
) virtual
)
partition by list (sale_category)
(
partitionp_low values ('LOW'),
partitionp_medium values ('MEDIUM'),
partitionp_high values ('HIGH'),
partitionp_ultra values ('ULTRA')
)
现在，如果您插入以下行：
SQL> insert into sales (sales_id,cust_id,sales_amt) values (1,1,100);
1 row created.
SQL> insert into sales (sales_id,cust_id,sales_amt) values (2,1,1500);
1 row created.
SQL> insert into sales (sales_id,cust_id,sales_amt) values (3,102,1500);
1 row created.
SQL> insert into sales (sales_id,cust_id,sales_amt) values (4,102,10000);
1 row created.
SQL> commit;
Commit complete.
注意，您未输入sale_category 的值。现在，如果您检查 p_low 中的记录，将看到正确的记录：
SQL> select * from sales partition (p_low);
SALES_ID CUST_ID SALES_AMT SALE_C
---------- ---------- ---------- ------
1 1 100 LOW
该记录放在相应的分区中。
根据虚拟列分区使您能够创建对业务重要的分区，即使列本身是不存在的。这里，您已经对虚拟列使用了一个非常简单的计算方法，但它也可以如您希望的那样复杂。在这些情况下，根据虚拟列进行分区将变得更有价值。
交换分区(不作为一个分区形式，是一个转换工具)
Exchange partition提供了一种方式，让你在表与表或分区与分区之间迁
移数据，注意不是将表转换成分区或非分区的形式，而仅只是迁移表中数
据(互相迁移)，由于其号称是采用了更改数据字典的方式，因此效率最高(
几乎不涉及io操作)。Exchange partition适用于所有分区格式，你可以将
数据从分区表迁移到非分区表，也可以从非分区表迁移至分区表，或者从
hash partition到range partition诸如此类吧。
其语法很简单：alter table tbname1 exchange
partition/subpartitionptname with table tbname2;
注意：在将未分区表的数据迁移到分区表中时，可能出现ora-14099的错误，虽然可以用without validation去解决，但是此时进入分区表的数据可能不符合分区规则。所以without validation一定要慎用。
l 涉及交换的两表之间表结构必须一致，除非附加with validation子
句;
l 如果是从非分区表向分区表做交换，非分区表中的数据必须符合分
区表中指定分区的规则，除非附加without validation子句;
l 如果从分区表向分区表做交换，被交换的分区的数据必须符合分区
规则，除非附加without validation子句;
l Global索引或涉及到数据改动了的global索引分区会被置为
unusable，除非附加update indexes子句。
提示：
一旦附加了without validation子句，则表示不再验证数据有效性，
因此指定该子句时务必慎重。
分区表操作
以上了解了三种分区表的建表方法，下面将使用实际的数据并针对按日期的范围分区来测试分区表的数据记录的操作。
插入记录：
|
SQL> insert into dinya_testvalues(1,12,’BOOKS’,sysdate); |
按上面的建表结果，2006年前的数据将存储在第一个分区part_01上，而2006年到2010年的交易数据将存储在第二个分区part_02上，2010年以后的记录存储在第三个分区part_03上。
查询分区表记录：
|
SQL> select * from
dinya_test partition(part_01); |
从查询的结果可以看出，插入的数据已经根据交易时间范围存储在不同的分区中。这里是指定了分区的查询，当然也可以不指定分区，直接执行select * from dinya_test查询全部记录。
在也检索的数据量很大的时候，指定分区会大大提高检索速度。
更新分区表的记录
|
SQL> update
dinya_testpartition(part_01) t set t.item_description=’DESK’ where |
这里将第一个分区中的交易ID=1的记录中的item_description字段更新为“DESK”，可以看到已经成功更新了一条记录。但是当更新的时候指定了分区，而根据查询的记录不在该分区中时，将不会更新数据，请看下面的例子：
|
SQL> update
dinya_testpartition(part_01) t set t.item_description=’DESK’ where |
指定了在第一个分区中更新记录，但是条件中限制交易ID为6，而查询全表，交易ID为6的记录在第三个分区中，这样该条语句将不会更新记录。
删除分区表记录：
|
SQL> delete from
dinya_testpartition(part_02) t where t.transaction_id=4; |
上面例子删除了第二个分区part_02中的交易记录ID为4的一条记录，和更新数据相同，如果指定了分区，而条件中的数据又不在该分区中时，将不会删除任何数据。
分区表索引的使用
分区表和一般表一样可以建立索引，分区表可以创建局部索引和全局索引。当分区中出现许多事务并且要保证所有分区中的数据记录的唯一性时采用全局索引。
局部索引分区的建立
就是索引信息的存放位置依赖于父表的Partition信息，换句话说创建这样的索引必须保证父表是Partition
索引信息存放在父表的分区所在的表空间。但是仅可以创建在父表为HashTable或者composite分区表的。
LOCAL STORE IN (tablespace) 仅可以创建在父表为HashTable或者composite分区表的。并且指定的分区数目要与父表的分区数目要一致
LOCAL STORE IN (tablespace) (PARTITION [partition [LOGGING|NOLOGGING] [TABLESPACE {tablespace|DEFAULT}] [PCTFREE int] [PCTUSED int] [INITRANS int] [MAXTRANS int] [STORAGE storage_clause] [STORE IN {tablespace_name|DEFAULT] [SUBPARTITION [subpartition [TABLESPACE tablespace]]]])
索引信息存放在父表的分区所在的表空间，这种语法最简单，也是最常用的分区索引创建方式。 Local
并且指定的Partition 数目要与父表的Partition要一致
LOCAL (PARTITION [partition
[LOGGING|NOLOGGING]
[TABLESPACE {tablespace|DEFAULT}]
[PCTFREE int]
[PCTUSED int]
[INITRANS int]
[MAXTRANS int]
[STORAGE storage_clause]
[STORE IN {tablespace_name|DEFAULT]
[SUBPARTITION [subpartition [TABLESPACE tablespace]]]])
|
SQL> create index
dinya_idx_t on dinya_test(item_id) |
看查询的执行计划，从下面的执行计划可以看出，系统已经使用了索引：
|
SQL> select * from
dinya_test partition(part_01) t where t.item_id=12; |
全局索引分区的建立
索引信息的存放位置与父表的Partition信息完全不相干。甚至父表是不是分区表都无所谓的。语法如下：
GLOBAL PARTITION BY RANGE (col_list)
( PARTITION partition VALUES LESS THAN (value_list)
[LOGGING|NOLOGGING]
[TABLESPACE {tablespace|DEFAULT}]
[PCTFREE int]
[PCTUSED int]
[INITRANS int]
[MAXTRANS int]
[STORAGE storage_clause] )
但是在这种情况下，如果父表是分区表，要删除父表的一个分区都必须要更新Global Index ,否则索引信息不正确
ALTER TABLE TableName DROP PARTITION PartitionName Update Global Indexes
--查询索引
select object_name,object_type,tablespace_name,sum(value)
from v$segment_statistics
where statistic_name IN ('physical reads','physicalwrite','logical reads')and
object_type='INDEX'
group by object_name,object_type,tablespace_name
order by 4 desc
全局索引建立时global 子句允许指定索引的范围值，这个范围值为索引字段的范围值：
|
SQL> create index
dinya_idx_t on dinya_test(item_id) |
本例中对表的item_id字段建立索引分区，当然也可以不指定索引分区名直接对整个表建立索引，如：
|
SQL> create index
dinya_idx_t on dinya_test(item_id); |
同样的，对全局索引根据执行计划可以看出索引已经可以使用：
|
SQL> select * from
dinya_test t where t.item_id=12; |
分区表的维护
了解了分区表的建立、索引的建立、表和索引的使用后，在应用的还要经常对分区进行维护和管理。日常维护和管理的内容包括：增加一个分区，合并一个分区及删除分区等等。下面以范围分区为例说明增加、合并、删除分区的一般操作：
ü 增加一个分区
以下代码给SALES表添加了一个P3分区
ALTERTABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD'));
注意：以上添加的分区界限应该高于最后一个分区界限。
|
SQL> alter table dinya_test 2 add partition part_04 values lessthan(to_date(’2012-01-01’,’yyyy-mm-dd’)) tablespacedinya_spa ce03; Table altered. SQL> |
增加一个分区的时候，增加的分区的条件必须大于现有分区的最大值，否则系统将提示ORA-14074 partition bound must collate higher than that of the last partition 错误。
ü 删除分区
以下代码删除了P3表分区：
ALTER TABLE SALES DROP PARTITION P3;
在以下代码删除了P4SUB1子分区：
ALTER TABLE SALES DROP SUBPARTITION P4SUB1;
注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。
删除分区表的一个分区后，查询该表的数据时显示，该分区中的数据已全部丢失，所以执行删除分区动作时要慎重，确保先备份数据后再执行，或将分区合并。
以下代码给SALES表的P3分区添加了一个P3SUB1子分区
ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE');
ü 截断分区
截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。当表中即使只有一个分区时，也可以截断该分区。通过以下代码截断分区：
ALTER TABLE SALES TRUNCATE PARTITION P2;
通过以下代码截断子分区：
ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;
ü 合并分区
合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能将分区合并到界限较低的分区。以下代码实现了P1 P2分区的合并：
ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;
ü 拆分分区
拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。
ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);
ü 接合分区(coalesca)
结合分区是将散列分区中的数据接合到其它分区中，当散列分区中的数据比较大时，可以增加散列分区，然后进行接合，值得注意的是，接合分区只能用于散列分区中。通过以下代码进行接合分区：
ALTER TABLE SALES COALESCA PARTITION;
ü 重命名表分区
以下代码将P21更改为P2
ALTER TABLE SALES RENAME PARTITION P21 TO P2;
八、相关查询
ü 跨分区查询
select sum( *) from
(select count(*) cn from t_table_SS PARTITION (P200709_1)
union all
select count(*) cn from t_table_SS PARTITION (P200709_2)
);
ü 查询表上有多少分区
SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='tableName'
ü 查询索引信息
selectobject_name,object_type,tablespace_name,sum(value)
fromv$segment_statistics
wherestatistic_name IN ('physical reads','physicalwrite','logical reads')and object_type='INDEX'
group by object_name,object_type,tablespace_name
order by 4 desc
ü --显示数据库所有分区表的信息：
select * from DBA_PART_TABLES
ü --显示当前用户可访问的所有分区表信息:
select * from ALL_PART_TABLES
ü --显示当前用户所有分区表的信息：
select * from USER_PART_TABLES
ü --显示表分区信息显示数据库所有分区表的详细分区信息：
select * from DBA_TAB_PARTITIONS
ü --显示当前用户可访问的所有分区表的详细分区信息：
select * from ALL_TAB_PARTITIONS
ü
ü --显示当前用户所有分区表的详细分区信息：
select * from USER_TAB_PARTITIONS
ü --显示子分区信息显示数据库所有组合分区表的子分区信息：
select * from DBA_TAB_SUBPARTITIONS
ü --显示当前用户可访问的所有组合分区表的子分区信息：
select * from ALL_TAB_SUBPARTITIONS
ü --显示当前用户所有组合分区表的子分区信息：
select * from USER_TAB_SUBPARTITIONS
ü --显示分区列显示数据库所有分区表的分区列信息：
select * from DBA_PART_KEY_COLUMNS
ü --显示当前用户可访问的所有分区表的分区列信息：
select * from ALL_PART_KEY_COLUMNS
ü --显示当前用户所有分区表的分区列信息：
select * from USER_PART_KEY_COLUMNS
ü --显示子分区列显示数据库所有分区表的子分区列信息：
select * from DBA_SUBPART_KEY_COLUMNS
ü --显示当前用户可访问的所有分区表的子分区列信息：
select * from ALL_SUBPART_KEY_COLUMNS
ü --显示当前用户所有分区表的子分区列信息：
select* from USER_SUBPART_KEY_COLUMNS
ü --怎样查询出oracle数据库中所有的的分区表
select * from user_tables a where a.partitioned='YES'
ü --删除一个表的数据是
truncate table table_name;
ü --删除分区表一个分区的数据是
alter table table_name truncate partition p5;
确定表分区的分区策约是最重要的，因为随着数据量的增加，表分区必然存在删除，扩容，增加等。在这些过程中还牵涉到全局等索引，因为对分区表进行ddl操作为破坏全局索引，故全局索引必须在ddl后要重rebuild.
重建整个全局索引或其全部分区。如果已被分区，Oracle 允许在用于维护操作的ALTER TABLE 语句中指定UPDATE GLOBAL INDEXES 来重载这个默认特性，指定这个子句也就告诉Oracle 当他执行维护操作的DDL语句时更新全局索引，这提供了如下好处：
1.在操作基础表的同时更新全局索引这就不必后来独立地重建全局索引；
2.因为没有被标记成UNUSABLE，所以全局索引的可用性更高了，甚至正在执行分区的DDL 语句时仍然可用索引来访问表中的其他分区,避免了查询所有失效的全局索引的名字以便重建他们；另外在指定UPDATE GLOBAL INDEXES 之前还要考虑如下性能因素:
1.因为要更新事先被标记成UNUSABLE 的索引，所以分区的DDL 语句要执行更长时间，当然这要和先不更新索引而执行DDL 然后再重建索引所花的时间做个比较，一个适用的规则是如果分区的大小小于表的大小的5% ，则更新索引更快一点；
2.DROP TRUNCATE 和EXCHANGE 操作也不那么快了，同样这必须和先执行DDL
然后再重建所有全局索引所花的时间做个比较；
3.要登记对索引的更新并产生重做记录和撤消记录，重建整个索引时可选择NOLOGGING；
4.重建整个索引产生一个更有效的索引，因为这更利于使用空间，再者重建索引时允许修改存储选项。注意分区索引结构表不支持UPDATE GLOBAL INDEXES 子句
oracle 把非分区表转化为分区表（对split partition 和exchange partition的使用）
/*
功能：
将一个非分区表转换为分区表
基本思路：
SST_DAY_TOTAL是(数据量上百万条，列比较多)一个非分区表，此时创建一个与SST_DAY_TOTAL同结构的分区表zhaozhenlong_partition，维护数据，
删除SST_DAY_TOTAL表，将zhaozhenlong_partition更名为SST_DAY_TOTA，删除zhaozhenlong_partition
*/
--具体步骤：
--1、查询非分区表数据
select to_char(rpt_date, 'yyyymm'), count(*) from SST_DAY_TOTAL group by
to_char(rpt_date, 'yyyymm') order by to_char(RPT_DATE, 'yyyymm');
--2、创建分区表（结构和非分区表SST_DAY_TOTAL相同）
create table zhaozhenlong_partition
(
GAS_ID
VARCHAR2(12) not null,
RPT_DATE
DATE not null,
INV_NO
VARCHAR2(12),
......
)
tablespacesdlg
partition by range(rpt_date)(
partition rest values less than (maxvalue))
--3、交换数据（数据从非分区表到分区表）
alter table zhaozhenlong_partition exchange partition rest with table
SST_DAY_TOTAL;
--4、查询分区表数据
select to_char(RPT_DATE, 'mm-yyyy'), count(*) from SST_DAY_TOTAL group by
to_char(RPT_DATE, 'mm-yyyy');
--辅助脚本
/*
select 'alter table zhaozhenlong_partition split partition rest at (to_date('''
|| to_char(RPT_DATE, 'yyyymm') ||''',''yyyymm'')) into (partition p'
|| to_char(RPT_DATE, 'yyyymm')
|| ', partition rest);'
fromzhaozhenlong_partition
group by to_char(RPT_DATE, 'yyyymm')
order by to_char(RPT_DATE, 'yyyymm')
*/
--5、对rest分区进行拆分
alter table zhaozhenlong_partition split partition rest at
(to_date('200608','yyyymm')) into (partition p200608, partition rest);
alter table zhaozhenlong_partition split partition rest at
(to_date('200609','yyyymm')) into (partition p200609, partition rest);
alter table zhaozhenlong_partition split partition rest at
(to_date('200610','yyyymm')) into (partition p200610, partition rest);
alter table zhaozhenlong_partition split partition rest at
(to_date('200611','yyyymm')) into (partition p200611, partition rest);
alter table zhaozhenlong_partition split partition rest at
(to_date('200612','yyyymm')) into (partition p200612, partition rest);
--6、删除非分区表
drop table SST_DAY_TOTAL
--7、将分区表重命名为原非分区表名
alter table zhaozhenlong_partition rename to SST_DAY_TOTAL
--8、删除中间处理过程的分区表
drop table zhaozhenlong_partition
--补充
如果已存在分区表，要将某未分区表（与已分区表结构完全相同）加入到分区中：
alter table SST_DAY_TOTAL exchange partition p200608 with table
zhaozhenlong_1;
alter table SST_DAY_TOTAL exchange partition p200609 with table zhaozhenlong_2;
如果zhaozhenlong_2不符合分区规则，则会报错，则需要指定without
validation 来禁止检查
alter table SST_DAY_TOTAL exchange partition p200609 with table zhaozhenlong_2
without validation;
当然，如果你确认zhaozhenlong_1表中的数据是符合分区规则的，那指定without validation 之后，不会对zhaozhenlong_1进行全表扫描，
则会缩短exchange时间
