遭遇极品 开发人员

由于项目要从 UAT环境迁移到 Prod 环境，infosys(阿三) team 的人要我运行下面的SQL，具体的业务逻辑我就不多说了。

BEGIN

Update ADWGU_DICFR.DICFR_ORDR_CUT_FCT a
Set
TP_END_DATE =
( select TO_NUMBER(TO_CHAR((TP_END_DATE),'YYYYMMDD')) from CAL_MASTR_DIM b
where a.TIME_PERD_START_DATE= TO_NUMBER(TO_CHAR((b.DAY_DATE),'YYYYMMDD')));

Update ADWGU_DICFR.DICFR_ORDR_CUT_FCT a
Set
WK_END_DATE =
( select TO_NUMBER(TO_CHAR((WK_END_DATE),'YYYYMMDD') from CAL_MASTR_DIM b where
a.TIME_PERD_START_DATE= TO_NUMBER(TO_CHAR((b.DAY_DATE),'YYYYMMDD')));

Update ADWGU_DICFR.DICFR_ORDR_CUT_FCT a
Set
MTH_START_DATE =
( select TO_NUMBER(TO_CHAR((MTH_START_DATE),'YYYYMMDD') from CAL_MASTR_DIM b where
a.TIME_PERD_START_DATE= TO_NUMBER(TO_CHAR((b.DAY_DATE),'YYYYMMDD')));

Update ADWGU_DICFR.DICFR_ORDR_CUT_FCT a
Set
MTH_END_DATE =
( select TO_NUMBER(TO_CHAR((MTH_END_DATE),'YYYYMMDD') from CAL_MASTR_DIM b where
a.TIME_PERD_START_DATE= TO_NUMBER(TO_CHAR((b.DAY_DATE),'YYYYMMDD')));

COMMIT;

END;
/

SQL> select count(*) from ADWGU_DICFR.DICFR_ORDR_CUT_FCT ; ---这个表有5百多万记录

COUNT(*)
----------
5615733

相信你看了这个过程肯定要发狂了，这简直就是极品开发人员写的啊，印度阿三，哎，水平确实不咋地，妈的项目上面的性能问题绝大部分就是这些SB开发人员乱写SQL，写好了也不给我检查。哥就不明白为啥不能在一个 SQL里面搞定所有的update，而非要

单独的update。我前面一篇博客才写了 一个update的优化案例， 那么这里也适用，下面就是我改写的SQL

declare
cursor c is
select
TO_NUMBER(TO_CHAR((b.TP_END_DATE),'YYYYMMDD')) TP_END_DATE,
TO_NUMBER(TO_CHAR((b.WK_END_DATE),'YYYYMMDD')) WK_END_DATE,
TO_NUMBER(TO_CHAR((b.MTH_START_DATE),'YYYYMMDD')) MTH_START_DATE,
TO_NUMBER(TO_CHAR((b.MTH_END_DATE),'YYYYMMDD')) MTH_END_DATE,
a.rowid row_id
from ADWGU_DICFR.DICFR_ORDR_CUT_FCT a, ADWGU_DICFR.CAL_MASTR_DIM b
where a.TIME_PERD_START_DATE= TO_NUMBER(TO_CHAR((b.DAY_DATE),'YYYYMMDD')) ;
v_counter number;
begin
v_counter := 0;
for v_row in c loop
update ADWGU_DICFR.DICFR_ORDR_CUT_FCT
set TP_END_DATE = v_row.TP_END_DATE,
WK_END_DATE = v_row.WK_END_DATE,
MTH_START_DATE = v_row.MTH_START_DATE,
MTH_END_DATE = v_row.MTH_END_DATE
where rowid = v_row.row_id;
v_counter := v_counter + 1;
if (v_counter >= 1000) then
commit;
v_counter := 0;
end if;
end loop;
commit;
end;
/

改写后的SQL 28分钟就完成了
PL/SQL procedure successfully completed.

Elapsed: 00:27:59.81