表分区的优点:
改善表的查询性能;表更容易管理;便于备份和恢复;提高数据安全性。
注意:要分区的表不能具有Long和Long Raw数据类型的列。
四种分区方法:
1、范围分区:根据表的某个列或一组列的值范围,决定将该数据存储在哪个分区上。
语法如下:
在Create Table语句后增加
PARTITION BY RANGE(column_name)
(
PARTITION part1 VALUE LESS THAN (range1) [TABLESPACE tbs1],
PARTITION part2 VALUE LESS THAN (range2) [TABLESPACE tbs2],
....
PARTITION partN VALUE LESS THAN (MAXVALUE) [TABLESPACE tbsN]
);
其中:column_name是以其为基础创建范围分区的列,特定行的该列值称为分区键。
part1...partN是分区的名称。
range1...MAXVALUE是分区的边界值。
tbs1...tbsN是分区所在的表空间,TABLESPACE子句是可选项。
示例3:
create table t_emp
(
empno number(4),
ename varchar2(30),
sal number
)
partition by range(empno)
(
partition e1 values less than (1000) tablespace emp1, 也可以 than (to_date('2003-01-01','yyyy-mm-dd'))
partition e2 values less than (2000) tablespace emp2, 也可以 than (to_date('2004-01-01','yyyy-mm-dd'))
partition e3 values less than (maxvalue) tablespace emp3
);
2、散列分区:语法有两种如下
PARTITION BY HASH(column_name)
PARTITIONS number_of_partitions [STORE IN (tablespace_list)];
或
PARTITION BY HASH(column_name)
(
PARTITION part1 [TABLESPACE tbs1],
PARTITION part2 [TABLESPACE tbs2],
...
PARTITION partN [TABLESPACE tbsN]
);
其中:column_name是以其为基础创建散列分区的列。
number_of_partitions是散列分区的数目,使用这种方法系统会自动生成分区的名称。
tablespace_list指定分区使用的表空间,如果分区数目比表空间的数目多,分区将会以循环的方式分配到表空间中。
part1...partN是分区的名称。
tbs1...tbsN是分区所在的表空间,TABLESPACE子句是可选项。
示例4:自动分配4个散列分区,
可以使用select partition_name,HIGH_VALUE from user_tab_partitions where table_name=upper('t_emp')查询分区名
create table t_emp
(
empno number(4),
ename varchar2(30),
sal number
)
partition by hash (empno)
partitions 4;
3、复合分区:是范围分区和散列分区的结合。在创建复合分区时,先根据范围对数据进行分区,然后在这些分区内创建散列子分区。
语法如下:
PARTITION BY RANGE(column_name1)
SUBPARTITION BY HASH(column_name2)
SUBPARTITIONS number_of_partitions [STORE IN (tablespace_list)]
(
PARTITION part1 VALUE LESS THAN (range1) [TABLESPACE tbs1],
PARTITION part2 VALUE LESS THAN (range2) [TABLESPACE tbs2],
....
PARTITION partN VALUE LESS THAN (MAXVALUE) [TABLESPACE tbsN]
);
其中:column_name1是以其为基础创建范围分区的列。
column_name2是以其为基础创建散列分区的列。
number_of_partitions是要创建的子分区的数目。
part1...partN是分区的名称。
range1...MAXVALUE是范围分区的边界值
示例5:将雇员表先按照雇佣时间hiredate进行了范围分区,然后再把每个分区分为2个子hash分区,此表一共是6个分区。
create table t_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)
);
4、列表分区:此分区允许用户明确地控制行到分区的映射。
语法如下:
PARTITION BY LIST(column_name)
(
PARTITION part1 VALUES (values_list1),
PARTITION part2 VALUES (values_list2),
....
PARTITION partN VALUES (DEFAULT)
);
其中:column_name是以其为基础创建列表分区的列。
part1...partN是分区的名称。
values_list是对应分区的分区键值的列表。
DEFAULT关键字允许存储前面的分区不能存储的记录。
示例6:
create table t_emp1
(
empno number(4),
ename varchar2(30),
location varchar2(30)
)
partition by list (location)
(
partition e1 values ('北京'),
partition e2 values ('上海','天津','重庆'),
partition e3 values ('广东','福建'),
);
要查询表分区中的数据行:select * from table_name PARTITION(p1); 注:p1是分区名。
分区维护操作:
1、添加分区:ALTER...ADD PARTITION语句用于在现有的最后一个分区之后添加新的分区。
示例7:演示如何将名为E4的新分区添加到示例3中创建的t_emp表。
ALTER TABLE t_emp ADD PARTITION E4 VALUES LESS THAN (3000);
在此请注意:上例公适用于已使用特定的键值定义了最后一个分区的表。如果要在表的开始或中间位置添加分区,或者最高分区的分区
边界是MAXVALUE,则应使用SPLIT PARTITION语句。
2、删除分区:使用ALTER TABLE...DROP PARTITION语句。
示例8:演示了如何删除t_emp表的E4分区。删除分区时,分区中的数据也随之删除。
ALTER TABLE t_emp DROP PARTITION E4;
3、截断分区:使用ALTER TABLE...TRUNCATE PARTITION语句来截断分区,只删除表分区中的所有记录。
示例9:演示了如何删除t_emp表中e3的分区的所有记录。
ALTER TABLE t_emp TRUNCATE PARTITION e3;
4、合并分区:可以将范围分区或复合分区表的两个相邻分区连接起来。结果分区将继承被合并的两个分区的较高上界。
语法如下:
ALTER TABLE table_name MERGE PARTITIONS partitions_name,partitions_name INTO PARTITION partition_name;
示例10:演示了如何将e1和e2合并成一个e2分区。
ALTER TABLE t_emp MERGE PARTITIONS e1,e2 INTO PARTITION e2;
5、拆分分区:使用SPLIT PARTITION语句在表的开头或中间添加分区。拆分分区允许用户将一个分区拆分为两个分区。
语法如下:
ALTER TABLE table_name SPLIY PARTITION partition_name AT (value) INTO (PARTITION partition1,PARTITION partition2);
示例11:演示了如何将t_emp表中的e3分区拆分为e31和e32两个分区。
ALTER TABLE t_emp SPLIT PARTITION e3 AT (Date '2005-01-01') INTO (PARTITION e31,PARTITION e32);
6、重新命名拆分后的分区:
ALTER TABLE t_emp RENAME PARTITION e31 TO P3;
ALTER TABLE t_emp RENAME PARTITION e32 TO P4;
可以查询字典视图user_tab_partitions来查看用户所创建的分区的详细信息
示例12:select table_name,partition_name,high_value from user_tab_partitions;
可以查询字典视图dba_tab_subpartitions来查看用户所创建的子分区的详细信息
示例13:select table_name,partition_name,subpartition_name from dba_tab_subpartitions;
原文链接:http://blog.sina.com.cn/s/blog_4b2778c701007tg1.html