oracle优化问题

Question

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

Answer 1

只要没有太多大块头大对象，10几万对ORACLE来说真不多。
贴出SQL。

Answer 2

你关心下表与表之间的关联方式~要让SQL尽量最优，如果速度还不行，你就要考试数据库结构了·

Answer 3

呵呵，问题太泛泛了给你些意见吧，多看看批量插入 insert into a select * from b;在有就是注意表与表之间的关联了。你可以吧具体的表结构和需求拿出来，我们大家给你分析一下啊

Answer 4

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);

Answer 5

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;

Answer 6

一点建议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 select5. 哥们还得多看书，基础的东西欠缺的比较多