oracle11g 大表改造分区表

#j记录工作实践#

背景描述：
EDC_SEPERATOR2 数采表，随着数据量的增加，数据的查询效率低下，同时导致数据的清理（删除）消耗大量系统资源，效率低下。需要对该表进行改造分区表。提升数据查询的效率和数据管理效率。

详细步骤：

获取并记录原表的关键信息：
1.1 获取表的表结构和索引DDL信息

--- 获取源表表结构
SELECT DBMS_METADATA.GET_DDL('TABLE', 'EDC_SEPERATOR2', 'MYCIM2') FROM DUAL;

输出结果：
CREATE TABLE "MYCIM2"."EDC_SEPERATOR2"
(   "FACILITY" NVARCHAR2(15),
"EQUIPMENTINFO" NVARCHAR2(20),
"IMPORTTIME" DATE,
"DBIMPORTTIME" DATE,
"DBIMPORTTIMENUM" NUMBER,
"SHIFT" NVARCHAR2(10),
"COMMENT0" NVARCHAR2(50),
"COMMENT1" NVARCHAR2(50),
"COMMENT2" NVARCHAR2(50),
"COMMENT3" NVARCHAR2(50),
"UNIQUEID" NVARCHAR2(50),
"TESTTIME" DATE,
"TESTDATE" NVARCHAR2(50),
"BIN" NVARCHAR2(50),
"BIN_COMMENT" NVARCHAR2(100),
"UOC" NVARCHAR2(50),
"ISC" NVARCHAR2(50),
"RSERLFDFIEC" NVARCHAR2(50),
"RSHUNTDFDR" NVARCHAR2(50),
"FF" NVARCHAR2(50),
"ETA" NVARCHAR2(50),
"IREV1" NVARCHAR2(50),
"IREV2" NVARCHAR2(50),
"INSOL" NVARCHAR2(50),
"IMPP" NVARCHAR2(50),
"UMPP" NVARCHAR2(50),
"PMPP" NVARCHAR2(50),
"TCELL" NVARCHAR2(50),
"TMONICELL" NVARCHAR2(50),
"TENV" NVARCHAR2(50),
"MONICELLMVTOINSOL" NVARCHAR2(50),
"FLASHMONICELLVOLTAGE" NVARCHAR2(50),
"AOI_1_Q" NVARCHAR2(50),
"AOI_1_R" NVARCHAR2(50),
"AOI_2_Q" NVARCHAR2(50),
"AOI_2_R" NVARCHAR2(50),
"TITLE" NVARCHAR2(256),
"BATCHID" NVARCHAR2(50),
"LINE" NVARCHAR2(20),
"UPLOAD_TIME" DATE,
"EASUREMENT" NVARCHAR2(50),
"COMMENTS" NVARCHAR2(256)
)
SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "MESCELL_DATA";

--- 获取源表索引结构
SELECT OWNER, INDEX_NAME, INDEX_TYPE
FROM DBA_INDEXES
WHERE TABLE_OWNER = 'MYCIM2' AND TABLE_NAME = 'EDC_SEPERATOR2';

输出结果：
OWNER       INDEX_NAME                  INDEX_TYPE

MYCIM2      IX_EDC_LINE                 NORMAL
MYCIM2      IX_EDC_SEPERATOR2_1         NORMAL
MYCIM2      BIN_COMMENT_01              NORMAL
MYCIM2      IX_EDC_SEPERATOR2_COMMENTS  NORMAL
MYCIM2      IX_EDC_SEPERATOR_TESTTIME   NORMAL
MYCIM2      IX_EDC_SEPERATOR_ETA        NORMAL
MYCIM2      IX_EDC_TITLE                NORMAL
MYCIM2      IX_EDC_FACILITY             NORMAL

--- 获取具体索引DDL（示例）
SELECT DBMS_METADATA.GET_DDL('INDEX', 'IX_EDC_LINE', 'MYCIM2') FROM DUAL;

输出结果：
CREATE INDEX "MYCIM2"."IX_EDC_LINE" ON "MYCIM2"."EDC_SEPERATOR2" ("LINE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "MYCIM2";

--- 依次查询其他索引DDL
SELECT DBMS_METADATA.GET_DDL('INDEX', 'IX_EDC_SEPERATOR2_1', 'MYCIM2') FROM DUAL;

CREATE INDEX "MYCIM2"."IX_EDC_SEPERATOR2_1" ON "MYCIM2"."EDC_SEPERATOR2" ("UPLOAD_TIME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "MESCELL_DATA";

SELECT DBMS_METADATA.GET_DDL('INDEX', 'BIN_COMMENT_01', 'MYCIM2') FROM DUAL;

CREATE INDEX "MYCIM2"."BIN_COMMENT_01" ON "MYCIM2"."EDC_SEPERATOR2" ("BIN_COMMENT")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "MYCIM2";

SELECT DBMS_METADATA.GET_DDL('INDEX', 'IX_EDC_SEPERATOR2_COMMENTS', 'MYCIM2') FROM DUAL;

CREATE INDEX "MYCIM2"."IX_EDC_SEPERATOR2_COMMENTS" ON "MYCIM2"."EDC_SEPERATOR2" ("COMMENTS")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "MYCIM2";

SELECT DBMS_METADATA.GET_DDL('INDEX', 'IX_EDC_SEPERATOR_TESTTIME', 'MYCIM2') FROM DUAL;

CREATE INDEX "MYCIM2"."IX_EDC_SEPERATOR_TESTTIME" ON "MYCIM2"."EDC_SEPERATOR2" ("TESTTIME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE