oracle优化问题

kendonal 2010-02-26 09:31:53

我写了一个存储过程，使用游标对数据进行插入操作，涉及到很多表，其中一些是主从表关系，按顺序下来，主表生成数据，再生成子表的。
最近碰到一个问题，由于有的数据量过大，造成速度很慢，在10几万条以上。想请教下高手，有没有好的方法进行优化，提高下速度！

...全文

186 17 打赏收藏举报

写回复

17 条回复

切换为时间正序

请发表友善的回复…

发表回复

lzf616 2010-03-24

打赏
举报

拒绝用游标解决问题.........................

kendonal 2010-03-24

打赏
举报

谢谢各位了！！！我目前对数据库的编程是比较弱，大家有没顺便推荐写书籍看看呢？

friendjin 2010-03-21

打赏
举报

十几万，1S都不要，好好学习吧，正如楼上说的，要多学习学习，多看看

tarky_z 2010-03-20

打赏
举报

一点建议

1. 针对你贴出的代码，假设数据源就是数组cBL_CLASS_AREA_DTL,可以用for all关键字，避免使用循环，至少提高10倍以上效率。具体用法google一下吧，不难的

2. 重新考虑数据源在哪里，如果数组cBL_CLASS_AREA_DTL本身就是从别的表来的，可以不用数组，直接insert ...select ...

3. 重新考虑数据源在哪里，如果csv文件等，可以用SQL*Loader，10万数据，在笔记本上顶多10几秒就导入

4. 如果insert 是必须的，简单的/* append */可以提高不少效率，no logging也能提高一倍左右效率，最理想是create table ... as select

5. 哥们还得多看书，基础的东西欠缺的比较多

kendonal 2010-03-19

打赏
举报

上面这一段代码占用的时间比较多呢。。

kendonal 2010-03-19

打赏
举报

FOR rDTL IN cBL_CLASS_AREA_DTL(rBL_CLASS_AREA.CA_ID) LOOP
--土地分类面积表子表,CAD_ID用序列取值,CA_ID用新的CA_ID,其他字段用原来的值
select S_BL_CLASS_AREA_DTL.NEXTVAL || SeqBaseCode
into newCAD_ID
from dual;
insert into BL_CLASS_AREA_DTL
(cad_id,
ca_id,
unit_name,
theowner,
ldlrs,
rjgd,
yyrks,
rjgdzh,
get_type
)
values
(newCAD_ID,
newCA_ID,
rDTL.unit_name,
rDTL.theowner,
rdtl.rjgdzh,
rDTL.ldlrs,
rDTL.rjgd,
rDTL.yyrks,
rdtl.get_type
);

insert into SourceVSBatch
(SourceFlowsn,
NewFlowsn,
Tablename,
Keyfields,
Sourcekeyvalues,
Newkeyvalues)
values
(aFlowSN(i),
NewFlowSN,
'BL_CLASS_AREA_DTL',
'CAD_ID',
rDTL.CAD_ID,
newCAD_ID);

--插入土地分类面积表子表拆分表
FOR rDTL2 IN cBL_CLASS_AREA_DTL2(RDTL.CAD_ID) LOOP
SELECT S_BL_CLASS_AREA_DTL2.NEXTVAL || SeqBaseCode
into newcadd_ID from dual;
insert into bl_class_area_dtl2
(
cadd_id,
cad_id,
dl_id,
dl_value,
dl_value_hj
)
values
(
newcadd_ID,
newCAD_ID,
rDTL2.Dl_Id,
rDTL2.Dl_Value,
rdtl2.dl_value_hj
);

insert into SourceVSBatch
(SourceFlowsn,
NewFlowsn,
Tablename,
Keyfields,
Sourcekeyvalues,
Newkeyvalues)
values
(aFlowSN(i),
NewFlowSN,
'BL_CLASS_AREA_DTL2',
'CADD_ID',
rDTL2.CADD_ID,
newcadd_ID);
END LOOP;
END LOOP;

END LOOP; --end of cBL_CLASS_AREA cursor
--从批次清单(勘测定界地块)游标逐地块处理
FOR rBL_PLOT IN cBL_PLOT(oldSB_ID) LOOP

--获取旧地块ID
oldPL_ID := rBL_PLOT.PL_ID;

--获取新的地块ID
select S_BL_PLOT.NEXTVAL || SeqBaseCode into newPL_ID from dual;

