oracle大表分区

来源：互联网发布：mac ai路径查找器编辑：程序博客网时间：2024/05/16 14:43

最近在做一个项目，有一张表数据量很大，每天系统会产生4W多条数据，现在的数据量已经达到了1500W条，给查询带来了极大的不便，但由于该表当初设计的时候没有对其进行分区，现阶段由于客户反映查询超级缓慢（15秒以上），所以权衡一下就对该表做了表分区，以下是我做表分区的步骤。

1、备份表

create table USERWORKINFO_copy_bak as select * from USERWORKINFO_copy

2、删除原表

truncate table USERWORKINFO_copy
drop table USERWORKINFO_copy

3、建立分区测试用3个表空间)

create tablespace atspace_01

datafile 'E:/tablespace_local/atspace_01.dbf'

size 500M
autoextend on next 50M maxsize 5120M;

create tablespace atspace_02

datafile 'E:/tablespace_local/atspace_02.dbf'

size 500M
autoextend on next 50M maxsize 5120M;

create tablespace atspace_03

datafile 'E:/tablespace_local/atspace_03.dbf'

size 500M
autoextend on next 50M maxsize 5120M;

4、指定表空间的用户及权限
create user atspace_01 identified by "attendance";
create user atspace_02 identified by "attendance";
create user atspace_03 identified by "attendance";

grant create session to atspace_01;
grant create session to atspace_02;
grant create session to atspace_03;

alter user attendance quota unlimited on atspace_01;
alter user attendance quota unlimited on atspace_02;
alter user attendance quota unlimited on atspace_03;

5、建表，分区

create table USERWORKINFO_COPY
(
id VARCHAR2(40) not null,
empno VARCHAR2(20) default '',
empname VARCHAR2(150) default '',
statistics_months VARCHAR2(20) default '',
date_day VARCHAR2(20) default '',
department VARCHAR2(100) default '',
attendance_group VARCHAR2(40) default '',
over_time BINARY_DOUBLE default 0,
absent_day_flag VARCHAR2(20) default ('0'),
leave_early_flag VARCHAR2(20) default ('0'),
leave_day_flag VARCHAR2(20) default ('0'),
late_day_flag VARCHAR2(20) default ('0'),
emp_full_name VARCHAR2(1000) default '',
leaveearly_time BINARY_DOUBLE default 0,
lateday_time BINARY_DOUBLE default 0,
leaveday_time BINARY_DOUBLE default 0,
be_statistics_state VARCHAR2(20) default '0',
end_statistics_state VARCHAR2(20) default ('0'),
tour_code VARCHAR2(20) default '',
normaldayflag VARCHAR2(20) default '0',
tourtype VARCHAR2(20) default '0',
logindate VARCHAR2(40),
logoutdate VARCHAR2(40),
tasktime VARCHAR2(100),
holidayname VARCHAR2(100),
tourbetime VARCHAR2(10),
tourendtime VARCHAR2(10),
recess VARCHAR2(10),
type_name VARCHAR2(40),
supplement VARCHAR2(3),
unassigned VARCHAR2(2),
unassignedname VARCHAR2(10)
)
partition by range (date_day)
(
partition part_01 values less than('2016-04-01')
tablespace atspace_01,
partition part_02 values less than('2016-08-01')
tablespace atspace_02,
partition part_03 values less than(maxvalue)
tablespace atspace_03
);

create index IDX_USERWORKINFO_COPY on USERWORKINFO_COPY (EMPNO, DATE_DAY, EMP_FULL_NAME)
tablespace ATSPACE
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);

6、将USERWORKINFO_copy_bak的数据导入到USERWORKINFO_copy表

0 0