# 概要
Oracle 11g中引进的分区新功能
- Interval 分区(Interval Partitioning).
- 扩展的复合分区(Extended Composite Partitioning).
- 系统分区(System Partitioning).
- 虚拟列分区(Vitual Column Partitioning).
- 关联分区或叫参考分区(Reference Partitioning).
# 概述与限制
## Interval 分区
它属于范围分区。这种分区不需要定义MAXVALUE，Oracle会自动地根据分区定义来动态地分配新分区，以便存储超出已有分区范围的数据。换句话说，Oracle只会给已存在的数据分区进行分配，不会初始所有的未存在的分区，当有需要时，再自动分配。
### Interval分区表限制
-
Interval 分区表只能拥有一个分区关键字列，且此列的数据类型只支持NUMBER和DATE类型.
-
索引组织表(IOT)不受支持.
-
在Interval分区中，不支持域索引(Domain Index).
-
Interval分区可以创建在复合分区表中的主分区上，但不支持子分区；即不能在SUBPARTITION 上创建Interval分区.
## 扩展的复合分区
Oracle 11g中新引进了四种复合分区特性：RANGE-RANGE、LIST-RANGE、LIST-HASH和LIST-LIST；相比至Oracle9i的两种复合分区：RANGE-HASH和RANGE-LIST，可提供更多的分区方法和选择，以及更好的分区效果.
## 系统分区
Oracle 11g引进的一种新功能；它可为一个表提供多个物理分区，以供存储数据。在定义这种表时，可以指定多个分区，而不需要定义分区入口(Partiotion Key)；这种表分区没有提供分区方式，因而在Insert数据时，须提供Partition语句，以指定数据存储的分区。
### 系统分区表限制
- 支持本地索引、本地位图索引、全局索引、域索引.
- 支持带有Partition子句的ITAS(Insert as select)语句对其进行操作.
- 不支持CTAS(Creat as select)语句.
## 虚拟列分区
Oracle 11g新引进了虚拟列(Vitual Columns)功能，它是基于其他物理列由表达式或函数计算而来的列.在Oracle 11g，虚拟列支持分区功能，可以作为分区关键字列，所以有了虚拟列分区特性.
### 虚拟列分区限制
虚拟列分区限制只要受虚拟列的限制
- 不可向分区关键字列写数据，只能通过其定义来驱动数据。
- 不支持索引组织表(IOT)、外部表、簇表、对象表、临时表。
- 不支持用户定义、Oracle-supplied、LONG RAWs、LOBs数据类型。
## 关联或参考分区
Oracle 11g引进了一种新的分区方法，它用于主从表之间的关联性上。若主表建立分区，从表可以根据外键约束来建立对应的主表分区。
Oracle自动维护在从表上的分区，不需要手工干预。
### 关联分区限制
- 不支持Interval 分区。
- 不支持索引组织表、外部表、域索引。
- 关联的主键和唯一约束不能支持虚拟列。
# 新分区功能 定义与使用
## Interval 分区
1. 创建一个Interval分区表
SQL> create table emp_interval_tab
2 (empno integer,
3 ename varchar2(8),
4 deptno varchar2(6),
5 hirdate date,
6 sal number(8,2),
7 degree varchar2(20))
8 partition by range (hirdate)
9 interval (numtoyminterval(1,'MONTH'))
10 ( partition p_bf_20080101 values
11 less than (to_date('01-01-2008','dd-mm-yyyy')) tablespace users);
2. 查看分区表信息
SQL> select table_name,
2 partition_name,
3 subpartition_count,
4 high_value
5 from user_tab_partitions
6 where table_name = 'EMP_INTERVAL_TAB';
TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE
----------------- ---------------- ------------------ ------------------------------------------------------------------------
EMP_INTERVAL_TAB P_BF_20080101 0 TO_DATE(' 2008-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
3. 开始新插入数据到表中，再检测表分区的变化.
SQL> insert into emp_interval_tab(empno,ename,deptno,hirdate)
2 values(1112,'gite',10,to_date('2008-01-02','yyyy-mm-dd'));
已创建 1 行。
★.插入这条数据后,oracle将自动分配一个新的分区.
SQL> select table_name,
2 partition_name,
3 subpartition_count,
4 high_value
5 from user_tab_partitions
6 where table_name = 'EMP_INTERVAL_TAB';
TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE
----------------- --------------- ------------------ ------------------------------------------------------------------------
EMP_INTERVAL_TAB P_BF_20080101 0 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
EMP_INTERVAL_TAB SYS_P21 0 TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
4. 首次插入数据后，Oracle自动分配的分区，不能随rollback语句撤消
SQL> select * from emp_interval_tab;
EMPNO ENAME DEPTNO HIRDATE SAL DEGREE
---------- ---------------- ------------ -------------- ---------- -------
1112 gite 10 02-1月 -08
SQL> rollback;
回退已完成。
SQL> select * from emp_interval_tab;
未选定行
SQL> select table_name,
2 partition_name,
3 subpartition_count,
4 high_value
5 from user_tab_partitions
6 where table_name = 'EMP_INTERVAL_TAB';
TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE
----------------- -------------- ------------------ -------------------------------------------------------------------------
EMP_INTERVAL_TAB P_BF_20080101 0 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
EMP_INTERVAL_TAB SYS_P21 0 TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
## 复合分区
四种复合分区：RANGE-RANGE、LIST-RANGE、LIST-HASH和LIST-LIST使用示例.
### 范围-范围分区(RANGE-RANGE),即主分区使用范围分区方法，子分区也使用范围分区方法.
创建RANGE-RANGE分区表.
SQL> create table emp_r_r_tab
2 (empno integer,
3 ename varchar2(8),
4 deptno integer,
5 hirdate date,
6 sal number(8,2),
7 degree varchar2(20),
8 constraint pk_emp_rr_empno primary key(empno))
9 partition by range (hirdate)
10 subpartition by range (sal)
11 (partition hirdate_07
12 values less than (to_date('2007-12-31','yyyy-mm-dd'))
13 (subpartition p_sb1_emp_sal_1000
14 values less than (1000),
15 subpartition p_sb1_emp_sal_3000
16 values less than (3000),
17 subpartition p_sb1_emp_sal_5000
18 values less than (5000),
19 subpartition p_sb1_emp_sal_10000
20 values less than (10000),
21 subpartition p_sb1_emp_sal_oth
22 values less than (MAXVALUE)
23 ),
24 partition hirdate_08
25 values less than (to_date('2008-12-31','yyyy-mm-dd'))
26 (subpartition p_sb2_emp_sal_1000
27 values less than (1000),
28 subpartition p_sb2_emp_sal_3000
29 values less than (3000),
30 subpartition p_sb2_emp_sal_5000
31 values less than (5000),
32 subpartition p_sb2_emp_sal_10000
33 values less than (10000),
34 subpartition p_sb2_emp_sal_oth
35 values less than (MAXVALUE)
36 ),
37 partition hirdate_oth
38 values less than (MAXVALUE)
39 (subpartition p_sb3_emp_sal_1000
40 values less than (1000),
41 subpartition p_sb3_emp_sal_3000
42 values less than (3000),
43 subpartition p_sb3_emp_sal_5000
44 values less than (5000),
45 subpartition p_sb3_emp_sal_10000
46 values less than (10000),
47 subpartition p_sb3_emp_sal_oth
48 values less than (MAXVALUE)
49 )
50 );
表已创建。
### 列表-范围分区(LIST-RANGE),即主分区使用列表分区方法，子分区使用范围分区方法
创建LIST-RANGE分区表.
SQL> create table emp_l_r_tab
2 (empno integer,
3 ename varchar2(8),
4 deptno integer,
5 hirdate date,
6 sal number(8,2),
7 degree varchar2(20),
8 constraint pk_emp_lr_empno primary key(empno))
9 partition by list (deptno)
10 subpartition by range (hirdate)
11 (partition emp_list_1 values (10,60)
12 (
13 subpartition p_sb1_emp_hir_07 values less than (to_date('2007-12-31','yyyy-mm-dd')),
14 subpartition p_sb1_emp_hir_08 values less than (to_date('2008-12-31','yyyy-mm-dd')),
15 subpartition p_sb1_emp_hir_oth values less than (MAXVALUE)
16 ),
17 partition emp_list_2 values (20,30,40)
18 (
19 subpartition p_sb2_emp_hir_07 values less than (to_date('2007-12-31','yyyy-mm-dd')),
20 subpartition p_sb2_emp_hir_08 values less than (to_date('2008-12-31','yyyy-mm-dd')),
21 subpartition p_sb2_emp_hir_oth values less than (MAXVALUE)
22 ),
23 partition emp_list_def values (DEFAULT)
24 (
25 subpartition p_sb3_emp_hir_07 values less than (to_date('2007-12-31','yyyy-mm-dd')),
26 subpartition p_sb3_emp_hir_08 values less than (to_date('2008-12-31','yyyy-mm-dd')),
27 subpartition p_sb3_emp_hir_oth values less than (MAXVALUE)
28 )
29 );
表已创建。
### 列表-哈希分区(LIST-HASH),即主分区使用列表分区方法，子分区使用哈希分区方法
创建LIST-HASH分区表
SQL> create table emp_l_h_tab
2 (empno integer,
3 ename varchar2(8),
4 deptno integer,
5 hirdate date,
6 sal number(8,2),
7 degree varchar2(20),
8 constraint pk_emp_lh_empno primary key(empno))
9 partition by list (deptno)
10 subpartition by hash (degree)
11 subpartitions 3
12 store in(users)
13 (partition emp_lh_1 values (10,60),
14 partition emp_lh_2 values (20,30,40),
15 partition emp_lh_def values (DEFAULT)
16 );
表已创建。
### 列表-列表分区(LIST-LIST),即主分区使用列表分区方法，子分区也使用列表分区方法
创建LIST-LIST分区表
SQL> create table emp_l_l_tab
2 (empno integer,
3 ename varchar2(8),
4 deptno integer,
5 hirdate date,
6 sal number(8,2),
7 degree varchar2(20),
8 constraint pk_emp_ll_empno primary key(empno))
9 partition by list (deptno)
10 subpartition by list (degree)
11 (partition emp_ll_1 values (10,60)
12 (
13 subpartition p_sb1_emp_deg1 values ('小学','大学','硕士'),
14 subpartition p_sb1_emp_deg2 values ('本科','专科','博士'),
15 subpartition p_sb1_emp_deg3 values (DEFAULT)
16 ),
17 partition emp_ll_2 values (20,30,40)
18 (
19 subpartition p_sb2_emp_deg1 values ('小学','大学','硕士'),
20 subpartition p_sb2_emp_deg2 values ('本科','专科','博士'),
21 subpartition p_sb2_emp_deg3 values (DEFAULT)
22 ),
23 partition emp_ll_def values (DEFAULT)
24 (
25 subpartition p_sb3_emp_deg1 values ('小学','大学','硕士'),
26 subpartition p_sb3_emp_deg2 values ('本科','专科','博士'),
27 subpartition p_sb3_emp_deg3 values (DEFAULT)
28 )
29 );
表已创建。
### 创建完毕，我们可以查看这些分区的信息
a.查看分区表分区类型信息
SQL> select table_name,
2 partitioning_type,
3 subpartitioning_type,
4 partition_count
5 from user_part_tables
6 /
TABLE_NAME PARTITIONING_TYPE SUBPARTITIONIN PARTITION_COUNT
----------------- ------------------ -------------- ---------------
EMP_INTERVAL_TAB RANGE NONE 1048575
EMP_L_H_TAB LIST HASH 3
EMP_L_L_TAB LIST LIST 3
EMP_L_R_TAB LIST RANGE 3
EMP_PART_TAB RANGE NONE 3
EMP_R_R_TAB RANGE RANGE 3
b.查看主分区信息
SQL> select table_name,partition_name,subpartition_count
2 from user_tab_partitions
3 /
TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT
----------------- -------------- -----------
EMP_PART_TAB LOW_COMM 0
EMP_PART_TAB MED_COMM 0
EMP_PART_TAB HIG_COMM 0
EMP_INTERVAL_TAB P_BF_20080101 0
EMP_INTERVAL_TAB SYS_P21 0
EMP_INTERVAL_TAB SYS_P22 0
EMP_INTERVAL_TAB SYS_P23 0
EMP_R_R_TAB HIRDATE_07 5
EMP_R_R_TAB HIRDATE_08 5
EMP_R_R_TAB HIRDATE_OTH 5
EMP_L_R_TAB EMP_LIST_1 3
TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT
----------------- -------------- -----------
EMP_L_R_TAB EMP_LIST_2 3
EMP_L_R_TAB EMP_LIST_DEF 3
EMP_L_H_TAB EMP_LH_1 3
EMP_L_H_TAB EMP_LH_2 3
EMP_L_H_TAB EMP_LH_DEF 3
EMP_L_L_TAB EMP_LL_1 3
EMP_L_L_TAB EMP_LL_2 3
EMP_L_L_TAB EMP_LL_DEF 3
c.查看子分区信息
SQL> select table_name,partition_name,subpartition_name
2 from user_tab_subpartitions
3 /
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
----------- -------------- -----------
EMP_R_R_TAB HIRDATE_07 P_SB1_EMP_SAL_OTH
EMP_R_R_TAB HIRDATE_07 P_SB1_EMP_SAL_10000
EMP_R_R_TAB HIRDATE_07 P_SB1_EMP_SAL_5000
EMP_R_R_TAB HIRDATE_07 P_SB1_EMP_SAL_3000
EMP_R_R_TAB HIRDATE_07 P_SB1_EMP_SAL_1000
EMP_R_R_TAB HIRDATE_08 P_SB2_EMP_SAL_OTH
EMP_R_R_TAB HIRDATE_08 P_SB2_EMP_SAL_10000
EMP_R_R_TAB HIRDATE_08 P_SB2_EMP_SAL_5000
EMP_R_R_TAB HIRDATE_08 P_SB2_EMP_SAL_3000
EMP_R_R_TAB HIRDATE_08 P_SB2_EMP_SAL_1000
EMP_R_R_TAB HIRDATE_OTH P_SB3_EMP_SAL_OTH
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
----------- -------------- -----------
EMP_R_R_TAB HIRDATE_OTH P_SB3_EMP_SAL_10000
EMP_R_R_TAB HIRDATE_OTH P_SB3_EMP_SAL_5000
EMP_R_R_TAB HIRDATE_OTH P_SB3_EMP_SAL_3000
EMP_R_R_TAB HIRDATE_OTH P_SB3_EMP_SAL_1000
EMP_L_R_TAB EMP_LIST_1 P_SB1_EMP_HIR_OTH
EMP_L_R_TAB EMP_LIST_1 P_SB1_EMP_HIR_08
EMP_L_R_TAB EMP_LIST_1 P_SB1_EMP_HIR_07
EMP_L_R_TAB EMP_LIST_2 P_SB2_EMP_HIR_OTH
EMP_L_R_TAB EMP_LIST_2 P_SB2_EMP_HIR_08
EMP_L_R_TAB EMP_LIST_2 P_SB2_EMP_HIR_07
EMP_L_R_TAB EMP_LIST_DEF P_SB3_EMP_HIR_OTH
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
----------- -------------- -----------
EMP_L_R_TAB EMP_LIST_DEF P_SB3_EMP_HIR_08
EMP_L_R_TAB EMP_LIST_DEF P_SB3_EMP_HIR_07
EMP_L_H_TAB EMP_LH_1 SYS_SUBP26
EMP_L_H_TAB EMP_LH_1 SYS_SUBP25
EMP_L_H_TAB EMP_LH_1 SYS_SUBP24
EMP_L_H_TAB EMP_LH_2 SYS_SUBP29
EMP_L_H_TAB EMP_LH_2 SYS_SUBP28
EMP_L_H_TAB EMP_LH_2 SYS_SUBP27
EMP_L_H_TAB EMP_LH_DEF SYS_SUBP32
EMP_L_H_TAB EMP_LH_DEF SYS_SUBP31
EMP_L_H_TAB EMP_LH_DEF SYS_SUBP30
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
----------- -------------- -----------
EMP_L_L_TAB EMP_LL_1 P_SB1_EMP_DEG3
EMP_L_L_TAB EMP_LL_1 P_SB1_EMP_DEG2
EMP_L_L_TAB EMP_LL_1 P_SB1_EMP_DEG1
EMP_L_L_TAB EMP_LL_2 P_SB2_EMP_DEG3
EMP_L_L_TAB EMP_LL_2 P_SB2_EMP_DEG2
EMP_L_L_TAB EMP_LL_2 P_SB2_EMP_DEG1
EMP_L_L_TAB EMP_LL_DEF P_SB3_EMP_DEG3
EMP_L_L_TAB EMP_LL_DEF P_SB3_EMP_DEG2
EMP_L_L_TAB EMP_LL_DEF P_SB3_EMP_DEG1
## 分区维护
Oracle 11g中，采用引进的这些新的分区功能的分区的维护和其他普通分区表一样,
可以进行分区表的分区重命名、合并、分裂等操作.
### 系统分区
- 创建系统分区表
SQL> create table emp_s_tab
2 (empno integer,
3 ename varchar2(8),
4 deptno integer,
5 hirdate date,
6 sal number(8,2),
7 degree varchar2(20),
8 constraint pk_emp_s_empno primary key (empno))
9 partition by system
10 (
11 partition emp_s_01 tablespace users,
12 partition emp_s_02 tablespace users,
13 partition emp_s_03 tablespace users,
14 partition emp_s_04 tablespace users,
15 partition emp_s_05 tablespace users);
表已创建。
2. 分区入口
系统分区，在定义时没有指定分区入口(关键字),即没有指定分区方式，所以它的分区实现是通过应用程序来处理和控制,就是通过在插入数据的语句中使用Partition字句，来指定存储的分区。
SQL> insert into emp_s_tab
2 partition (emp_s_03)
3 values (1112,'jack',10,sysdate,8000.00,'本科');
SQL> insert into emp_s_tab
2 partition (emp_s_01)
3 (empno,ename,deptno,hirdate)
4 values (1113,'kacy',20,sysdate);
3. 维护
系统分区表，对其维护时可以使用大部分维护功能，但不能进行分裂(split)，因系统分区没有分区列，即不存在分区方式，Oracle无法把分区的数据分配到两个新的分区中.
### 虚拟列分区
虚拟列分区，顾名思义，是通过虚拟列来实现分区，即虚拟列作为分区关键字的一种分区实现方式.
创建虚拟列分区
SQL> create table emp_vcol_tab
2 (empno integer constraint pk_emp_vcol_empno primary key,
3 ename varchar2(8),
4 deptno integer,
5 hire_date date,
6 sal number(8),
7 comm as (sal*0.3+300))
8 partition by range (comm)
9 ( partition p_low_comm values less than (500) tablespace users,
10 partition p_med_comm values less than (1000) tablespace users,
11 partition p_hig_comm values less than (maxvalue) tablespace users
12 );
表已创建。
### 关联或参考分区
关联分区，是建立在主从表之间的约束上,因而需建立两个主从表.
1. 首先建立主表
SQL> create table sales
2 ( product_id integer,
3 sale_date date not null,
4 sale_id integer not null,
5 saler varchar2(8),
6 constraint pk_sales_sale_id primary key (sale_id))
7 partition by range (sale_date)
8 ( partition p_sal_2007
9 values less than (to_date('2007-12-31','yyyy-mm-dd')),
10 partition p_sal_2008
11 values less than (to_date('2008-12-31','yyyy-mm-dd')),
12 partition p_sal_oth
13 values less than (MAXVALUE)
14 );
表已创建。
2. 建立从表
SQL> create table sales_detail
2 (sale_id integer not null,
3 product_id integer not null,
4 sales_amount number not null,
5 sale_price number(6,2),
6 sales_fee as (sale_price*sales_amount),
7 constraint fk_sales_detail
8 foreign key (sale_id) references sales(sale_id)
9 )
10 partition by reference (fk_sales_detail);
表已创建。
3. 创建完参考分区，我们可以查看此种分区表的信息
查看分区表类型
SQL> select table_name,partitioning_type,
2 subpartitioning_type,partition_count
3 from user_part_tables
4 where table_name in('SALES','SALES_DETAIL');
TABLE_NAME PARTITIONING_TYPE SUBPARTITIONIN PARTITION_COUNT
------------- ------------------ -------------- ---------------
SALES RANGE NONE 3
SALES_DETAIL REFERENCE NONE 3
查看分区情况
SQL> select table_name,partition_name,high_value
2 from user_tab_partitions
3 where table_name in('SALES','SALES_DETAIL');
TABLE_NAME PARTITION_NAME HIGH_VALUE
------------ -------------- ------------------------------------------------------------------------
SALES P_SAL_2007 TO_DATE(' 2007-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES P_SAL_2008 TO_DATE(' 2008-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES P_SAL_OTH MAXVALUE
SALES_DETAIL P_SAL_2007
SALES_DETAIL P_SAL_200
SALES_DETAIL P_SAL_OTH
★.从表在分区上继承了主表的分区方式。值得注意的是从表的外键约束列必须定义为NOT NULL约束.
扫一扫
4248
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
