insertinto pntmall_point_detail(PNTMALL_PNT_ID,PNTMALL_PNT_DT,PNTMALL_VALIDITY,PNTMALL_LASTUPDATEDT,

在達州等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強發(fā)展的系統(tǒng)性、市場前瞻性、產(chǎn)品創(chuàng)新能力，以專注、極致的服務理念，為客戶提供成都網(wǎng)站設(shè)計、成都網(wǎng)站建設(shè)、外貿(mào)網(wǎng)站建設(shè) 網(wǎng)站設(shè)計制作按需網(wǎng)站策劃,公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),品牌網(wǎng)站制作,營銷型網(wǎng)站,外貿(mào)網(wǎng)站制作,達州網(wǎng)站建設(shè)費用合理。

                           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萬條數(shù)據(jù)，cost 6 hours。

優(yōu)化后

 

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。

其實還可以進一步優(yōu)化

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;

總體優(yōu)化思路，不要在insert中加入過多的判斷語句，刪索引，append，重建索引，如果是歸檔模式，alter table nologing；append 只適用于insert select 這種方式，而且insert后要加commit,否則無法進行其他DML操作。

實測 append 1600萬條數(shù)據(jù)，cost 8s

新聞標題：Oracle優(yōu)化：千萬級大表邏輯判斷的累贅
文章源于：http://chinadenli.net/article30/jggpso.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站制作、響應式網(wǎng)站、ChatGPT、企業(yè)建站、微信小程序、Google

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)