Shulou(Shulou.com)06/01 Report--

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 contains 38 million pieces of data, cost 6 hours.

After optimization

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 .

In fact, it can be further optimized.

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

Overall optimization idea, do not add too many judgment statements to insert, delete index, append, re-index, if it is an archive mode, alter table nologing;append is only suitable for insert select, and add commit after insert, otherwise other DML operations cannot be carried out.

16 million measured append data, cost 8s

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.