曾经面试的时候有公司问ORACLE 开发不足之处有哪些? 当时不懂。最近才感受到,我想那就是UPDATE和临时表的使用上。
原本我要对一个表生成统计数据 该表的结构如下
周次,星期,商店,参观人数,购买人数,参观人次,购买人次。
200928,星期一,女装店,30,5,45,6.
虽然看起来很简单,实际上每个人数和人次都是个子查询而得来的数字。还有个问题就是有些店铺可以没有营业状态,那么其数字该为零。
也就说时间的生成 (周次,星期) 是不能依据店铺的营业时间为生成标准,得靠自然时间。从第一家店铺营业时间为起始点到统计的截止时间。
v_startday-->trunc(sysdate,'d') 然后采用循环插入进去
for i in 0..(v_endate-v_startday)
loop
insert into a
select to_char(v_currday,'yyyyiw'),to_char(v_currday,'day'), store_name, B.num,C.num,D.num,E.num
left join ( select b.num from ..... where open_day between v_currday and v_currday+1) on .....
left join ( select c.num from ..... where open_day between v_currday and v_currday+1) on .....
left join ( select d.num from ..... where open_day between v_currday and v_currday+1) on .....
left join ( select e.num from ..... where open_day between v_currday and v_currday+1) on .....
order by ......
end loop
速度超慢 而且IO量也好低 从10G的EM上 看一条
2 接着的办法是想到 先插入时间序列 然后在UPDATE 空值的数据。
for i in 0...(v_enddate-v_startday)
loop
insert into a (f_yeaweek,f_weekday)
values(to_char(v_currday,'yyyyiw'),to_char(v_currday,'day');
end loop;
update a
set (store_name,looknum,buynum,looks,buys) =
(
select f_yearweek,f_weekday,s.store_name,B.num,C.num,D.num,E.num
from
( select * from s
left join ( select count() from b where open_day between v_startdayand v_endate+1) on ...
left join ( select count() from b where open_day between v_startdayand v_endate+1) on ...
left join ( select count() from b where open_day between v_startdayand v_endate+1) on ...
left join ( select count() from b where open_day between v_startdayand v_endate+1) on ...
) F
where a.f_yearweek=f.f_yearweek and a.f_weekday= f.f_weekday
)
where a.f_yearweek between to_char(v_startday,'yyyyiw') and to_char(v_enddate,'yyyyiw')
;
结果同样的慢 从EM 图中看到 每更新次 都要执行次查询 虽然条件一样! 因为一次查询时间要5分钟。
3 想采用临时表。 先把时间数据插入到临时表 然后
insert into a
select *
from tmp
left jon ..
left jon ...
left jon....
问题是 ORACLE 临时表必须动态执行! 狂混啊! 不能像MS SQL样。
sql:="create ....tmep ...."
execute sql ;
for i in ....
loop
sql := '......'....'.... '; --动态参数赋值是个麻烦问题
exuecute sql;
end loop
sql:='
insert into a
select *
from tmp
left jon ..
left jon ...
left jon....
'
execute sql;
看到这里就头大了。