oracle分区表使用
来源:互联网 发布:仙女网络语言什么意思 编辑:程序博客网 时间:2024/06/06 01:09
原文链接:http://blog.itpub.net/24236575/viewspace-1116110/
分区概述:
分区优点:
1 、增强可用性:如果表的一个分区由于系统故障而不能使用,表的其余好的分区仍然可以使用;
2 、减少关闭时间:如果系统故障只影响表的一部分分区,那么只有这部分分区需要修复,故能比整个大表修复花的时间更少;
3 、维护轻松:如果需要重建表,独立管理每个分区比管理单个大表要轻松得多;
4 、均衡I/O:可以把表的不同分区分配到不同的磁盘来平衡I/O改善性能;
5 、改善性能:对大表的查询、增加、修改等操作可以分解到表的不同分区来并行执行,可使运行速度更快;
6 、分区对用户透明,最终用户感觉不到分区的存在。
关于何时应该进行分区,Oracle有如下建议:
(1)Tables greater than 2GB should always be considered for partitioning.
(2)Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.
一、常见分区:
1、范围分区
创建表空间,供分区使用:
create tablespace ts_emp1 datafile 'D:APPPETERORADATAWJIAO s_emp1.dbf' size 1m;
create tablespace ts_emp2 datafile 'D:APPPETERORADATAWJIAO s_emp2.dbf' size 1m;
create tablespace ts_emp3 datafile 'D:APPPETERORADATAWJIAO s_emp3.dbf' size 1m;
创建分区表:
create table emp
(
)
partition by range(empno)
(
);
观察表内分区信息
HR@wjiao>select segment_name,segment_type,partition_name from user_segments where segment_name='EMP';
SEGMENT_NAME
------------------------------ ------------------ ------------------------------
EMP
EMP
EMP
模拟数据:
insert into emp values (100,'Tom',1000);
insert into emp values (500,'Peter',2000);
insert into emp values (1000,'Scott',3000);
insert into emp values (1999,'Bill',4000);
insert into emp values (5000,'Gates',6000);
commit;
观察记录分布
HR@wjiao>select * from emp;
---------- ---------- ----------
HR@wjiao>select * from emp partition(p1000);
---------- ---------- ----------
HR@wjiao>select * from emp partition(p2000);
---------- ---------- ----------
HR@wjiao>select * from emp partition(pmax);
---------- ---------- ----------
维护分区
exp导出指定表分区
C:>exp userid=hr/hr file=d:apppeteroradataemp_p1000.dmp tables=emp:p1000
imp导入指定表分区
C:>imp userid=hr/hr file=d:apppeteroradataemp_p1000.dmp tables=(emp:p1000) ignore=y
分区不存在,则插入到p2000
ignore=y必须要有,忽略创建错误,否则导入失败
truncate指定分区
alter table emp truncate partition pmax;
drop指定分区
alter table emp drop partition pmax;
超出范围的数据,报错
HR@wjiao>insert into emp values (5000,'Gates',6000);
insert into emp values (5000,'Gates',6000)
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
加入分区
alter table emp add partition pmax values less than(maxvalue);
HR@wjiao>insert into emp values (5000,'Gates',6000);
1 row created.
2、hash分区
hash分区最主要的机制是根据hash算法来计算具体某条纪录应该插入到哪个分区中
(问:hash算法是干什么的?呵呵,只能去看看数据结构了)
hash算法中最重要的是hash函数,Oracle中如果你要使用hash分区,只需指定分区的数量即可
建议分区的数量采用2的n次方,这样可以使得各个分区间数据分布更加均匀
hash列上数值不能有太多的重复值,否则会导致数据分布不均匀
drop table emp purge;
create table emp
(
)
partition by hash(empno)
partitions 8 store in (users)
;
--插入数据,递增序列,分区数据分布均匀
set timing on;
begin
end;
/
HR@wjiao>analyze table emp compute statistics for table for all columns for all indexes;
HR@wjiao>select partition_name,count(*)
PARTITION_NAME
------------------------------ ----------
SYS_P82
SYS_P79
SYS_P81
SYS_P77
SYS_P78
SYS_P80
SYS_P83
SYS_P84