hsgzs 发表于 2006-3-15 12:16:29
原insert语句如下：
insert into qa_data_tmp1(zzbm_bm,ypbm_bm,fxxmbm_bm,fxsj_rq,fxsj_cysj,fxsj_sj,fxsj_bz,fxsj_hgbz,fxsj_ids,fxsj_hgz,searchid,tmp_yph,tmp_date,ZZBM_MC,YPBM_MC,FXXMBM_MC,YPBM_LB,YPBM_BY2,YPBM_XSXH,FXXMBM_DW,FXXMBM_FL,FXXMBM_XSXH,FXXMBM_BY2,ZZBM_XSXH,ZZDYYP_IDS)
select zzbm_bm,ypbm_bm,fxxmbm_bm,fxsj_rq,fxsj_cysj,fxsj_sj,fxsj_bz,fxsj_hgbz,fxsj_ids,fxsj_hgz,3,fxsj_by1,sysdate ,ZZBM_MC,YPBM_MC,FXXMBM_MC,YPBM_LB,YPBM_BY2,YPBM_XSXH,FXXMBM_DW,FXXMBM_FL,FXXMBM_XSXH,FXXMBM_BY2,ZZBM_XSXH,ZZDYYP_IDS
from view_fxsjcx where length(fxsj_sj)>0 and fxsj_rq between'2006-03-14' and '2006-03-14' and zzbm_mc in ('常压','催化','电精制','气分','加氢精制','重整','聚合','制硫','酸性水','油品气柜','小气分','产品质量数据','原材料质量数据') and fxsj_by1 in( select fxsj_by1 from view_fxsj where length(fxsj_sj)>0 and fxsj_rq>='2006-03-14' and fxsj_rq<='2006-03-14' and fxsj_hgbz='0') order by ZZDYYP_IDS
刚开始的时候速度是很快的，但是过了几个月以后，发现插入一次竟然要30秒以上，就算只有40几条记录也要这么久，开始以为是索引的问题，结果把索引全部去掉，再用脚本重新生成，还是一样，请问各位大虾，是不是插入时要写入日志的问题？？那么这个问题该怎么解决呢？？？
mosquitoxh 发表于 2006-3-15 12:28:08
索引是不能去的,应该优化
你这里还有查询,
建议先看看查询
select zzbm_bm,ypbm_bm,fxxmbm_bm,fxsj_rq,fxsj_cysj,fxsj_sj,fxsj_bz,fxsj_hgbz,fxsj_ids,fxsj_hgz,3,fxsj_by1,sysdate ,ZZBM_MC,YPBM_MC,FXXMBM_MC,YPBM_LB,YPBM_BY2,YPBM_XSXH,FXXMBM_DW,FXXMBM_FL,FXXMBM_XSXH,FXXMBM_BY2,ZZBM_XSXH,ZZDYYP_IDS
from view_fxsjcx where length(fxsj_sj)>0 and fxsj_rq between'2006-03-14' and '2006-03-14' and zzbm_mc in ('常压','催化','电精制','气分','加氢精制','重整','聚合','制硫','酸性水','油品气柜','小气分','产品质量数据','原材料质量数据') and fxsj_by1 in( select fxsj_by1 from view_fxsj where length(fxsj_sj)>0 and fxsj_rq>='2006-03-14' and fxsj_rq<='2006-03-14' and fxsj_hgbz='0') order by ZZDYYP_IDS
的性能
hsgzs 发表于 2006-3-15 12:58:26
查询速度大概是2秒，就是前面加了insert以后变慢
bobfang 发表于 2006-3-15 13:49:03
需要看看insert和单独执行select的执行计划。
cenlmmx 发表于 2006-3-15 13:56:35
insert /*+ APPEND */into ..试试,这个表可能经过大量的DML碎片很多,建议先整理
hsgzs 发表于 2006-3-15 14:00:51
回 cenlmmx(学海无涯苦作舟)：
执行完成，28.812秒
回bobfang(匆匆过客) ：
具体怎么操作阿？？
hsgzs 发表于 2006-3-15 14:03:40
对于插入的表我做了很多的索引，每次插入的时候都要查一遍索引，看是否重复，但是我把索引删掉一次后，还是要这么多的时间，不知道什么原因？？
cenlmmx 发表于 2006-3-15 14:17:04
大批量插入最好关闭索引,要不会很慢的,还有约束也是一样.
这样create table newtable as select * from qa_data_tmp1,
再插入newtable看要多少时间?
hsgzs 发表于 2006-3-15 14:23:31
回 cenlmmx(学海无涯苦作舟)：
照你的方法做了，再插入时间是25.629 ,22条记录
cenlmmx 发表于 2006-3-15 14:49:08
怎么还这么大,插了多少数据?
cenlmmx 发表于 2006-3-15 14:49:57
sorry没看到,你表里现在多少数据?
hsgzs 发表于 2006-3-15 15:29:19
我是from的一个视图，视图里有46万条记录
cenlmmx 发表于 2006-3-15 15:45:10
那直接插表不通过视图怎么样,时间多少?
hsgzs 发表于 2006-3-15 16:05:20
直接插表的话，速度快一点，大概10秒，但是我的视图查询很快阿，两秒上下阿，有关系么？？
cenlmmx 发表于 2006-3-15 16:12:26
插视图会比直接插表慢些,但这个具体量化值没研究过.
hsgzs 发表于 2006-3-15 17:38:21
现在问题是我在数据量20万条左右的时候，是很快的，那时新建了索引，补充了很多数据。到现在4个多月了，速度开始下来了，我的那个tmp1是做临时表用的，没有每次都在临时表空间里创建删除，但是现在速度很慢，不知道是什么原因，所以想请教高手，看看问题所在
hsgzs 发表于 2006-3-16 9:20:11
现在我把视图改成from a,b,c where a.z1=b.z1 and .....代码放在后面，但是插入还是要16秒以上，怎么回事呢？
lishaowensdut 发表于 2006-3-16 9:51:10
建议在qa_data_tmp1新增一列guid做主键。有可能是主键的原因。
insert into qa_data_tmp1(id，zzbm_bm,ypbm_bm,fxxmbm_bm,fxsj_rq,fxsj_cysj,fxsj_sj,fxsj_bz,fxsj_hgbz,fxsj_ids,fxsj_hgz,searchid,tmp_yph,tmp_date,ZZBM_MC,YPBM_MC,FXXMBM_MC,YPBM_LB,YPBM_BY2,YPBM_XSXH,FXXMBM_DW,FXXMBM_FL,FXXMBM_XSXH,FXXMBM_BY2,ZZBM_XSXH,ZZDYYP_IDS)
select newid（）,zzbm_bm,ypbm_bm,fxxmbm_bm,fxsj_rq,fxsj_cysj,fxsj_sj,fxsj_bz,fxsj_hgbz,fxsj_ids,fxsj_hgz,3,fxsj_by1,sysdate ,ZZBM_MC,YPBM_MC,FXXMBM_MC,YPBM_LB,YPBM_BY2,YPBM_XSXH,FXXMBM_DW,FXXMBM_FL,FXXMBM_XSXH,FXXMBM_BY2,ZZBM_XSXH,ZZDYYP_IDS
from view_fxsjc
cenlmmx 发表于 2006-3-16 9:59:45
你的视图还有join,那会更慢,想不出来什么好办法还是建议直接插表.
mosquitoxh 发表于 2006-3-16 12:13:52
DECLARE
CNT NUMBER(10):=0;
I NUMBER(10);
BEGIN
SELECT COUNT(*) INTO CNT FROM (select zzbm_bm,ypbm_bm,fxxmbm_bm,fxsj_rq,fxsj_cysj,fxsj_sj,fxsj_bz,fxsj_hgbz,fxsj_ids,fxsj_hgz,3,fxsj_by1,sysdate ,ZZBM_MC,YPBM_MC,FXXMBM_MC,YPBM_LB,YPBM_BY2,YPBM_XSXH,FXXMBM_DW,FXXMBM_FL,FXXMBM_XSXH,FXXMBM_BY2,ZZBM_XSXH,ZZDYYP_IDS
from view_fxsjcx where length(fxsj_sj)>0 and fxsj_rq between'2006-03-14' and '2006-03-14' and zzbm_mc in ('常压','催化','电精制','气分','加氢精制','重整','聚合','制硫','酸性水','油品气柜','小气分','产品质量数据','原材料质量数据') and fxsj_by1 in( select fxsj_by1 from view_fxsj where length(fxsj_sj)>0 and fxsj_rq>='2006-03-14' and fxsj_rq<='2006-03-14' and fxsj_hgbz='0') order by ZZDYYP_IDS
)
FOR I IN 1..TRUNC(CNT/500)+1 LOOP
insert into qa_data_tmp1(zzbm_bm,ypbm_bm,fxxmbm_bm,fxsj_rq,fxsj_cysj,fxsj_sj,fxsj_bz,fxsj_hgbz,fxsj_ids,fxsj_hgz,searchid,tmp_yph,tmp_date,ZZBM_MC,YPBM_MC,FXXMBM_MC,YPBM_LB,YPBM_BY2,YPBM_XSXH,FXXMBM_DW,FXXMBM_FL,FXXMBM_XSXH,FXXMBM_BY2,ZZBM_XSXH,ZZDYYP_IDS)
select zzbm_bm,ypbm_bm,fxxmbm_bm,fxsj_rq,fxsj_cysj,fxsj_sj,fxsj_bz,fxsj_hgbz,fxsj_ids,fxsj_hgz,3,fxsj_by1,sysdate ,ZZBM_MC,YPBM_MC,FXXMBM_MC,YPBM_LB,YPBM_BY2,YPBM_XSXH,FXXMBM_DW,FXXMBM_FL,FXXMBM_XSXH,FXXMBM_BY2,ZZBM_XSXH,ZZDYYP_IDS
from view_fxsjcx where length(fxsj_sj)>0 and fxsj_rq between'2006-03-14' and '2006-03-14' and zzbm_mc in ('常压','催化','电精制','气分','加氢精制','重整','聚合','制硫','酸性水','油品气柜','小气分','产品质量数据','原材料质量数据') and fxsj_by1 in( select fxsj_by1 from view_fxsj where length(fxsj_sj)>0 and fxsj_rq>='2006-03-14' and fxsj_rq<='2006-03-14' and fxsj_hgbz='0') order by ZZDYYP_IDS;
COMMIT;
END LOOP;
END;
hsgzs 发表于 2006-3-16 14:31:56
决定不用视图了，直接插表，速度终于起来了，2秒左右，谢谢各位大大了，送分
ming500 发表于 2006-3-16 23:19:00
把 where 条件中的 in （） 放到 where的后面，别放到整个sql语句的后面。试试。
