大表分区改造最佳实践

一个超过4000万的大表RESACTIVEINFO没有分区，将第一列主键ID按照范围分区，采用oracle的interval自动分区技术，做了一个改造，所有的索引均采用本地索引，实践证明性能大大提升，后续的维护也更为方便，分区可以根据规则自动创建，无需提前手工建立分区，以下是具体实施步骤，供参考。
采用新建分区表，将原表插入的方式，这样不会影响已有的业务，4300万的大表新建分区表，时间大约3-5个小时。

一、建分区表
id每500万一个分区，如下建表、索引、约束。

create table RESACTIVEINFOKK
(
  id                NUMBER(20) not null,
  flowid            VARCHAR2(50) not null,
  bussinesstype     VARCHAR2(100),
  type              VARCHAR2(50),
  active_no         NUMBER(20),
  oltid             VARCHAR2(50),
  ponid             VARCHAR2(255),
  authvalue         VARCHAR2(50),
  account           VARCHAR2(50),
  svlan             NUMBER(10),
  cvlan             NUMBER(20),
  rescode           VARCHAR2(50),
  resdesc           VARCHAR2(4000),
  sendtime          DATE,
  returntime        DATE,
  returnoltid       VARCHAR2(50),
  returnponid       VARCHAR2(50),
  returnsvlan       VARCHAR2(50),
  returncvlan       VARCHAR2(50),
  returnauthvalue   VARCHAR2(50),
  activexml         CLOB,
  activeresponsexml CLOB,
  returnxml         CLOB,
  returnresponsexml CLOB,
  onutype           VARCHAR2(50),
  password          VARCHAR2(50),
  countyid          VARCHAR2(50),
  cast_time         VARCHAR2(50),
  formno            VARCHAR2(50),
  oper_from         VARCHAR2(10),
  oper_name         VARCHAR2(100),
  oper_id           VARCHAR2(50)
)
partition by range (id)
INTERVAL(5000000)
(
  partition Pid_1 values less than (5000001)
    tablespace WLCS
);

create index INDEX_ACCOUNTKK on RESACTIVEINFOKK (ACCOUNT) local;
create index INDEX_OPERFROMKK on RESACTIVEINFOKK (OPER_FROM) local;
create index INDEX_RESACTIVEINFOKK on RESACTIVEINFOKK (FLOWID) local;

alter table RESACTIVEINFOKK
  add constraint PK_RESACTIVEINFOKK primary key (ID)
  using index local;

二、导入数据
采用游标的方式，批量导入数据，如下：

DECLARE
  CURSOR cur IS SELECT * FROM RESACTIVEINFO;
  TYPE rec IS TABLE OF RESACTIVEINFO%ROWTYPE;
  recs rec;
BEGIN
  OPEN cur;
  WHILE (TRUE) LOOP
    FETCH cur BULK COLLECT INTO recs LIMIT 100000;
    FORALL i IN 1 .. recs.count
      INSERT /*+ APPEND */ INTO RESACTIVEINFOKK NOLOGGING VALUES recs(i);
    COMMIT;
    EXIT WHEN cur%NOTFOUND;
  END LOOP;
  CLOSE cur;
END;
/

插入时间大约3-5个小时。

三、查看分区表信息

YYY@86.164:1521/irmsdb> SELECT table_name, partition_name, tablespace_name, num_rows, last_analyzed
  2    FROM dba_tab_partitions
  3   WHERE table_owner NOT IN ('SYS', 'SYSTEM', 'SCOTT', 'SH', 'AUDSYS')
  4     AND table_name NOT LIKE 'BIN$%'
  5     --AND num_rows > 0
  6   ORDER BY 5;

TABLE_NAME                     PARTITION_ TABLESPACE_NAME             NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------- --------------- -------------------
RESACTIVEINFOKK                SYS_P1073  WLCS                         3876732 2019-11-08 00:16:24
RESACTIVEINFOKK                SYS_P1064  WLCS                         4604723 2019-11-07 23:42:46
RESACTIVEINFOKK                SYS_P1055  WLCS                         4969618 2019-11-07 23:20:29
RESACTIVEINFOKK                PID_1      WLCS                         4995805 2019-11-07 21:13:24
RESACTIVEINFOKK                SYS_P1010  WLCS                         4999867 2019-11-07 21:09:44
RESACTIVEINFOKK                SYS_P1028  WLCS                         4999937 2019-11-07 22:10:03
RESACTIVEINFOKK                SYS_P1046  WLCS                         4999941 2019-11-07 23:22:15
RESACTIVEINFOKK                SYS_P1037  WLCS                         5000000 2019-11-07 23:23:45
RESACTIVEINFOKK                SYS_P1019  WLCS                         5000000 2019-11-07 22:11:34

9 rows selected.

四、查看各个分区id分布情况

select min(id),max(id) from "YYY"."RESACTIVEINFOKK" partition("PID_1");

        MIN(ID)         MAX(ID)
--------------- ---------------
           1601         5000000

select min(id),max(id) from "YYY"."RESACTIVEINFOKK" partition("SYS_P1010");

        MIN(ID)         MAX(ID)
--------------- ---------------
        5000001        10000000

select min(id),max(id) from "YYY"."RESACTIVEINFOKK" partition("SYS_P1073");

        MIN(ID)         MAX(ID)
--------------- ---------------
       40000001        43876933

五、分区前后性能测试对比

select count(1) from "YYY"."RESACTIVEINFO";
       43872009
Elapsed: 00:00:10.21
select count(1) from "YYY"."RESACTIVEINFOKK";
       43872009
Elapsed: 00:00:02.35

select count(1) from "YYY"."RESACTIVEINFO" where id > 20000000 and id < 30000000;
        9999940
Elapsed: 00:00:20.90
select count(1) from "YYY"."RESACTIVEINFOKK" where id > 20000000 and id < 30000000;
        9999940
Elapsed: 00:00:00.75

flowid = '170510153400719'
select count(bussinesstype) from "YYY"."RESACTIVEINFO" where flowid like '170%';
        4487755
Elapsed: 00:22:11.15
select count(bussinesstype) from "YYY"."RESACTIVEINFOKK" where flowid like '170%';
        4487755
Elapsed: 00:20:22.29

select count(1) from "YYY"."RESACTIVEINFO" where flowid like '17%';
       11698467
Elapsed: 00:00:04.03
select count(1) from "YYY"."RESACTIVEINFOKK" where flowid like '17%';
       11698467
Elapsed: 00:00:03.98

大表分区改造最佳实践

文章评论