insertinto pntmall_point_detail(PNTMALL_PNT_ID,PNTMALL_PNT_DT,PNTMALL_VALIDITY,PNTMALL_LASTUPDATEDT,
在达州等地区，都构建了全面的区域性战略布局，加强发展的系统性、市场前瞻性、产品创新能力，以专注、极致的服务理念，为客户提供成都网站设计、成都网站建设、外贸网站建设 网站设计制作按需网站策划,公司网站建设,企业网站建设,品牌网站制作,营销型网站,外贸网站制作,达州网站建设费用合理。
PNTMALL_RPTPNT_ID,PNTMALL_HAIERUID,
PNTMALL_OPTYPE_ID,PNTMALL_OPTYPE_DESC,
PNTMALL_NUM,PNTMALL_EXPDT,HPT_REDEMPT_POINT,
HPT_LEFT_POINT,HPT_FULLREDEMPT_STATUS)
SELECT PNTMALL_PNT_ID,PNTMALL_PNT_DT,PNTMALL_VALIDITY,PNTMALL_LASTUPDATEDT,
PNTMALL_RPTPNT_ID,PNTMALL_HAIERUID,
PNTMALL_OPTYPE_ID,PNTMALL_OPTYPE_DESC,
PNTMALL_NUM,PNTMALL_EXPDT,0 as HPT_REDEMPT_POINT,PNTMALL_NUM,0
FROM pntmall_point_detail_tmp a
WHERE NOT EXISTS (SELECT b.PNTMALL_PNT_ID
FROM pntmall_point_detail b
WHERE a.PNTMALL_PNT_ID = b.PNTMALL_PNT_ID);
PNTMALL_POINT_DETAIL包含3800万条数据，cost 6 hours。
优化后
delete from pntmall_point_detail_tmp a where exists (select 1 from pntmall_point_detail b where a.PNTMALL_PNT_ID = b.PNTMALL_PNT_ID);
insertinto pntmall_point_detail(PNTMALL_PNT_ID,PNTMALL_PNT_DT,PNTMALL_VALIDITY,PNTMALL_LASTUPDATEDT,
PNTMALL_RPTPNT_ID,PNTMALL_HAIERUID,
PNTMALL_OPTYPE_ID,PNTMALL_OPTYPE_DESC,
PNTMALL_NUM,PNTMALL_EXPDT,HPT_REDEMPT_POINT,
HPT_LEFT_POINT,HPT_FULLREDEMPT_STATUS)
SELECT PNTMALL_PNT_ID,PNTMALL_PNT_DT,PNTMALL_VALIDITY,PNTMALL_LASTUPDATEDT,
PNTMALL_RPTPNT_ID,PNTMALL_HAIERUID,
PNTMALL_OPTYPE_ID,PNTMALL_OPTYPE_DESC,
PNTMALL_NUM,PNTMALL_EXPDT,0 as HPT_REDEMPT_POINT,PNTMALL_NUM,0
FROM pntmall_point_detail_tmp a;
cost 5 minutes。
其实还可以进一步优化
drop indexBER.INDEX_POD_PNTMALL_HAIERUID;
drop indexBER.PNTMALL_POINT_ID_HAIERUID;
delete frompntmall_point_detail_tmp a where exists (select 1frompntmall_point_detailb wherea.PNTMALL_PNT_ID =b.PNTMALL_PNT_ID);
insert/*+append*/into pntmall_point_detail(PNTMALL_PNT_ID,PNTMALL_PNT_DT,PNTMALL_VALIDITY,PNTMALL_LASTUPDATEDT,
PNTMALL_RPTPNT_ID,PNTMALL_HAIERUID,
PNTMALL_OPTYPE_ID,PNTMALL_OPTYPE_DESC,
PNTMALL_NUM,PNTMALL_EXPDT,HPT_REDEMPT_POINT,
HPT_LEFT_POINT,HPT_FULLREDEMPT_STATUS)
SELECT PNTMALL_PNT_ID,PNTMALL_PNT_DT,PNTMALL_VALIDITY,PNTMALL_LASTUPDATEDT,
PNTMALL_RPTPNT_ID,PNTMALL_HAIERUID,
PNTMALL_OPTYPE_ID,PNTMALL_OPTYPE_DESC,
PNTMALL_NUM,PNTMALL_EXPDT,0 as HPT_REDEMPT_POINT,PNTMALL_NUM,0
FROM pntmall_point_detail_tmp a;
commit;
WHERE NOT EXISTS (SELECT b.PNTMALL_PNT_ID
FROMpntmall_point_detail b
WHEREa.PNTMALL_PNT_ID = b.PNTMALL_PNT_ID);
create index BER.INDEX_POD_PNTMALL_HAIERUIDon BER.PNTMALL_POINT_DETAIL (PNTMALL_HAIERUID) online nologing;
create unique indexBER.PNTMALL_POINT_ID_HAIERUID on BER.PNTMALL_POINT_DETAIL (PNTMALL_PNT_ID) online nologing;
总体优化思路，不要在insert中加入过多的判断语句，删索引，append，重建索引，如果是归档模式，alter table nologing；append 只适用于insert select 这种方式，而且insert后要加commit,否则无法进行其他DML操作。
实测 append 1600万条数据，cost 8s
