共計 2508 個字符，預計需要花費 7 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

insert 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

  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);

insert 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;

cost 5 minutes。

其實還可以進一步優化

drop indexBER.INDEX_POD_PNTMALL_HAIERUID;

drop indexBER.PNTMALL_POINT_ID_HAIERUID;

delete from pntmall_point_detail_tmp a where exists (select 1from pntmall_point_detailb where a.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

向 AI 問一下細節

丸趣 TV 網 – 提供最優質的資源集合！