原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几条记录也要这么久,开始以为是索引的问题,结果把索引全部去掉,再用脚本重新生成,还是一样,请问各位大虾,是不是插入时要写入日志的问题??那么这个问题该怎么解决呢???
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几条记录也要这么久,开始以为是索引的问题,结果把索引全部去掉,再用脚本重新生成,还是一样,请问各位大虾,是不是插入时要写入日志的问题??那么这个问题该怎么解决呢???
建议先看看查询
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的性能
执行完成,28.812秒回bobfang(匆匆过客) :
具体怎么操作阿??
这样create table newtable as select * from qa_data_tmp1,
再插入newtable看要多少时间?
照你的方法做了,再插入时间是25.629 ,22条记录
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
CNT NUMBER(10):=0;
I NUMBER(10);
BEGINSELECT 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 LOOPinsert 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;