--插入批次清单(勘测定界地块)新记录,其中PL_ID序列取值,SB_SB_ID用新的勘测ID，FLOWSN用新的业务序号,
--Source_Flowsn用当前业务序号,其他字段用原来的值
insert into BL_PLOT
(PL_ID,
FLOWSN,
SB_SB_ID,
PB_PB_ID,
PL_NO,
PL_NAME,
MAP_NO,
PNT_COUNT,
SURVEY_MAP_LINK,
SHAPE_TYPE,
PURPOSE,
LAND_TYPE,
PNT_SURVEY,
PNT_CHECKER,
DIKUAI_NO,
DIKUAI_AREA,
LAND_AREA,
AUTO_AREA,
FOR_PLACE,
REGIONCODE)
values
(newPL_ID,
NewFlowSN,
--oldSB_ID,--20090316
newSB_ID,
rBL_PLOT.PB_PB_ID,
rBL_PLOT.PL_NO,
rBL_PLOT.PL_NAME,
rBL_PLOT.MAP_NO,
rBL_PLOT.PNT_COUNT,
rBL_PLOT.SURVEY_MAP_LINK,
rBL_PLOT.SHAPE_TYPE,
rBL_PLOT.PURPOSE,
rBL_PLOT.LAND_TYPE,
rBL_PLOT.PNT_SURVEY,
rBL_PLOT.PNT_CHECKER,
rBL_PLOT.DIKUAI_NO,
rBL_PLOT.DIKUAI_AREA,
rBL_PLOT.LAND_AREA,
rBL_PLOT.AUTO_AREA,
rBL_PLOT.FOR_PLACE,
rBL_PLOT.REGIONCODE);

--插入地块项目对应表
insert into bl_proj_plot
(flowsn, pj_id, pl_id)
values
(NewFlowSN, newPJ_ID, newPL_ID);

--流程合并表记录对照表
insert into SourceVSBatch
(SourceFlowsn,
NewFlowsn,
Tablename,
Keyfields,
Sourcekeyvalues,
Newkeyvalues)
values
(aFlowSN(i),
NewFlowSN,
'BL_PLOT',
'PL_ID',
rBL_PLOT.PL_ID,
newPL_ID);

FOR rBl_PLOT_USE IN cBl_PLOT_USE(oldPL_ID) LOOP
--插入地块用途面积
select S_Bl_PLOT_USE.NEXTVAL || SeqBaseCode
into newUSE_ID
from dual;

insert into Bl_PLOT_USE
(USE_ID, FLOWSN, PL_ID, USENAME, USEAREA)
values
(newUSE_ID,
NewFlowSN,
newPL_ID,
rBl_PLOT_USE.USENAME,
rBl_PLOT_USE.USEAREA);
END LOOP;
--从土地分类面积表主表游标获取该旧地块的分类面积主表记录,并逐记录处理

--界址点成果表,PCD_ID用序列取值,PL_ID用新的地块ID,SB_SB_ID用新的勘测ID，其他字段用原来的值
insert into BL_PNT_COORD
(PCD_ID,
PL_ID,
SB_SB_ID,
PB_PB_ID,
PNT_NO,
X_COORD,
Y_COORD,
BORDER_LENGTH,
MATERIAL,
REMARK,
COORD_FALG,
SHAPE_GROUP,
PNT_SERIAL)
select s_BL_PNT_COORD.Nextval || SeqBaseCode,
newPL_ID,
newSB_ID,
PB_PB_ID,
PNT_NO,
X_COORD,
Y_COORD,
BORDER_LENGTH,
MATERIAL,
REMARK,
COORD_FALG,
SHAPE_GROUP,
PNT_SERIAL
from BL_PNT_COORD
where PL_ID = oldPL_ID; --从旧地块获取记录插入新地块记录
end loop;
end loop;

kendonal 2010-03-19

打赏
举报

FOR rbl_survey_bound in cbl_survey_bound(aFlowSN(i)) loop

--获取旧勘测ID
oldSB_ID := rbl_survey_bound.SB_ID;

--获取新的勘测ID
select S_bl_survey_bound.NEXTVAL || SeqBaseCode into newSB_ID from dual;

