分区就是将一个非常大的表或者索引物理地分解为多个较小的、可独立管理的部分。
分区表或索引在逻辑上是一个表或一个索引,但物理上是由多个物理分区组成的。
分区功能通过改善可管理性、性能、可用性,为各种应用系统带来了极大的好处。
分区功能的好处:
1.增强数据可用性:如果表的一个分区因故障或者维护而不能使用时,表的其余分区仍是可用的;
2.维护方便:独立管理多个分区,比维护单个大表要轻松;
3.均衡I/O:可以把不同分区映射到磁盘以平衡I/O,显著改善性能;
4.改善查询性能:对已分区对象的某些查询可以运行更快,因为搜索仅限于关心的分区;
分区表有哪些??
Oracle 11g 提供6种表分区方法:范围分区(range)、散列分区(hash)、列表分区(list)、符合分区、间隔分区、引用分区。
按表中某个列值的范围进行分区,根据该列的值决定将数据存储在哪个分区上。
创建范围分区需注意以下几点:
1.指明分区方法,分区列,和分区描述
2.每一个分区都有values less than子句
3.在最高分区中定义maxvalue,这个maxvalue值高区其他分区中的任何键值。
例:创建范围分区
create table range_orders
(order_id varchar2(10) constraint OR_PK primary key
,order_date date default sysdate
,qty integer
,payterms varchar2(10)
,book_id number(6)
)
partition by range (order_date)
(partition p1 values less than (to_date('20140331','yyyymmdd')) tablespace user01,
partition p2 values less than (to_date('20140430','yyyymmdd')) tablespace user02,
partition p3 values less than (to_date('20140531','yyyymmdd')) tablespace user03
)
;
SQL> insert into range_orders values ('10001',to_date('20140321','yyyymmdd'),1,'payterm_1',110345);
1 row inserted
SQL> insert into range_orders values ('10002',to_date('20140421','yyyymmdd'),1,'payterm_2',110745);
1 row inserted
SQL> insert into range_orders values ('10003',to_date('20140521','yyyymmdd'),1,'payterm_3',110945);
1 row inserted
SQL> commit;
Commit complete
SQL> select rowid,r.* from range_orders r;
ROWID ORDER_ID ORDER_DATE QTY PAYTERMS BOOK_ID
------------------ ---------- ----------- --------------------------------------- ---------- -------
AAADwpAAGAAAACFAAA 10001 2014/3/21 1 payterm_1 110345
AAADwqAAHAAAACFAAA 10002 2014/4/21 1 payterm_2 110745
AAADwrAAIAAAACFAAA 10003 2014/5/21 1 payterm_3 110945
---
可以看到AAG,AAH,AAI 分别代表了三条数据的文件号是6,7,8
备注:这个地方可以看下 http://blog.itpub.net/28929558/viewspace-1150766/ 了解rowid
验证下
SQL> select x.FILE#,x.NAME from v$datafile x;
FILE# NAME
---------- --------------------------------------------------------------------------------
1 D:\ORACLE\ORADATA\CRISS_DB\SYSTEM01.DBF
2 D:\ORACLE\ORADATA\CRISS_DB\SYSAUX01.DBF
3 D:\ORACLE\ORADATA\CRISS_DB\UNDOTBS01.DBF
4 D:\ORACLE\ORADATA\CRISS_DB\USERS01.DBF
5 D:\ORACLE\ORADATA\CRISS_DB\TEST01.DBF
6 D:\ORACLE\ORADATA\CRISS_DB\USER01.DBF
7 D:\ORACLE\ORADATA\CRISS_DB\USER02.DBF
8 D:\ORACLE\ORADATA\CRISS_DB\USER03.DBF
8 rows selected
散列分区指一个或多个列上应用一个散列函数,数根据该散列值存放在不同的分区中。
通过散列分区,可以将数据比较均匀地分布到各个分区中。
例:创建散列分区表
SQL> create table hash_orders
2 (order_id varchar2(10) constraint HOR_PK primary key
3 ,order_date date default sysdate
4 ,qty integer
5 ,payterms varchar2(10)
6 ,book_id number(6)
7 )
8 partition by hash(order_id)
9 ( partition hash_p1 tablespace user01
10 ,partition hash_p2 tablespace user02
11 );
Table created
SQL> insert into hash_orders select * from range_orders;
3 rows inserted
SQL> select rowid,h.* from hash_orders h;
ROWID ORDER_ID ORDER_DATE QTY PAYTERMS BOOK_ID
------------------ ---------- ----------- --------------------------------------- ---------- -------
AAADyIAAGAAAACNAAA 10002 2014/4/21 1 payterm_2 110745
AAADyIAAGAAAACNAAB 10003 2014/5/21 1 payterm_3 110945
AAADyJAAHAAAACNAAA 10001 2014/3/21 1 payterm_1 110345
order_id为 10002,10003的被存在AAG 6号文件上,第141个块上的 第1行和第2行
10001 存放在AAH 7号文件上
当表中某列的值只有几个的时候,可以采用列表分区,即指定在几个(根据列值个数)表空间中。
例:
SQL> create table list_orders
2 (order_id varchar2(10) constraint LOR_PK primary key
3 ,order_date date default sysdate
4 ,qty integer
5 ,payterms varchar2(10)
6 ,book_id number(6)
7 )
8 partition by list(payterms)
9 ( partition list_p1 values('payterm_1') tablespace user01
10 ,partition list_p2 values('payterm_2')tablespace user02
11 ,partition list_p3 values('payterm_3') tablespace user03
12 );
Table created
SQL> select rowid,l.* from list_orders l;
ROWID ORDER_ID ORDER_DATE QTY PAYTERMS BOOK_ID
------------------ ---------- ----------- --------------------------------------- ---------- -------
AAADyRAAGAAAACdAAA 10001 2014/3/21 1 payterm_1 110345
AAADySAAHAAAACdAAA 10002 2014/4/21 1 payterm_2 110745
AAADyTAAIAAAACVAAA 10003 2014/5/21 1 payterm_3 110945
基于范围分区和列表分区的组合 或 范围分区和散列分区的组合
SQL> create table comp_orders
2 (order_id varchar2(10) constraint COR_PK primary key
3 ,order_date date default sysdate
4 ,qty integer
5 ,payterms varchar2(10)
6 ,book_id number(6)
7 )
8 partition by range (order_date)
9 subpartition by list(payterms)
10 (partition p1 values less than (to_date('20140331','yyyymmdd'))
11 ( subpartition p1_sub1 values('payterm_1') tablespace user01
12 ,subpartition p1_sub2 values('payterm_2') tablespace user02
13 ,subpartition p1_sub3 values('payterm_3') tablespace user03
14 )
15 ,partition p2 values less than (to_date('20140430','yyyymmdd'))
16 ( subpartition p2_sub1 values('payterm_1') tablespace user04
17 ,subpartition p2_sub2 values('payterm_2') tablespace user05
18 ,subpartition p2_sub3 values('payterm_3') tablespace user06
19 )
20 ,partition p3 values less than (maxvalue) tablespace user07
21 )
22 ;
Table created
SQL> select rowid,c.* from comp_orders c;
ROWID ORDER_ID ORDER_DATE QTY PAYTERMS BOOK_ID
------------------ ---------- ----------- --------------------------------------- ---------- -------
AAADyZAAGAAAAClAAA 10004 2014/3/21 1 payterm_1 110345
AAADyZAAGAAAAClAAB 10001 2014/3/21 1 payterm_1 110345
AAADydAAKAAAACFAAA 10005 2014/4/21 1 payterm_2 110745
AAADydAAKAAAACFAAB 10002 2014/4/21 1 payterm_2 110745
AAADyfAAMAAAACFAAA 10006 2014/5/21 1 payterm_3 110945
AAADyfAAMAAAACFAAB 10003 2014/5/21 1 payterm_3 110945
6 rows selected
PAYTERMS字段值相同,且ORDER_DATE时间区间相同的数据被放到同一个表空间下面。
下面对比下PAYTERMS字段值相同,ORDER_DATE时间区间不同的结果
SQL> insert into comp_orders values('10007',to_date('20140421','yyyymmdd'),1,'payterm_1','113888');
1 row inserted
SQL> select rowid,c.* from comp_orders c;
ROWID ORDER_ID ORDER_DATE QTY PAYTERMS BOOK_ID
------------------ ---------- ----------- --------------------------------------- ---------- -------
AAADyZAAGAAAAClAAA 10004 2014/3/21 1 payterm_1 110345
AAADyZAAGAAAAClAAB 10001 2014/3/21 1 payterm_1 110345
AAADycAAJAAAACFAAA 10007 2014/4/21 1 payterm_1 113888
AAADydAAKAAAACFAAA 10005 2014/4/21 1 payterm_2 110745
AAADydAAKAAAACFAAB 10002 2014/4/21 1 payterm_2 110745
AAADyfAAMAAAACFAAA 10006 2014/5/21 1 payterm_3 110945
AAADyfAAMAAAACFAAB 10003 2014/5/21 1 payterm_3 110945
7 rows selected
ORDER_ID为10007的数据存放在了另一个表空间内(AAJ AAADyc[AAJ]AAAACFAAA)
间隔分区时oracle 11g release 1 以后版本中新增的特性,它是对范围分区的扩展,可以自动进行等距离范围的分区。
实验对比一下,间隔分区与范围分区有什么区别即可。
首先创建一个间隔分区,间隔分区以一个范围分区为‘起点’,并定义一个间隔,当有数据插入,依据该间隔为附加的数据创建新的分区。
SQL> create table intvl_orders
2 (order_id varchar2(10) constraint INO_PK primary key
3 ,order_date date default sysdate
4 ,qty integer
5 ,payterms varchar2(10)
6 ,book_id number(6)
7 )
8 partition by range (order_date)
9 interval(numtoyminterval(1,'MONTH'))
10 store in (user01,user02,user03)
11 (
12 partition p1 values less than (to_date('20140101','yyyymmdd'))
13 )
14 ;
Table created
注意:partition p1 values less than (to_date('20140101','yyyymmdd')) 起始是月初,不然会报错 ORA-14767: 无法使用现有上限指定此间隔
至于为什么,大家自行去查找下答案吧。
重新创建一个范围分区表并插入数据!
SQL> drop table range_orders purge;
Table dropped
SQL>
SQL> create table range_orders
2 (order_id varchar2(10) constraint OR_PK primary key
3 ,order_date date default sysdate
4 ,qty integer
5 ,payterms varchar2(10)
6 ,book_id number(6)
7 )
8 partition by range (order_date)
9 (partition p1 values less than (to_date('20140331','yyyymmdd')) tablespace user01,
10 partition p2 values less than (to_date('20140430','yyyymmdd')) tablespace user02,
11 partition p3 values less than (to_date('20140531','yyyymmdd')) tablespace user03
12 )
13 ;
Table created
SQL> insert into range_orders values ('10003',to_date('20140621','yyyymmdd'),1,'payterm_3',110945);
insert into range_orders values ('10003',to_date('20140621','yyyymmdd'),1,'payterm_3',110945)
ORA-14400: 插入的分区关键字未映射到任何分区
SQL> insert into range_orders values ('10003',to_date('20140521','yyyymmdd'),1,'payterm_3',110945);
1 row inserted
可以看到,新创建的范围分区表最大范围是20140531,当出入的数据超出分区范围就会报错,需要手工去做分区扩展。
下面再给间隔分区插入数据
SQL> insert into intvl_orders values ('10003',to_date('20140621','yyyymmdd'),1,'payterm_3',110945);
1 row inserted
我们插入用样超出分区范围的数据,数据插入成功了!
我们看一下前后两次的建表语句有什么变化
刚刚建好间隔分区表时候oracle内的建表语句:
-- Create table
create table INTVL_ORDERS
(
ORDER_ID VARCHAR2(10) not null,
ORDER_DATE DATE default sysdate,
QTY INTEGER,
PAYTERMS VARCHAR2(10),
BOOK_ID NUMBER(6)
)
partition by range (ORDER_DATE)
(
partition P1 values less than (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
)
);
-- Create/Recreate primary, unique and foreign key constraints
alter table INTVL_ORDERS
add constraint INO_PK primary key (ORDER_ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
插入数据后间隔分区表时候oracle内的建表语句:
-- Create table
create table INTVL_ORDERS
(
ORDER_ID VARCHAR2(10) not null,
ORDER_DATE DATE default sysdate,
QTY INTEGER,
PAYTERMS VARCHAR2(10),
BOOK_ID NUMBER(6)
)
partition by range (ORDER_DATE)
(
partition P1 values less than (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition SYS_P22 values less than (TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace USER01
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
)
);
-- Create/Recreate primary, unique and foreign key constraints
alter table INTVL_ORDERS
add constraint INO_PK primary key (ORDER_ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
结果自动扩展了一个分区!这也就是间隔分区的特点。
间隔分区会根据起始区间和一个间隔(之前我们设置的是一个月),插入数据时,会创建相应的分区,而不会因数据超出分区范围导致语句执行失败。
引用分区通过父表继承分区键,可以在逻辑上均分具有分子关系的表。分区键通过现有的父子关系解析,由现行的主键或外键约束实施。
SQL> create table range_books
2 (bookid number(6) primary key
3 ,bookname varchar2(40) not null
4 ,booktime date default sysdate
5 )
6 partition by range(booktime)
7 (
8 partition p1 values less than (to_date('20140331','yyyymmdd'))
9 ,partition p2 values less than (to_date('20140430','yyyymmdd'))
10 );
Table created
SQL> create table ref_order
2 (order_id varchar2(20) constraint RFO_PK primary key
3 ,order_date date default sysdate
4 ,book_id number(6) not null
5 ,constraint RFO_FK foreign key (book_id) references range_books(bookid)
6 )
7 partition by reference (RFO_FK)
8 ;
Table created
上面的实验,通过使用外键发现分区机制,外键RFO_PK指向父表range_books,因此,子表ref_orders就按照父表的分区方式进行相应的分区。
注意:创建引用分区表时,通过partition by reference 子句指定分区方法,其后括号中指定分区使用的约束。
外键约束引用的列必须具有not null约束。
=====================================================================================================================
分区表维护:
主要的就是 删除分区、增加分区、合并分区、移动分区、重命名、截断分区
删除分区: alter table range_orders drop partition p2;
增加分区: alter table range_orders add partition p2 values less than (to_date('20140430','yyyymmdd')) tablespace user02;
合并分区: alter table range_orders merge partition p1,p2 into partition p2;
移动分区: alter table range_orders move partition p2 tablespace user05;
重命名: alter table range_orders rename partition p2 to p1;
截断分区: alter table range_orders truncate partition p1;
分区表或索引在逻辑上是一个表或一个索引,但物理上是由多个物理分区组成的。
分区功能通过改善可管理性、性能、可用性,为各种应用系统带来了极大的好处。
分区功能的好处:
1.增强数据可用性:如果表的一个分区因故障或者维护而不能使用时,表的其余分区仍是可用的;
2.维护方便:独立管理多个分区,比维护单个大表要轻松;
3.均衡I/O:可以把不同分区映射到磁盘以平衡I/O,显著改善性能;
4.改善查询性能:对已分区对象的某些查询可以运行更快,因为搜索仅限于关心的分区;
分区表有哪些??
Oracle 11g 提供6种表分区方法:范围分区(range)、散列分区(hash)、列表分区(list)、符合分区、间隔分区、引用分区。
按表中某个列值的范围进行分区,根据该列的值决定将数据存储在哪个分区上。
创建范围分区需注意以下几点:
1.指明分区方法,分区列,和分区描述
2.每一个分区都有values less than子句
3.在最高分区中定义maxvalue,这个maxvalue值高区其他分区中的任何键值。
例:创建范围分区
create table range_orders
(order_id varchar2(10) constraint OR_PK primary key
,order_date date default sysdate
,qty integer
,payterms varchar2(10)
,book_id number(6)
)
partition by range (order_date)
(partition p1 values less than (to_date('20140331','yyyymmdd')) tablespace user01,
partition p2 values less than (to_date('20140430','yyyymmdd')) tablespace user02,
partition p3 values less than (to_date('20140531','yyyymmdd')) tablespace user03
)
;
SQL> insert into range_orders values ('10001',to_date('20140321','yyyymmdd'),1,'payterm_1',110345);
1 row inserted
SQL> insert into range_orders values ('10002',to_date('20140421','yyyymmdd'),1,'payterm_2',110745);
1 row inserted
SQL> insert into range_orders values ('10003',to_date('20140521','yyyymmdd'),1,'payterm_3',110945);
1 row inserted
SQL> commit;
Commit complete
SQL> select rowid,r.* from range_orders r;
ROWID ORDER_ID ORDER_DATE QTY PAYTERMS BOOK_ID
------------------ ---------- ----------- --------------------------------------- ---------- -------
AAADwpAAGAAAACFAAA 10001 2014/3/21 1 payterm_1 110345
AAADwqAAHAAAACFAAA 10002 2014/4/21 1 payterm_2 110745
AAADwrAAIAAAACFAAA 10003 2014/5/21 1 payterm_3 110945
---
可以看到AAG,AAH,AAI 分别代表了三条数据的文件号是6,7,8
备注:这个地方可以看下 http://blog.itpub.net/28929558/viewspace-1150766/ 了解rowid
验证下
SQL> select x.FILE#,x.NAME from v$datafile x;
FILE# NAME
---------- --------------------------------------------------------------------------------
1 D:\ORACLE\ORADATA\CRISS_DB\SYSTEM01.DBF
2 D:\ORACLE\ORADATA\CRISS_DB\SYSAUX01.DBF
3 D:\ORACLE\ORADATA\CRISS_DB\UNDOTBS01.DBF
4 D:\ORACLE\ORADATA\CRISS_DB\USERS01.DBF
5 D:\ORACLE\ORADATA\CRISS_DB\TEST01.DBF
6 D:\ORACLE\ORADATA\CRISS_DB\USER01.DBF
7 D:\ORACLE\ORADATA\CRISS_DB\USER02.DBF
8 D:\ORACLE\ORADATA\CRISS_DB\USER03.DBF
8 rows selected
散列分区指一个或多个列上应用一个散列函数,数根据该散列值存放在不同的分区中。
通过散列分区,可以将数据比较均匀地分布到各个分区中。
例:创建散列分区表
SQL> create table hash_orders
2 (order_id varchar2(10) constraint HOR_PK primary key
3 ,order_date date default sysdate
4 ,qty integer
5 ,payterms varchar2(10)
6 ,book_id number(6)
7 )
8 partition by hash(order_id)
9 ( partition hash_p1 tablespace user01
10 ,partition hash_p2 tablespace user02
11 );
Table created
SQL> insert into hash_orders select * from range_orders;
3 rows inserted
SQL> select rowid,h.* from hash_orders h;
ROWID ORDER_ID ORDER_DATE QTY PAYTERMS BOOK_ID
------------------ ---------- ----------- --------------------------------------- ---------- -------
AAADyIAAGAAAACNAAA 10002 2014/4/21 1 payterm_2 110745
AAADyIAAGAAAACNAAB 10003 2014/5/21 1 payterm_3 110945
AAADyJAAHAAAACNAAA 10001 2014/3/21 1 payterm_1 110345
order_id为 10002,10003的被存在AAG 6号文件上,第141个块上的 第1行和第2行
10001 存放在AAH 7号文件上
当表中某列的值只有几个的时候,可以采用列表分区,即指定在几个(根据列值个数)表空间中。
例:
SQL> create table list_orders
2 (order_id varchar2(10) constraint LOR_PK primary key
3 ,order_date date default sysdate
4 ,qty integer
5 ,payterms varchar2(10)
6 ,book_id number(6)
7 )
8 partition by list(payterms)
9 ( partition list_p1 values('payterm_1') tablespace user01
10 ,partition list_p2 values('payterm_2')tablespace user02
11 ,partition list_p3 values('payterm_3') tablespace user03
12 );
Table created
SQL> select rowid,l.* from list_orders l;
ROWID ORDER_ID ORDER_DATE QTY PAYTERMS BOOK_ID
------------------ ---------- ----------- --------------------------------------- ---------- -------
AAADyRAAGAAAACdAAA 10001 2014/3/21 1 payterm_1 110345
AAADySAAHAAAACdAAA 10002 2014/4/21 1 payterm_2 110745
AAADyTAAIAAAACVAAA 10003 2014/5/21 1 payterm_3 110945
基于范围分区和列表分区的组合 或 范围分区和散列分区的组合
SQL> create table comp_orders
2 (order_id varchar2(10) constraint COR_PK primary key
3 ,order_date date default sysdate
4 ,qty integer
5 ,payterms varchar2(10)
6 ,book_id number(6)
7 )
8 partition by range (order_date)
9 subpartition by list(payterms)
10 (partition p1 values less than (to_date('20140331','yyyymmdd'))
11 ( subpartition p1_sub1 values('payterm_1') tablespace user01
12 ,subpartition p1_sub2 values('payterm_2') tablespace user02
13 ,subpartition p1_sub3 values('payterm_3') tablespace user03
14 )
15 ,partition p2 values less than (to_date('20140430','yyyymmdd'))
16 ( subpartition p2_sub1 values('payterm_1') tablespace user04
17 ,subpartition p2_sub2 values('payterm_2') tablespace user05
18 ,subpartition p2_sub3 values('payterm_3') tablespace user06
19 )
20 ,partition p3 values less than (maxvalue) tablespace user07
21 )
22 ;
Table created
SQL> select rowid,c.* from comp_orders c;
ROWID ORDER_ID ORDER_DATE QTY PAYTERMS BOOK_ID
------------------ ---------- ----------- --------------------------------------- ---------- -------
AAADyZAAGAAAAClAAA 10004 2014/3/21 1 payterm_1 110345
AAADyZAAGAAAAClAAB 10001 2014/3/21 1 payterm_1 110345
AAADydAAKAAAACFAAA 10005 2014/4/21 1 payterm_2 110745
AAADydAAKAAAACFAAB 10002 2014/4/21 1 payterm_2 110745
AAADyfAAMAAAACFAAA 10006 2014/5/21 1 payterm_3 110945
AAADyfAAMAAAACFAAB 10003 2014/5/21 1 payterm_3 110945
6 rows selected
PAYTERMS字段值相同,且ORDER_DATE时间区间相同的数据被放到同一个表空间下面。
下面对比下PAYTERMS字段值相同,ORDER_DATE时间区间不同的结果
SQL> insert into comp_orders values('10007',to_date('20140421','yyyymmdd'),1,'payterm_1','113888');
1 row inserted
SQL> select rowid,c.* from comp_orders c;
ROWID ORDER_ID ORDER_DATE QTY PAYTERMS BOOK_ID
------------------ ---------- ----------- --------------------------------------- ---------- -------
AAADyZAAGAAAAClAAA 10004 2014/3/21 1 payterm_1 110345
AAADyZAAGAAAAClAAB 10001 2014/3/21 1 payterm_1 110345
AAADycAAJAAAACFAAA 10007 2014/4/21 1 payterm_1 113888
AAADydAAKAAAACFAAA 10005 2014/4/21 1 payterm_2 110745
AAADydAAKAAAACFAAB 10002 2014/4/21 1 payterm_2 110745
AAADyfAAMAAAACFAAA 10006 2014/5/21 1 payterm_3 110945
AAADyfAAMAAAACFAAB 10003 2014/5/21 1 payterm_3 110945
7 rows selected
ORDER_ID为10007的数据存放在了另一个表空间内(AAJ AAADyc[AAJ]AAAACFAAA)
间隔分区时oracle 11g release 1 以后版本中新增的特性,它是对范围分区的扩展,可以自动进行等距离范围的分区。
实验对比一下,间隔分区与范围分区有什么区别即可。
首先创建一个间隔分区,间隔分区以一个范围分区为‘起点’,并定义一个间隔,当有数据插入,依据该间隔为附加的数据创建新的分区。
SQL> create table intvl_orders
2 (order_id varchar2(10) constraint INO_PK primary key
3 ,order_date date default sysdate
4 ,qty integer
5 ,payterms varchar2(10)
6 ,book_id number(6)
7 )
8 partition by range (order_date)
9 interval(numtoyminterval(1,'MONTH'))
10 store in (user01,user02,user03)
11 (
12 partition p1 values less than (to_date('20140101','yyyymmdd'))
13 )
14 ;
Table created
注意:partition p1 values less than (to_date('20140101','yyyymmdd')) 起始是月初,不然会报错 ORA-14767: 无法使用现有上限指定此间隔
至于为什么,大家自行去查找下答案吧。
重新创建一个范围分区表并插入数据!
SQL> drop table range_orders purge;
Table dropped
SQL>
SQL> create table range_orders
2 (order_id varchar2(10) constraint OR_PK primary key
3 ,order_date date default sysdate
4 ,qty integer
5 ,payterms varchar2(10)
6 ,book_id number(6)
7 )
8 partition by range (order_date)
9 (partition p1 values less than (to_date('20140331','yyyymmdd')) tablespace user01,
10 partition p2 values less than (to_date('20140430','yyyymmdd')) tablespace user02,
11 partition p3 values less than (to_date('20140531','yyyymmdd')) tablespace user03
12 )
13 ;
Table created
SQL> insert into range_orders values ('10003',to_date('20140621','yyyymmdd'),1,'payterm_3',110945);
insert into range_orders values ('10003',to_date('20140621','yyyymmdd'),1,'payterm_3',110945)
ORA-14400: 插入的分区关键字未映射到任何分区
SQL> insert into range_orders values ('10003',to_date('20140521','yyyymmdd'),1,'payterm_3',110945);
1 row inserted
可以看到,新创建的范围分区表最大范围是20140531,当出入的数据超出分区范围就会报错,需要手工去做分区扩展。
下面再给间隔分区插入数据
SQL> insert into intvl_orders values ('10003',to_date('20140621','yyyymmdd'),1,'payterm_3',110945);
1 row inserted
我们插入用样超出分区范围的数据,数据插入成功了!
我们看一下前后两次的建表语句有什么变化
刚刚建好间隔分区表时候oracle内的建表语句:
-- Create table
create table INTVL_ORDERS
(
ORDER_ID VARCHAR2(10) not null,
ORDER_DATE DATE default sysdate,
QTY INTEGER,
PAYTERMS VARCHAR2(10),
BOOK_ID NUMBER(6)
)
partition by range (ORDER_DATE)
(
partition P1 values less than (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
)
);
-- Create/Recreate primary, unique and foreign key constraints
alter table INTVL_ORDERS
add constraint INO_PK primary key (ORDER_ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
插入数据后间隔分区表时候oracle内的建表语句:
-- Create table
create table INTVL_ORDERS
(
ORDER_ID VARCHAR2(10) not null,
ORDER_DATE DATE default sysdate,
QTY INTEGER,
PAYTERMS VARCHAR2(10),
BOOK_ID NUMBER(6)
)
partition by range (ORDER_DATE)
(
partition P1 values less than (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition SYS_P22 values less than (TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace USER01
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
)
);
-- Create/Recreate primary, unique and foreign key constraints
alter table INTVL_ORDERS
add constraint INO_PK primary key (ORDER_ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
结果自动扩展了一个分区!这也就是间隔分区的特点。
间隔分区会根据起始区间和一个间隔(之前我们设置的是一个月),插入数据时,会创建相应的分区,而不会因数据超出分区范围导致语句执行失败。
引用分区通过父表继承分区键,可以在逻辑上均分具有分子关系的表。分区键通过现有的父子关系解析,由现行的主键或外键约束实施。
SQL> create table range_books
2 (bookid number(6) primary key
3 ,bookname varchar2(40) not null
4 ,booktime date default sysdate
5 )
6 partition by range(booktime)
7 (
8 partition p1 values less than (to_date('20140331','yyyymmdd'))
9 ,partition p2 values less than (to_date('20140430','yyyymmdd'))
10 );
Table created
SQL> create table ref_order
2 (order_id varchar2(20) constraint RFO_PK primary key
3 ,order_date date default sysdate
4 ,book_id number(6) not null
5 ,constraint RFO_FK foreign key (book_id) references range_books(bookid)
6 )
7 partition by reference (RFO_FK)
8 ;
Table created
上面的实验,通过使用外键发现分区机制,外键RFO_PK指向父表range_books,因此,子表ref_orders就按照父表的分区方式进行相应的分区。
注意:创建引用分区表时,通过partition by reference 子句指定分区方法,其后括号中指定分区使用的约束。
外键约束引用的列必须具有not null约束。
=====================================================================================================================
分区表维护:
主要的就是 删除分区、增加分区、合并分区、移动分区、重命名、截断分区
删除分区: alter table range_orders drop partition p2;
增加分区: alter table range_orders add partition p2 values less than (to_date('20140430','yyyymmdd')) tablespace user02;
合并分区: alter table range_orders merge partition p1,p2 into partition p2;
移动分区: alter table range_orders move partition p2 tablespace user05;
重命名: alter table range_orders rename partition p2 to p1;
截断分区: alter table range_orders truncate partition p1;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28929558/viewspace-1153474/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28929558/viewspace-1153474/