为什么会这样
v_mtgid 用户编号
t_ydxc_rec_meterage 用户表
t_ydxc_det_power 功率表
n_0 最大功率
语句功能:查询出每个用户编号小于'10_u92_M4',并且日期从5号到11号的最大功率,放到临时表T_YDXC_STA_TARGET2中
INSERT INTO T_YDXC_STA_TARGET2 语句1
select t1.v_mtgid, max(t1.n_0)
from t_ydxc_det_power t1,
(select t4.v_mtgid from t_ydxc_rec_meterage t4 where t4.v_mtgid<'10_u92_M4') v4
where t1.d_clectime >= to_date('2008-01-05', 'YYYY-MM-DD')
and t1.d_clectime <= to_date('2008-01-11', 'YYYY-MM-DD')
and t1.v_datatype = '0' 筛选数据的条件
and t1.c_placetype = '1' 筛选数据的条件
and t1.v_mtgid= v4.v_mtgid
group by t1.v_mtgid
这条语句用时不到1秒,共插入1150条数据。
修改用户筛选条件为t4.v_mtgid<'15_u92_M4'
INSERT INTO T_YDXC_STA_TARGET2 语句2
select t1.v_mtgid, max(t1.n_0)
from t_ydxc_det_power t1,
(select t4.v_mtgid from t_ydxc_rec_meterage t4 where t4.v_mtgid<'15_u92_M4') v4
where t1.d_clectime >= to_date('2008-01-05', 'YYYY-MM-DD')
and t1.d_clectime <= to_date('2008-01-11', 'YYYY-MM-DD')
and t1.v_datatype = '0' 筛选数据的条件
and t1.c_placetype = '1' 筛选数据的条件
and t1.v_mtgid= v4.v_mtgid
group by t1.v_mtgid
这条语句需要170秒左右,插入4000条数据
分别运行下面两条语句,用时为 不到1秒,6秒左右
select t1.v_mtgid, max(t1.n_0) 语句3
from t_ydxc_det_power t1,
(select t4.v_mtgid from t_ydxc_rec_meterage t4 where t4.v_mtgid<'10_u92_M4') v4
where t1.d_clectime >= to_date('2008-01-05', 'YYYY-MM-DD')
and t1.d_clectime <= to_date('2008-01-11', 'YYYY-MM-DD')
and t1.v_datatype = '0'
and t1.c_placetype = '1'
and t1.v_mtgid= v4.v_mtgid
group by t1.v_mtgid
select t1.v_mtgid, max(t1.n_0) 语句4
from t_ydxc_det_power t1,
(select t4.v_mtgid from t_ydxc_rec_meterage t4 where t4.v_mtgid<'15_u92_M4') v4
where t1.d_clectime >= to_date('2008-01-05', 'YYYY-MM-DD')
and t1.d_clectime <= to_date('2008-01-11', 'YYYY-MM-DD')
and t1.v_datatype = '0'
and t1.c_placetype = '1'
and t1.v_mtgid= v4.v_mtgid
group by t1.v_mtgid
以上测试都是在PL/SQL中进行
我的感觉单独运行语句4的时间,只是返回了几行计算结果的时间,不是所有行计算结果的时间。不知道是不是这样,如果是这样请告诉我为什么。
还有个疑问,为什么插入1150条数据用时不到1秒,而插入4000条数据需要170秒?