--勘测定界表,SB_ID用序列取值,其他字段用原来的值
insert into BL_SURVEY_BOUND
(SB_ID,
PL_ID,
PB_PB_ID,
BPL_BPL_ID,
UNIT_NAME,
HDL_ENO,
ADDRESS,
COM_TEL,
CHARGE_UNIT,
PROPERTY,
LOCATION,
USAGE,
APPL_DATE,
RELT_FILE,
CHARGE_ENO,
CHECK_ENO,
PRINCIPAL,
MAP_NO,
COORD_SYSTEM,
PNT_COUNT,
PNT_DATE,
NOTE,
SB_NAME,
RANGE_MAP_LINK,
BPL_LINK,
BC_BC_ID,
REMARK,
FLOWSN)
select newSB_ID,
PL_ID,
PB_PB_ID,
BPL_BPL_ID,
UNIT_NAME,
HDL_ENO,
ADDRESS,
COM_TEL,
CHARGE_UNIT,
PROPERTY,
LOCATION,
USAGE,
APPL_DATE,
RELT_FILE,
CHARGE_ENO,
CHECK_ENO,
PRINCIPAL,
MAP_NO,
COORD_SYSTEM,
PNT_COUNT,
PNT_DATE,
NOTE,
SB_NAME,
RANGE_MAP_LINK,
BPL_LINK,
BC_BC_ID,
REMARK,
NewFlowSN
from BL_SURVEY_BOUND
where SB_ID = oldSB_ID; --从旧勘测获取记录插入新勘测记录
--勘测定界技术报告书表,DTR_ID用序列取值,SB_SB_ID用新的勘测ID,其他字段用原来的值
insert into BL_DELIMITATION_TAC_RPT
(DTR_ID,
PL_ID,
SB_SB_ID,
PB_PB_ID,
DTR_NO,
UNIT,
PROJ_NAME,
DWFZR,
ZLFSR,
ZLSHR,
XMFZR,
RIQI)
select s_BL_DELIMITATION_TAC_RPT.Nextval || SeqBaseCode,
PL_ID,
newSB_ID,
PB_PB_ID,
DTR_NO,
UNIT,
PROJ_NAME,
DWFZR,
ZLFSR,
ZLSHR,
XMFZR,
RIQI
from BL_DELIMITATION_TAC_RPT
where SB_SB_ID = oldSB_ID; --从旧勘测获取记录插入新勘测记录

--勘测定界技术说明表,PCD_ID用序列取值,SB_SB_ID用新的勘测ID,其他字段用原来的值
insert into BL_DelimitationTac
(DT_ID,
PL_ID,
SB_SB_ID,
PB_PB_ID,
BPL_BPL_ID,
DELIMITATIONTAC_TITLE,
HEDING,
YOU,
KANCERIQI,
MIANJIWEI,
MU,
JIEZHIZHUANG,
FANGFASHI,
FUZEREN,
RIQI,
COMPUTATION_UNIT,
PRECISION,
PROJECTION_TYPE,
HOW_MANY_ANGLE,
TERRAIN_NO,
X_MOVE,
Y_MOVE,
Z_MOVE,
X_ROTATE,
Y_ROTATE,
Z_ROTATE,
MEASURE,
FORMAT_VER,
MAKE_UNIT,
MAKE_DATE,
COORD_SYS)
select s_BL_DelimitationTac.Nextval || SeqBaseCode,
PL_ID,
newSB_ID,
PB_PB_ID,
BPL_BPL_ID,
DELIMITATIONTAC_TITLE,
HEDING,
YOU,
KANCERIQI,
MIANJIWEI,
MU,
JIEZHIZHUANG,
FANGFASHI,
FUZEREN,
RIQI,
COMPUTATION_UNIT,
PRECISION,
PROJECTION_TYPE,
HOW_MANY_ANGLE,
TERRAIN_NO,
X_MOVE,
Y_MOVE,
Z_MOVE,
X_ROTATE,
Y_ROTATE,
Z_ROTATE,
MEASURE,
FORMAT_VER,
MAKE_UNIT,
MAKE_DATE,
COORD_SYS
from BL_DelimitationTac
where SB_SB_ID = oldSB_ID; --从旧勘测获取记录插入新勘测记录

FOR rBL_CLASS_AREA IN cBL_CLASS_AREA(oldSB_ID) LOOP
--获取新的CA_ID
select S_BL_CLASS_AREA.NEXTVAL || SeqBaseCode
into newCA_ID
from dual;

--插入土地分类面积表主表新记录,其中CA_ID用新的CA_ID,SB_SB_ID用新的勘测ID,其他字段用原来的值
insert into BL_CLASS_AREA
(CA_ID,
PL_ID,
SB_SB_ID,
PB_PB_ID,
County,
COUNTRYSIDE,
VISIBLE_SETING)
values
(newCA_ID,
rBL_CLASS_AREA.PL_ID,
newSB_ID,
rBL_CLASS_AREA.PB_PB_ID,
rBL_CLASS_AREA.County,
rBL_CLASS_AREA.COUNTRYSIDE,
rBL_CLASS_AREA.VISIBLE_SETING);