Oracle亿级数据的分区处理

随着业务数据量的增大,单张表的数据量会越来越大,对表的操作效率会越来越低,因此有必要对数据库的表进行分区处理.这里介绍其一,还有另一种在线重定义表分区也是可以的(ORACLE9之后添加的新功能)

思路:
1.查看表大小(确定是否有必要进行分区)
2.对CUS_SUMMARY的表结构和数据进行备份CUS_SUMMARY1和建立分区表之后将数据导入到CUS_SUMMARY2中
3.删除CUS_SUMMARY,将CUS_SUMMARY2重命名为CUS_SUMMARY

首先看下数据量和所占用的内存大小,查询后选择分区

--1
select sum(bytes)/(1024*1024) as "size(M)" from user_segments
where segment_name=upper('CUS_SUMMARY');
--2
select count(*) from CUS_SUMMARY

1.创建复制一份原有的表和数据到新的表中,原建表语句

-- Create table
create table CUS_SUMMARY
(
  pk               VARCHAR2(36) default "PBP"."ISEQ$$_99928".nextval not null,
  pktt             VARCHAR2(4),
  term             VARCHAR2(8),
  termsub          VARCHAR2(8),
  pkorg            VARCHAR2(12),
  orgname          VARCHAR2(30),
  pkcus            VARCHAR2(36),
  cusname          VARCHAR2(100),
  pkkv             VARCHAR2(36),
  pkog             VARCHAR2(12),
  pkkv_source      VARCHAR2(36),
  value            NUMBER(20,6),
  pksub            VARCHAR2(40),
  if_import        CHAR(1),
  remark           VARCHAR2(300),
  rcreationtime    DATE,
  rcreator         VARCHAR2(36),
  rcreationversion VARCHAR2(20)
)
tablespace TSPBP
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table CUS_SUMMARY
  is '客户汇总表（用BAS_KEYWORD_VARIABLE变量）';
-- Add comments to the columns 
comment on column CUS_SUMMARY.pk
  is '客户汇总主键';
comment on column CUS_SUMMARY.pktt
  is '时期类型主键（3月6年06年初bas_term_type）';
comment on column CUS_SUMMARY.term
  is '时期（例六月201406，二季20142，年2014，上半年20141，当前9不填）';
comment on column CUS_SUMMARY.termsub
  is '时期-辅助字段（不定期存结束日期）';
comment on column CUS_SUMMARY.pkorg
  is '机构主键';
comment on column CUS_SUMMARY.orgname
  is '机构名称';
comment on column CUS_SUMMARY.pkcus
  is '客户主键';
comment on column CUS_SUMMARY.cusname
  is '客户名称(姓名/企业中文名称)';
comment on column CUS_SUMMARY.pkkv
  is '关键字变量主键';
comment on column CUS_SUMMARY.pkog
  is '网格主键';
comment on column CUS_SUMMARY.pkkv_source
  is '关键字变量主键_源';
comment on column CUS_SUMMARY.value
  is '关键字变量值';
comment on column CUS_SUMMARY.pksub
  is '辅助主键－1:相关表中的主键值，方便特殊查询2:null代表总数';
comment on column CUS_SUMMARY.if_import
  is '是否导入';
comment on column CUS_SUMMARY.remark
  is '备注';
comment on column CUS_SUMMARY.rcreationtime
  is '记录创建时间';
comment on column CUS_SUMMARY.rcreator
  is '记录创建人主键';
comment on column CUS_SUMMARY.rcreationversion
  is '记录创建版本';
-- Create/Recreate indexes 
create unique index INDEX_62 on CUS_SUMMARY (PKTT, PKORG, PKKV, PKSUB, TERM DESC, PKCUS)
  tablespace TSPBP
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table CUS_SUMMARY
  add constraint PK_CUS_SUMMARY primary key (PK)
  using index 
  tablespace TSPBP
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

创建CUS_SUMMARY1,备份数据

insert into CUS_SUMMARY1 select * from CUS_SUMMARY;

创建分区表CUS_SUMMARY2,添加分区语句,这里最好先不要加索引,因为添加索引之后,从旧表往新表导入数据会很慢,

-- Create table
   partition by range(term)
   interval (1)
   store in (users, system)
   (
      partition sp1 values less than (201811)
   )

2.Oracle的普通表没有办法通过修改属性的方式直接转化为分区表，必须通过重建的方式进行转变,添加分区语句,根据需要添加分区(不需要添加MAXVALUE分区,不然之后添加分区会很麻烦)
3.重命名原CUS_SUMMARY表为CUS_SUMMARY3(备份作用,千万别删除,出现问题,就无法挽回了),将CUS_SUMMARY2重命名为CUS_SUMMARY,可能数据量比较大,所以我这里报错了,所以大家可以删除CUS_SUMMARY,重新新建带分区的CUS_SUMMARY,将CUS_SUMMARY1或者CUS_SUMMARY2数据导入

ALTER TABLE CUS_SUMMARY RENAME TO CUS_SUMMARY3;
ALTER TABLE CUS_SUMMARY2 RENAME TO CUS_SUMMARY;

拓展

alter table CUS_SUMMARY2 add partition CUS_201905 values less than ('201906');



```sql
---彩蛋:测试建立表分区
create table emp
   (
     EMPNO    NUMBER(4) not null,
     ENAME    VARCHAR2(10),
     JOB      VARCHAR2(9),
     MGR      NUMBER(4),
     HIREDATE DATE,
     SAL      number,
     COMM     NUMBER(7,2),
     DEPTNO   NUMBER(2)
   )
   partition by range(sal)
   interval (1)
   store in (users, system)
   (
      partition sp1 values less than (201701)
   );

续集
[数据量大到原始表空间不足解决办法](https://blog.csdn.net/weixin_43495390/article/details/105215123)