Oracle亿级数据的分区处理

旧城等待， 2021-09-21 05:18 651阅读 0赞

随着业务数据量的增大,单张表的数据量会越来越大,对表的操作效率会越来越低,因此有必要对数据库的表进行分区处理.这里介绍其一,还有另一种在线重定义表分区也是可以的(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

![数据量][20200327095906725.png]![数据大小][20200327095656166.png]  
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)

[20200327095906725.png]: https://img-blog.csdnimg.cn/20200327095906725.png
[20200327095656166.png]: /images/20210920/f858b7331d4542c5b5c865ec4e6aec5f.png