1:分区的方式
1>区间分区RANGE
--创建分区
create table range_love2008
(datadate varchar2(8),
userid number,
money number)
partition by range(datadate)
(partition p1 values less than ('20080101'),
partition p2 values less than ('20080401'),
partition p3 values less than (maxvalue)
)
tablespace test_p001_d;
insert into range_love2008 values('20071101',1001,50);
insert into range_love2008 values('20071030',1002,50);
insert into range_love2008 values('20071201',1003,50);
insert into range_love2008 values('20080101',1004,50);
insert into range_love2008 values('20080102',1001,50);
insert into range_love2008 values('20080401',1003,50);
insert into range_love2008 values('20080402',1004,50);
insert into range_love2008 values('20080501',1005,50);
insert into range_love2008 values('20080601',1001,50);
SQL> select * from range_love2008 partition(p1);
DATADATE USERID MONEY
-------- ---------- ----------
20071101 1001 50
20071030 1002 50
20071201 1003 50
SQL> select * from range_love2008 partition(p2);
DATADATE USERID MONEY
-------- ---------- ----------
20080101 1004 50
20080102 1001 50
SQL> select * from range_love2008 partition(p3);
DATADATE USERID MONEY
-------- ---------- ----------
20080401 1003 50
20080402 1004 50
20080501 1005 50
20080601 1001 50
SQL>
--创建LOCAL索引
create index inx_range on range_love2008(datadate) tablespace users local
也可以为每个分区指定一个表空间
create index inx_range on range_love2008(datadate) local(partition p0 tablespace users,partition p1 tablespace users,partition p2 tablespace users,partition p3 tablespace users)
如果建立分区索引时没有指定默认的表空间,那么新增的索引分区所在表空间和新增表分区所在表空间保持一致。
--添加分区
对于RANGE分区表,要想添加一个分区,必须采用在分区表的最后添加一个分区。
[例如]:
SQL> alter table range_love2008 add partition p4 values less than ('20071101');
alter table range_love2008 add partition p4 values less than ('20071101')
*
第 1 行出现错误:
ORA-14074: 分区界限必须调整为高于最后一个分区界限
如果为表创建了本地分区索引,那么在ADD PARTITION 后会自动增加一个索引分区的。
[提问]:
如果想实现对第一个分区前面在添加一个分区该怎么办哪?
[解答]:
那就采用SPLIT分区。
[提问]:
如果想实现删除第一个分区,但是要保存它的数据到第二个分区内
[解答]:
那就采用MERGE两个分区
--分割分区
alter table range_love2008 split partition p1 at ('20071101') into (partition p0,partition p1);
SQL> select * from range_love2008 partition(p0);
DATADATE USERID MONEY
-------- ---------- ----------
20071030 1002 50
SQL> select * from range_love2008 partition(p1);
DATADATE USERID MONEY
-------- ---------- ----------
20071101 1001 50
20071201 1003 50
--合并分区MERGE
alter table range_love2008 merge partitions p0,p1 into partition p1;
这个语句执行后,P1分区的LOCAL索引会UNUSABLE,可以在合并分区的时候指定UPDATE INDEXES
而且如果没有指定表空间的话,P1分区的LOCAL索引的表空间也变了,变成P1分区的表空间了,可以在partition p1后面添加tablespace users。
--删除分区
alter table range_love2008 drop partition p2;
--移动分区
alter table range_love2008 move partition p2 tablespace users;
会对LOCAL索引造成失效、需要REBUILD索引
alter index inx_range rebuild partition p2;
--colease partition是只针对HASH分区的、TRUNCATE PARTITION这个都很简单、EXCHANGE PARTITION这个也不说了。
--RENAME PARTITION
分区的功能:
ADD PARTITION、SPLIT PARTITION、DROP PARTITION、TRUNCATE PARTITION、EXCHANGE PARTITION、
MERGE PARTITION、MOVE PARTITION、COALESCE PARTITION、RENAME PARTITION
2>散列分区HASH
[创建方式一]:
create table hash_love2008
(id number,
na number)
partition by hash(id)
(partition p1,partition p2)
tablespace list_tablespace;
--partition可以为每个分区指定不同的表空间
[创建方式二]:
create table hash_love2008
(id number,
na number)
partition by hash(id)
partitions 2
tablespace list_tablespace;
[创建方式三]:
create table hash_love2008
(id number,
na number)
partition by hash(id)
partitions 2
store in(list_tablespace,users);
insert into hash_love2008 values (2,1);
insert into hash_love2008 values (3,1);
insert into hash_love2008 values (4,1);
insert into hash_love2008 values (5,1);
insert into hash_love2008 values (6,1);
insert into hash_love2008 values (7,1);
--新增分区
alter table hash_love2008 add partition;
因为增加分区会使部分LOCAL索引失效,其实只是会对产生数据变化的分区的LOCAL索引失效,具体哪个分区的LOCAL索引会失效,是根据HASH算法确定的。
如果是按照第三种方法创建分区,那么新增分区的时候,新增分区的表空间是用户默认的表空间,而默认增加的LOCAL索引的表空间也是用户默认的表空间。
--接合分区COALESCE PARTITION
alter table hash_love2008 coalesce partition;
这样做的话会把hash_love2008的分区数减一,一般是从末尾去掉。
不能使用SPLIT PARTITION。
3>列表分区LIST
create table list_love2008
(id number,
u_id number,
no1 number
)
partition by list(id)
(partition p1 values(1,3,5),
partition p2 values(2,4,6)
)
tablespace list_tablespace;
insert into list_love2008 values(1,1,3);
insert into list_love2008 values(1,2,3);
insert into list_love2008 values(2,3,3);
insert into list_love2008 values(2,4,3);
insert into list_love2008 values(3,5,3);
insert into list_love2008 values(3,6,3);
insert into list_love2008 values(4,7,3);
insert into list_love2008 values(6,8,3);
insert into list_love2008 values(5,9,3);
create index inx_list on list_love2008(id) tablespace users local;
--分割分区
alter table list_love2008 split partition p1 values(3) into (partition p1,partition p0)
这句话的作用是把分区P1分割成两个分区P0和P1,其中P1中的ID为3、P0中的ID为1和5。
这个语句执行后,P1和新增加的P0分区所在的LOCAL索引都将会UNUSABLE。
--合并分区
alter table list_love2008 merge partitions p0,p1 into partition p1;
--截断分区
alter table list_love2008 truncate partition p1;
--重命名分区
alter table list_love2008 rename partition p2 to p_2;
重命名分区不会影响LOCAL索引。
--交换分区
alter table list_love2008 exchange partition p1 with table list_tab;
如果普通非分区表中存在不符合这个分区的数据的话,可以用如下两种方法做:
[方法一]:
alter table list_love2008 exchange partition p1 with table list_tab without validation;
不对数据进行验证,但是这样做就违反了分区表的规则。
交换分区必须是和一个普通非分区表交换数据,一定要知道是交换数据,由于这个属于DDL操作,所以速度比较快。
一定要确定list_tab表中的数据符合list_love2008 partition(p1)的定义规则,否则会报错:
ORA-14099: 未对指定分区限定表中的所有行,除非加上without validation子句,但是那样就会不符和分区表的定义了,不建议使用。
4>区间散列分区
--colease partition
5>区间列表分区
1>区间分区RANGE
--创建分区
create table range_love2008
(datadate varchar2(8),
userid number,
money number)
partition by range(datadate)
(partition p1 values less than ('20080101'),
partition p2 values less than ('20080401'),
partition p3 values less than (maxvalue)
)
tablespace test_p001_d;
insert into range_love2008 values('20071101',1001,50);
insert into range_love2008 values('20071030',1002,50);
insert into range_love2008 values('20071201',1003,50);
insert into range_love2008 values('20080101',1004,50);
insert into range_love2008 values('20080102',1001,50);
insert into range_love2008 values('20080401',1003,50);
insert into range_love2008 values('20080402',1004,50);
insert into range_love2008 values('20080501',1005,50);
insert into range_love2008 values('20080601',1001,50);
SQL> select * from range_love2008 partition(p1);
DATADATE USERID MONEY
-------- ---------- ----------
20071101 1001 50
20071030 1002 50
20071201 1003 50
SQL> select * from range_love2008 partition(p2);
DATADATE USERID MONEY
-------- ---------- ----------
20080101 1004 50
20080102 1001 50
SQL> select * from range_love2008 partition(p3);
DATADATE USERID MONEY
-------- ---------- ----------
20080401 1003 50
20080402 1004 50
20080501 1005 50
20080601 1001 50
SQL>
--创建LOCAL索引
create index inx_range on range_love2008(datadate) tablespace users local
也可以为每个分区指定一个表空间
create index inx_range on range_love2008(datadate) local(partition p0 tablespace users,partition p1 tablespace users,partition p2 tablespace users,partition p3 tablespace users)
如果建立分区索引时没有指定默认的表空间,那么新增的索引分区所在表空间和新增表分区所在表空间保持一致。
--添加分区
对于RANGE分区表,要想添加一个分区,必须采用在分区表的最后添加一个分区。
[例如]:
SQL> alter table range_love2008 add partition p4 values less than ('20071101');
alter table range_love2008 add partition p4 values less than ('20071101')
*
第 1 行出现错误:
ORA-14074: 分区界限必须调整为高于最后一个分区界限
如果为表创建了本地分区索引,那么在ADD PARTITION 后会自动增加一个索引分区的。
[提问]:
如果想实现对第一个分区前面在添加一个分区该怎么办哪?
[解答]:
那就采用SPLIT分区。
[提问]:
如果想实现删除第一个分区,但是要保存它的数据到第二个分区内
[解答]:
那就采用MERGE两个分区
--分割分区
alter table range_love2008 split partition p1 at ('20071101') into (partition p0,partition p1);
SQL> select * from range_love2008 partition(p0);
DATADATE USERID MONEY
-------- ---------- ----------
20071030 1002 50
SQL> select * from range_love2008 partition(p1);
DATADATE USERID MONEY
-------- ---------- ----------
20071101 1001 50
20071201 1003 50
--合并分区MERGE
alter table range_love2008 merge partitions p0,p1 into partition p1;
这个语句执行后,P1分区的LOCAL索引会UNUSABLE,可以在合并分区的时候指定UPDATE INDEXES
而且如果没有指定表空间的话,P1分区的LOCAL索引的表空间也变了,变成P1分区的表空间了,可以在partition p1后面添加tablespace users。
--删除分区
alter table range_love2008 drop partition p2;
--移动分区
alter table range_love2008 move partition p2 tablespace users;
会对LOCAL索引造成失效、需要REBUILD索引
alter index inx_range rebuild partition p2;
--colease partition是只针对HASH分区的、TRUNCATE PARTITION这个都很简单、EXCHANGE PARTITION这个也不说了。
--RENAME PARTITION
分区的功能:
ADD PARTITION、SPLIT PARTITION、DROP PARTITION、TRUNCATE PARTITION、EXCHANGE PARTITION、
MERGE PARTITION、MOVE PARTITION、COALESCE PARTITION、RENAME PARTITION
2>散列分区HASH
[创建方式一]:
create table hash_love2008
(id number,
na number)
partition by hash(id)
(partition p1,partition p2)
tablespace list_tablespace;
--partition可以为每个分区指定不同的表空间
[创建方式二]:
create table hash_love2008
(id number,
na number)
partition by hash(id)
partitions 2
tablespace list_tablespace;
[创建方式三]:
create table hash_love2008
(id number,
na number)
partition by hash(id)
partitions 2
store in(list_tablespace,users);
insert into hash_love2008 values (2,1);
insert into hash_love2008 values (3,1);
insert into hash_love2008 values (4,1);
insert into hash_love2008 values (5,1);
insert into hash_love2008 values (6,1);
insert into hash_love2008 values (7,1);
--新增分区
alter table hash_love2008 add partition;
因为增加分区会使部分LOCAL索引失效,其实只是会对产生数据变化的分区的LOCAL索引失效,具体哪个分区的LOCAL索引会失效,是根据HASH算法确定的。
如果是按照第三种方法创建分区,那么新增分区的时候,新增分区的表空间是用户默认的表空间,而默认增加的LOCAL索引的表空间也是用户默认的表空间。
--接合分区COALESCE PARTITION
alter table hash_love2008 coalesce partition;
这样做的话会把hash_love2008的分区数减一,一般是从末尾去掉。
不能使用SPLIT PARTITION。
3>列表分区LIST
create table list_love2008
(id number,
u_id number,
no1 number
)
partition by list(id)
(partition p1 values(1,3,5),
partition p2 values(2,4,6)
)
tablespace list_tablespace;
insert into list_love2008 values(1,1,3);
insert into list_love2008 values(1,2,3);
insert into list_love2008 values(2,3,3);
insert into list_love2008 values(2,4,3);
insert into list_love2008 values(3,5,3);
insert into list_love2008 values(3,6,3);
insert into list_love2008 values(4,7,3);
insert into list_love2008 values(6,8,3);
insert into list_love2008 values(5,9,3);
create index inx_list on list_love2008(id) tablespace users local;
--分割分区
alter table list_love2008 split partition p1 values(3) into (partition p1,partition p0)
这句话的作用是把分区P1分割成两个分区P0和P1,其中P1中的ID为3、P0中的ID为1和5。
这个语句执行后,P1和新增加的P0分区所在的LOCAL索引都将会UNUSABLE。
--合并分区
alter table list_love2008 merge partitions p0,p1 into partition p1;
--截断分区
alter table list_love2008 truncate partition p1;
--重命名分区
alter table list_love2008 rename partition p2 to p_2;
重命名分区不会影响LOCAL索引。
--交换分区
alter table list_love2008 exchange partition p1 with table list_tab;
如果普通非分区表中存在不符合这个分区的数据的话,可以用如下两种方法做:
[方法一]:
alter table list_love2008 exchange partition p1 with table list_tab without validation;
不对数据进行验证,但是这样做就违反了分区表的规则。
交换分区必须是和一个普通非分区表交换数据,一定要知道是交换数据,由于这个属于DDL操作,所以速度比较快。
一定要确定list_tab表中的数据符合list_love2008 partition(p1)的定义规则,否则会报错:
ORA-14099: 未对指定分区限定表中的所有行,除非加上without validation子句,但是那样就会不符和分区表的定义了,不建议使用。
4>区间散列分区
--colease partition
5>区间列表分区
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/728254/viewspace-366022/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/728254/viewspace-366022/