Oracle中大表分区的管理操作

为了简化数据库大表的管理,ORACLE8推出了分区选项。分区将表分离在若干不同的表空间上,用分而治之的方法来支撑无限膨胀的大表,给大表在物理一级的可管理性。将大表分割成较小的分区可以改善表的维护、备份、恢复、事务及查询性能。
分区的优点:
1 、增强可用性:如果表的一个分区由于系统故障而不能使用,表的其余好的分区仍然可以使用;
2 、减少关闭时间:如果系统故障只影响表的一部分分区,那么只有这部分分区需要修复,故能比整个大表修复花的时间更少;
3 、维护轻松:如果需要重建表,独立管理每个分区比管理单个大表要轻松得多;
4 、均衡I/O:可以把表的不同分区分配到不同的磁盘来平衡I/O改善性能;
5 、改善性能:对大表的查询、增加、修改等操作可以分解到表的不同分区来并行执行,可使运行速度更快;
6 、分区对用户透明,最终用户感觉不到分区的存在。
分区的管理:
建立表的各个分区的表空间:
CREATE TABLESPACE ts_sale1999q1
DATAFILE ‘E:\oracle\oradata\mjs\sales1999_q1.dat’
SIZE 1M
DEFAULT STORAGE (INITIAL 3K NEXT 3K
MINEXTENTS 3 PCTINCREASE 0) ;
CREATE TABLESPACE ts_sale1999q2
DATAFILE ‘E:\oracle\oradata\mjs\sales1999_q2.dat’
SIZE 1M
DEFAULT STORAGE (INITIAL 3K NEXT 3K
MINEXTENTS 3 PCTINCREASE 0) ;
建立基于分区的表:
CREATE TABLE sales
(no NUMBER not null,
sale_date DATE NOT NULL )
PARTITION BY RANGE (sale_date)
(PARTITION sales1999_q1 VALUES LESS THAN (TO_DATE(‘1999-04-01′,’YYYY-MM-DD’)) TABLESPACE ts_sale1999q1,
PARTITION sales1999_q2 VALUES LESS THAN (TO_DATE(‘1999-07-01′,’YYYY-MM-DD’)) TABLESPACE ts_sale1999q2);
选择其中的一个分区查询
select * from sales partition (sales1999_q1);
删除不必要的分区:
STEP1、DROP 分区:
ALTER TABLE sales DROP PARTITION sales2000_q1;
STEP2、利用操作系统的工具删除以上表空间占用的文件,UNIX系统为例:
oracle$ rm E:\oracle\oradata\mjs/sales1999_q1.dat
分区的其他操作:
分区的其他操作包括截短分区(truncate),将存在的分区划分为多个分区(split),交换分区(exchange),重命名(rename),为分区建立索引等,可以根据适当的情况使用。
使用了分区,还可以单独针对指定的分区进行truncate操作:
alter table sales truncate partition sales1999_q1;
分裂分区:
ALTER TABLE sales SPLIT partition sales1999_q4 AT (TO_DATE(‘1999-11-01′,’YYYY-MM-DD’)) INTO (partition sales1999_q4_p1, partition sales1999_q4_p2);
更改分区的存储表空间
ALTER TABLE sales MOVE PARTITION p4_id TABLESPACE ptbs5;
查看分区信息:
要查看表的分区信息,可查看数据字典USER_EXTENTS,操作如下:
SELECT * FROM user_extents WHERE SEGMENT_NAME=’SALES’;
EXPORT分区:
exp sales/sales_password tables=sales:sales1999_q1 rows=Y file=sales1999_q1.dmp
IMPORT分区:
imp sales/sales_password FILE =sales1999_q1.dmp TABLES = (sales:sales1999_q1) IGNORE=y
建立分区表的时候,索引加local选项,否则truncate分区,索引将失效,所有用到索引的查询都无法进行。
范围分区
create table emp1(
empno number(5) primary key,
ename varchar2(20)
)
partition by range(empno)
(partition p1 values less than (3000), — empno < 3000 partition p2 values less than (5000) -- empno <5000 and empno>= 3000
);
–分区表的维护
–增加一个分区 empno>=5000
alter table emp1 add partition p3 values less than (MAXVALUE);
–门诊的处方表
create table receipt(
receipt_no number, –处方号
receipt_time date, –处方的时间
med_name varchar2(10), –药名
med_num number(3), –数量
primary key (receipt_no,med_name)
)
partition by range(receipt_time)
(partition p0 values less than (to_date(‘20050101′,’yyyymmdd’)),
partition p1 values less than (to_date(‘20050201′,’yyyymmdd’)),
partition p2 values less than (to_date(‘20050301′,’yyyymmdd’)),
partition p3 values less than (to_date(‘20050401′,’yyyymmdd’)),
partition p4 values less than (to_date(‘20050501′,’yyyymmdd’)));
–添加分区
alter table receipt add partition p5 values less than (to_date(‘20050601′,’yyyymmdd’));
alter table receipt add partition p6 values less than (to_date(‘20050701′,’yyyymmdd’));
b) 散列分区
–散列分区(哈希分区)
create table dept1(
deptno number(2), –部门号
dname varchar2(14), –部门名称
loc varchar2(13)) –位置
partition by hash(deptno)
( partition p1,partition p2);
对这个查询起到加快查询的好处
select * from dept1 where deptno > 30;
c)–复合分区
create table salgrade1(
grade number,
losal number,
hisal number)
partition by range(grade)
subpartition by hash(losal,hisal)
(partition p1 values less than (10)
(subpartition sp1,subpartition sp2),
partition p2 values less than (20)
(subpartition sp3,subpartition sp4) );
d)–列表分区
create table sales_details(
sales_id number(6),
sman_name varchar2(20),
sales_state varchar2(15),
sales_date date,
amount number(10))
partition by list(sman_name)
(
partition sales_east values (‘antony’,’henry’,’jack’),
partition sales_west values (‘peter’,’serena’,’venus’)
);
–人员的分区表
create table student(
xh number(4) primary key,
xm varchar2(20) not null,
sex char(2) check (sex in (‘男’,’女’))
)
partition by list(sex)
(partition p1 values (‘男’),
partition p2 values (‘女’)
);
—这4种分区 用的多是范围分区,其次是散列
范围分区按时间来做:
create table ord_mast_2(
orderno varchar2(5) primary key,–定单号
odate date,–定单的日期
vencode varchar2(5) ,–供应商的代码
o_status char(1) ,–定单的状态
del_date date –定单处理的日期
)
partition by range(odate)
(
partition om1 values less than (to_date(‘1999-06-01’,
‘YYYY-MM-DD’)),
partition om2 values less than (to_date(‘1999-07-01’,
‘YYYY-MM-DD’)),
partition om3 values less than (to_date(‘1999-08-01’,
‘YYYY-MM-DD’))
);

没有简单的命令来改变普通表成为分区表
<例子>把emp表变为分区表
a)建立分区表
create table emp_b(
empno number(4) primary key,
ename varchar2(20),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2))
partition by range(empno)
(partition p1 values less than (3000),
partition p2 values less than (5000),
partition p3 values less than (MAXVALUE)
);
b) 复制数据
insert into emp_b select
* from emp;
c) 给表改名字
rename emp to emp_n
rename emp_b to emp

评论已关闭!