温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

Oracle优化:千万级大表逻辑判断的累赘

发布时间:2020-10-26 02:44:56 来源:网络 阅读:1696 作者:dbapower 栏目:关系型数据库

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问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI