分区方法: 范围分区、散列分区、列表分区、复合分区
(1)范围分区 :以表中的一个列或一组列的值得范围分区
例如:
CREATE table sales2
(
product_id varchar2(5),
sales_date date not null,
sales_cost number(10)
)
partition by range(sales_date)
(
partition p1 values less then (date '2003-01-01'),
partition p2 values less then (date '2004-01-01'),
partition p3 values less then (maxvalue)
);
(2) 散列分区:允许用户对不具有逻辑范围的数据进行分区
通过在分区键上执行 HASH函数决定存储的分区
将数据平均的分布到不同的分区
散列分区语法: partition by hash(column_name)
partitions number_of_partitions;
或
partition by hash (colum_name)
(partition part1 [tablespace tbs1],
partition part2 [tablespace tbs2],
......
partition partN [tablespace tbsN]};
例如:
create table Employy
(
Employee_ID varchar2(5),
Employee_Name varchar2(20),
Department varchar2(10)
)
partition by hash (Department)
{
partition D1,
partition D2,
partition D3
}
--------------
create table employee
( emp_id number(4),
emp_name varchar2(15),
emp_address varchar2(15),
department varchar2(10)
)
partition by hash (department)
partitions 4;
(3) 列表分区:允许用户将不相关的数据组织在一起
例如:create table Employee
(Emp_ID number(4),
Emp_Name varchar2(14),
Emp_Address varchar2(15)
)
partition by list (Emp_Address)
(
partition north values('芝加哥'),
partition west values('旧金山','洛杉矶'),
partition south values('亚特兰大','达拉斯','休斯敦'),
partition east values('纽约','波斯顿')
);
(4) 复合分区:范围分区与散列分区或列表分区的组合
例如:
create table sales
(product_id varchar2(5),
sales_date date not null,
sales_cost number(10)
)
partition by range(sales_date)
subpartition by hash (product_id)
subpartitions 5
(partition s1 values less then (to_date('01/4月/2001','DD/MON/YYYY'),
partition s2 values less then (to_date('01/7月/2001','DD/MON/YYYY'),
partition s3 values less then (to_date('01/9月/2001','DD/MON/YYYY'),
partition s4 values less then (maxvalue)
);
===================
分区的查询,修改,删除
insert into sales3 values('p001','02-3月-2001',2000);
select * from sales3 partition(p3);
delete from sales3 partition(p2);
=================================
分区维护操作:添加分区、删除分区、截断分区、合并分区、拆分分区
(1)添加分区 ---在最后一个分区之后添加新分区
alter table sales add partition p4 values less then (4000);
(2) 删除分区 ---删除一个指定的分区,分区的数据也随之删除
alter table sales drop partition p4;
(3)截断分区 ---删除制定分区中的所有记录
alter table sales truncate partition p3;
(4)合并分区 ---将范围分区或复合分区的两个相邻分区连接起来
alter table sales merge partitions s1,s2 into partition s2;
(5)拆分分区---将一个大分区中的纪录拆分成两个分区中
alter table sales split partition p2 at(1500) into (partition p21,partition p22);