Oracle数据库编程调优手册

ORACLE数据库编程优化手册V1.0新意科技®版权所有©第1页共15页Oracle数据库编程调优手册ORACLE数据库编程优化手册V1.0新意科技®版权所有©第2页共15页目录前言................................................................................................................................................3一、ORACLE数据加载优化(SQLLoader)....................................................................................3二、UPDATE优化..........................................................................................................................31、对全表的Update操作.......................................................................................................32、有条件的Update操作.......................................................................................................43、多表连接Update操作.......................................................................................................44、两张表关联Update的三种方式.......................................................................................65、用视图代替对表的Update操作.......................................................................................8三、DELETE优化...........................................................................................................................91、通过分拆表，避免DELETE操作....................................................................................92、通过中间表，用INSERT代替DELETE操作................................................................9四、DBA级别优化提示...............................................................................................................10五、其它优化提示.........................................................................................................................13ORACLE数据库编程优化手册V1.0新意科技®版权所有©第3页共15页前言本文根据最近一段时间，技术部与6.0项目组共同在做系统优化过程中，最常用的做法做一个总结。目前只收录最近优化时的一些做法，供各项目组参考。各个项目组在实际开发过程中，如有其它优化SQL的技巧，也请提交技术部，形成技术文档，供各项目组参考。性能优化的几个原则：1.简化业务流程是提高性能的最可靠的方法------它可以给你的性能带来成倍的提升。2.不要把希望寄托在DBA得身上，它不可能给你的性能带来成倍的提升。3.尽量少用或者不用Update和Delete语句。一、ORACLE数据加载优化(SQLLoader)用于外部数据加载的表应该尽量简单，尽量不要创建主键、字段的Default值以及其它约束。这样可以充分利用Oracle数据库的直接路径加载（Direct=y）、并行加载（PARALLEL=true）提高数据加载性能。［示例］用SQLLoader加载数据时使用直接路径加载（DirectPathLoads）参数Direct=y（或者DIRECT=true），可以使用下列方法：方法：sqlldrscott/tigercontrol=ldr.ctldirect=y二、UPDATE优化Oracle数据库中对大表（通常更新的记录数在1万条以上）进行Update操作的代价是非常高的，但是对表的INSERT、SELECT操作则相对较快。因此应该从数据库结构设计、SQL语句中尽量不使用对大表的Update操作，针对不同情况，可以参考以下几种做法：1、对全表的Update操作对全表的Update操作，可以先把数据Select到一个临时表中，再把被Update的表用truncate删除，然后把数据再从临时表Insert到被Update表中。例如：updateea_custacc.inv_accsetinv_acc=nvl(trim(inv_acc),'1');上述全表的Update操作可以改为下面的一系列SQL语句替代：--1.将更新后的数据Inser到临时表CREATETABLEinv_acc_tmpASSELECTnvl(trim(inv_acc),'1')inv_acc,mkt_code,inv_name,cert_code,inv_prop,inv_status,open_date,close_date,noteFROMea_custacct.inv_acc;--2.清空被更新的表ORACLE数据库编程优化手册V1.0新意科技®版权所有©第4页共15页TRUNCATETABLEea_custacct.inv_acc;--3.将数据从临时表再Insert到被更新的表中INSERTINTOea_custacct.inv_accSELECT*FROMinv_acc_tmp;COMMIT;2、有条件的Update操作当对大表中的部分数据，以一定的条件进行Update时，可以利用Oracle的ROWID字段，用以下方式实现有条件的Update：1、将满足Update条件的记录（包含原表中的ROWID）Insert到临时表；2、再根据ROWID，将未更新的记录Inser到临时表；3、清空被更新的表；4、将数据从临时表Insert到被更新的表；如下，下列Update语句：UPDATEEI_SRCDATA.HA_JSMX_TEMPASETEND_FLAG='1'WHEREA.QSBZ='080'可以使用下列语句替换：--1.将满足Update条件的记录（包含原表中的ROWID）Insert到临时表；CREATETABLEHA_JSMX_TEMP_TMPASSELECTROWIDASROW_ID,A.*FROMEI_SRCDATA.HA_JSMX_TEMPAWHEREA.QSBZ='080';COMMIT;--2.再根据ROWID，将未更新的记录Insert到临时表INSERTINTOHA_JSMX_TEMP_TMPSELECTA.ROWID,A.*FROMEI_SRCDATA.HA_JSMX_TEMPAWHERENOTEXISTS(SELECT1FROMHA_JSMX_TEMP_TMPBWHEREA.ROWID=B.ROW_ID);COMMIT;--3.清空被更新的表TRUNCATETABLEEI_SRCDATA.HA_JSMX_TEMP;--4.将数据从临时表Insert到被更新的表INSERTINTOEI_SRCDATA.HA_JSMX_TEMP(表的字段列表)SELECT表的字段列表FROMHA_JSMX_TEMP_TMP;COMMIT;3、多表连接Update操作很多情况下，需要通过一张表的数据来关联更新另一张表的数据，ORACLE的关联表ORACLE数据库编程优化手册V1.0新意科技®版权所有©第5页共15页更新语法非常复杂，性能同样不佳。对这类UPDATE的优化思路与有条件的UPDATE操作相同。也是利用Oracle的ROWID字段：1、将满足Update条件的记录（包含原表中的ROWID）Insert到临时表；2、再根据ROWID，将未更新的记录Inser到临时表；3、清空被更新的表；4、将数据从临时表Insert到被更新的表；例如，下面较复杂的UPDATE操作：UPDATEEI_SRCDATA.HA_JSMX_CLASET(SETTLE_DEPT_CODE,SUB_DEPT_CODE)=(SELECTC.SETTLE_DEPT_CODE,C.SUB_DEPT_CODEFROMEA_PUB.SYS_SEAT_INFOB,EA_PUB.BR_CONTRACTCWHEREA.XWH3=B.SEAT_CODEANDB.MKT_CODE=C.MKT_CODEANDB.SEAT_CODE=C.SEAT_CODEANDB.IS_SHARE='0'ANDB.MKT_CODE=AC_I_MKT_CODE)WHEREA.SETTLE_DEPT_CODE=AC_UNKNOWN_SETTLE_DEPANDEXISTS(SELECT1FROMEA_PUB.SYS_SEAT_INFOB,EA_PUB.BR_CONTRACTCWHEREA.XWH3=B.SEAT_CODEANDB.MKT_CODE=C.MKT_CODEANDB.SEAT_CODE=C.SEAT_CODEANDB.IS_SHARE='0'ANDB.MKT_CODE=AC_I_MKT_CODE);COMMIT;可以参照下列做法：--1.将满足更新条件的数据Inser到临时表INSERT/*+APPEND*/INTOTMP_HA_JSMX_CL(ROW_ID,SCDM,JLLX,JYFS,JSFS,YWLX,QSBZ,GHLX,JSBH,CJBH,SQBH,WTBH,JYRQ,QSRQ,JSRQ,QTRQ,WTSJ,CJSJ,XWH1,XWH2,XWHY,JSHY,TGHY,ZQZH,ZQDM1,ZQDM2,ZQLB,LTLX,QYLB,GPNF,MMBZ,SL,CJSL,ZJZH,BZ,JG1,JG2,QSJE,YHS,JSF,GHF,ZGF,SXF,QTJE1,QTJE2,QTJE3,SJSF,JGDM,FJSM,MKT_CODE,SEC_TYPE,CL_SQBH,RZRQBZ,XYZH,DATA_TYPE,SETTLE_ENTITY_ID,FUND_PROP,XWH3,SETTLE_DEPT_CODE,SUB_DEPT_CODE)SELECTA.ROWIDASROW_ID,SCDM,JLLX,JYFS,JSFS,YWLX,QSBZ,GHLX,JSBH,CJBH,SQBH,WTBH,JYRQ,QSRQ,JSRQ,QTRQ,WTSJ,CJSJ,XWH1,XWH2,XWHY,JSHY,TGHY,ZQZH,ZQDM1,ZQDM2,ZQLB,LTLX,QYLB,GPNF,MMBZ,SL,CJSL,ZJZH,BZ,JG1,JG2,QSJE,YHS,JSF,GHF,ZGF,SXF,QTJE1,QTJE2,QTJE3,SJSF,JGDM