方案:由于数据量过大,而且里面有重复数据,直接导入数据库超慢,所以需要建立临时表,然后去重插入最终表。临时表、最终表分区,都不建立主键和索引,设置临时表和最终表为nologging+并行,使用sqlload直路径+并行把数据导入临时表,然后重构主键为本地索引(为了以后查询数据速度快些),临时表对每个分区去重后插入最终表,最终表重构主键和索引,设置最终表为logging+noparallel,删除临时表。
步骤:
1. 创建临时表
-- 创建临时表
create table ESB_CUSTOMERNO_RELATION_OLD
(
CUSTOMERNO VARCHAR2(40) not null,
IDTYPE VARCHAR2(10) not null,
CUSTOMERID VARCHAR2(20) not null,
CREDATE DATE,
UPDDATE DATE,
CUSTOMERSEQ VARCHAR2(2) not null
)
partition by hash (CUSTOMERID)
(
partition P01
tablespace TCBUCC_DATA_P01,
......
partition P42
tablespace TCBUCC_DATA_P42
);
2. 导入数据到临时表
2.1 txt文件格式如下:
10000201044324534026|a|320223196301195428|1|20180504120000|20180504120000
10000201020567704042|a|320222198002261889|1|20180504120000|20180504120000
......
10000201012050396509|a|640221197309130625|1|20180504120000|20180504120000
2.2 ctl文件格式如下:
load data
CHARACTERSET AL32UTF8
infile 'esb_customerno_relation_1_01.txt'
......
infile 'esb_customerno_relation_1_55.txt'
append
into table esb_customerno_relation_old
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(CUSTOMERNO,
IDTYPE,
CUSTOMERID,
CUSTOMERSEQ,
CREDATE date 'yyyy/mm/dd hh24:mi:ss',
UPDDATE date 'yyyy/mm/dd hh24:mi:ss'
)