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_mtgidselect 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秒?
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_mtgidselect 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秒?
2, T_YDXC_STA_TARGET2的table size是否合理, 在insert时, 如果在同一seg上存储不够, 将数据分别存储在其它seg也会对速度产生影响
3, 这四个语句是否按顺序执行过, 如果是Oracle SQL Engine已优化一语句4(作为语句2的内嵌子查询), 当然快了
T_YDXC_STA_TARGET2表是临时表,每次用完都TRUNCATE,最多的时候插入4000记录,table size不存在问题。
我重复执行语句2,第一次和第二次时间上是有区别的,但是最好的也需要170秒左右。
我重复执行语句4,第一次需要4秒,第二次需要0.05秒。第二次执行完后我点击显示所有数据的下拉按钮,显示的速度非常慢,而且每次增加100条记录,给我的感觉就是0.05秒只是显示几行记录的时间,要看所有的记录就是边计算边显示。
我想问下PL/SQL处理这种带有排序操作的机制是怎么样的?
详细的,可以执行以下两个来看看.
select count(*) from t_ydxc_rec_meterage t4 where t4.v_mtgid <'15_u92_M4';
--
set serveroutput on;
set autotrace traceonly;
然后执行整个语句就可以明白了
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
很短时间4秒select count(*) from
(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秒
这是为什么让我很苦恼