Oracle optimization: Tens of big table logical judgment

Insert into Pntmall_point_detail (pntmall_pnt_id, ...). Hpt_left_point,hpt_fullredempt_status) SELECT pntmall_pnt_id,......., pntmall_num,0 from Pntmall_point_detail_tmp a W Here isn't EXISTS (SELECT b.pntmall_pnt_id from pntmall_point_detail b WHERE a.pntmall_pnt_id = B.pntma LL_PNT_ID);

Pntmall_point_detail contains 38 million 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.pntma                          LL_PNT_ID); insert into Pntmall_point_detail (pntmall_pnt_id, ...). Hpt_left_point,hpt_fullredempt_status) SELECT pntmall_pnt_id,......., pntmall_num,0 from Pntmall_point_detail_tmp a

Cost 5 minutes.

In fact, we can further optimize

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,....                           hpt_left_point,hpt_ Fullredempt_status)   select pntmall_pnt_id,...., pntmall_num,0    from  Pntmall_point_detail_tmp a;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 in insert, delete index, append, rebuild index, if is archive mode, ALTER TABLE Nologing;append only applies To insert Select this way, Add commit after insert, otherwise you will not be able to perform other DML operations.

Measured append 16 million data, cost 8s

Oracle optimization: Tens of big table logical judgment