背景:
a表的表结构如下:
statis_date
subscriberkey
productkey1
productkey2
productkey3
productkey4
productkey5
opertime1
expiretime1
opertime2
expiretime2
opertime3
expiretime3
opertime4
expiretime4
opertime5
expiretime5
现在要对a表做行列转换 成 b 表
b 表表结构
statis_date
subscriberkey
productkey
expiretime
opertime
现在逻辑:
declare
vv_sql1 varchar2(3000);
vv_int := 0;
begin
loop
exit when(vv_int > 4);
vv_int := vv_int + 1;
vv_sql1 := 'insert /*+ append */ into lbikr_airtel.TB_KR_50_PSOSUBSCRI_2_DAY_MID
(
statis_date ,
subscriberkey ,
productkey ,
expiretime ,
opertime
)
select /*+ parallel(a,4)*/
a.statis_date,
a.subscriberkey,
a.productkey' || vv_int ||',
to_char(to_date(a.expiretime' || vv_int ||',' || chr(39) ||'yyyyMMddhh24miss' || chr(39) ||'),' || chr(39) ||'dd Mon yyyy hh24:mi:ss' || chr(39) ||',' || chr(39) ||'NLS_DATE_LANGUAGE = American' || chr(39) ||'),
to_char(to_date(a.opertime' || vv_int ||',' || chr(39) ||'yyyyMMddhh24miss' || chr(39) ||'),' || chr(39) ||'dd Mon yyyy hh24:mi:ss' || chr(39) ||',' || chr(39) ||'NLS_DATE_LANGUAGE = American' || chr(39) ||')
from lbidw_airtel.tb_dw_ts_productorder_his_day a
where a.productkey' || vv_int ||' <>0
and a.expiretime' || vv_int ||'>=' || chr(39) || iv_date || '000000' || chr(39)||'
and a.statis_date =' || chr(39) || iv_date || chr(39) ;
EXECUTE IMMEDIATE vv_sql1;
commit;
end loop;
end
但是这样做的话,要全表扫描5次,而lbidw_airtel.tb_dw_ts_productorder_his_day 这个表的数据接近2亿,现在的执行时间是7.5小时,想请教各位有什么好的实现方法。