Oracle optimization: the encumbrance of tens of millions of large table logic judgments
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
