oracle多表关联的优化问题

1、ORACLE.。口人多表查询优化这里提供的是执行性能的优化而不是后台数据库优化器资料参考数据库开发性能方面的各种问题,收集了一些优化方案统计如下（当然,象索引等优化方案太过简单就不列入了,嘿嘿）:执行路径的这个功能大大地提高了的执行性能并节省了内存的使用我们发现单表数据的统计比多表统计的速度完全是两个概念单表统计可能只要秒但是张表联合统计就可能要几十表了这是因为只对简单的表提供高速缓冲这个功能并不适用于多表连接查询数据库管理员必须在中为这个区域设置合适的参数当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了.当你向提交一个语句会首先在这块内存中查找相同的语句这里需要注明的是

2、对两者采取的是一种严格匹配要达成共享语句必须完全相同包括空格换行等共享的语句必须满足三个条件字符级的比较当前被执行的语句和共享池中的语句必须完全相同例如和下列每一个都不同两个语句所指的对象必须完全相同如何访用户对象名如何访考虑一下下列语句能否在这两个用户之间共享能否共享原因不能每个用户都有一个它们是不同的对象能两个用户访问相同的对象不能用户通过访问而是表的所有者,对象不同.两个语句中必须使用相同的名字的绑定变量例如：第一组的两个语句是相同的可以共享而第二组中的两个语句是不同的（即使在运行时,赋于不同的绑定变量相同的值）重点关注选择最有效率的表名顺序只在基于规则的优化器中有效重点关注的解析器按照

3、从右到左的顺序处理子句中的表名因此子句中写在最后的表基础表将被最先处理在子句中包含多个表的情况下你必须选择记录条数最少的表作为基础表当处理多个表时会运用排序及合并的方式连接它们首先扫描第一个表子句中最后的那个表并对记录进行派序然后扫描第二个表子句中最后第二个表最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.例如:表TAB11条6记,录384表TAB21条记录选择作为基础表最好的方法执行时间秒选择作为基础表不佳的方法执行时间秒如果有个以上的表连接查询那就需要选择交叉表作为基础表交叉表是指那个被其他表所引用的表例如表描述了表和表的交集EMPEWHEREE.EMP_NOBETWEEN

4、1000ANDANDE.CAT_NO=C.CAT_NOANDE.LOCN=L.LOCN将比下列SL更有效率SELECT*FROMEMPE,LOCATIONL,CATEGORYCWHEREE.CAT_NO=C.CAT_NOANDE.LOCN=L.LOCNANEEMNOBETEENAN重点关注子句中的连接顺序.重点关注ORACLE采用自下而上的顺序解析ER子句根据这个原理表之间的连接必须写在其他ERft件之前那些可以过滤掉最大数量记录的条件必须写在ERE子句的末尾.例如:(低效,执行时间156秒.)3SELECTFROMEMPEWHERESAL;50000ANDJOB=MANAGERAND25(S

5、ELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO);(高效,执行时间10.秒6)SELECTFROMEMPEWHERE25;50000ANDJOB=MANAGER;重点关注SELEC子句中避免使用*.重点关注当你想在SELECT子句中列出所有的COLM时使用动态S列引用*是一个方便的方法不幸的是这是一个非常低效的方法实际上ORACLE在解析的过程中会将*依次转换成所有的列名这个工作是通过查询数据字典完成的这意味着将耗费更多的时间减少访问数据库的次数当执行每条SI语句时ORACLE在内部执行了许多工作解析S语句估算索引的利用率绑定变量读数据块等等由此可见减少访问数据库的次

6、数就能实际上减少ORACLE的工作量例如以下有三种方法可以检索出雇员号等于034或2029的1职员.方法1（最低效）SELECTEMP_NAME,SALARY,GRADEFROMEMPWHEREEMP_NO=342;SELECTEMP_NAME,SALARY,GRADEFROMEMPWHEREEMP_NO=291;方法2(次低效)DECLARECURSORC1(E_NONUMBER)ISSELECTEMP_NAME,SALARY,GRADEFROMEMPWHEREEMP_NO=E_NO;BEGINOPENC1(342);FETCHC1INTO,.,.;OPENC1(291);FETCHC1IN

7、TO,.,.;CLOSEC1;END;方法3(高效)DE,B.GRADE参数也可以增加每次SELECTA.EMP_NAME,A.SALARYDE,B.GRADE参数也可以增加每次B.EMP_NAME,B.SALFROMEMPA,EMPBWHEREA.EMP_NO=342ANDB.EMP_NO=291;注意:在SLP,SL和印中重新设置数据库访问的检索数据量,建议值为200.重点关注使用DECODE函数来减少处理时间.重点关注使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.例如SELECTCOUNT(*，)SUM(SAL)FROMEMPWHEREDEPT_NO=0020ANDEN

8、AMELIKESMITH%;SELECTCOUNT(*，)SUM(SAL)FROMEMPWHEREDEPT_NO=0030ANDENAMELIKESMITH%;你可以用DECODE函数高效地得到相同结果SELECTCOUNT(DECODE(DEPT_NO,0020,X,NULL)D0020_COUN,TCOUNT(DECODE(DEPT_NO,0030,X,NULL)D0030_COUNT,SUM(DECODE(DEPT_NO,0020,SAL,NULL)D0020_SASUM(DECODE(DEPT_NO,0030,SAL,NULL)D0030_SAFROMEMPWHEREENAMELIKE

9、SMITH%;类似的EOD数也可以运用于ROP和ORER子句中重点关注5:删除重复记录.重点关注最高效的删除重复记录方法因为使用了ROWIDELETEFROMEMPEWHEREE.ROWID;(SELECTMIN(X.ROWID)FROMEMPXWHEREX.EMP_NO=E.EMP_NO);重点关注用TRNAT替代ELETE重点关注当删除表中的记录时在通常情况下回滚段用来存放可以被恢复的信息如果你没有OMMIT事务ORALE会将数据恢复到删除之前的状态准确地说是恢复到执行删除命令之前的状况而当运用TRNAT时回滚段不再存放任何可被恢复的信息当命令运行后数据不能被恢复因此很少的资源被调用执行时

10、间也会很短译者按TRNAE4删除全表适用TRNATE不是ML重点关注尽量多使用OMMIT重点关注只要有可能在程序中尽量多使用OMMIT这样程序的性能得到提高需求也会因为OMMIT所释放的资源而减少OMMIT所释放的资源回滚段上用于恢复数据的信息被程序语句获得的锁中的空间ORALE为管理上述种资源中的内部花费译者按在使用OMMIT时必须要注意到事务的完整性现实中效率和事务完整性往往是鱼和熊掌不可得兼)重点关注8:减少对表的查询重重点关注在含有子查询的SI语句中要特别注意减少对表的查询例如:低效SELECTTAB_NAMEFROMTABLESWHERETAB_NAME=(SELECTTAB_NAM

11、EFROMTAOLUMNSERSION=WHERANDDB_VER=(SELECTDB_VERFROMTAB_COLUMNSWHEREVERSION=604)高效SELECTTAB_NAMEFROMTABLESWHERE(TAB_NAME,DB_VER)=(SELECTTAB_NAME,DB_VER)WHEREVERSION=604)(SELECTMAX(CATEGORY)FROMWHEREVERSION=604)(SELECTMAX(CATEGORY)FROME=(SELECTMAX(SAL_RANGE)T=0020;ROM多个C例子低效:UPDATEEMPSETEMP_CAT=EMP_CA

12、TEGORIES),SAL_RANGEMP_CATEGORIES)WHEREEMP_DEP高效:UPDATEEMPSET(EMP_CAT,SAL_RANGE)=(SELECTMAX(CATEGORY),MAX(SAL_RANGE)ROMEMP_CATEGORIES)WHEREEMP_DEPT=重点关注用EXISTS替代IN重点关注在许多基于基础表的查询中为了满足一个条件往往需要对另一个表进行联接在这种情况下使用EXISTS(或NOTEXISTS)通常将提高查询的效率低效SELECT*FROMEMP基(础表)WHEREEMPNO;0ANDDEPTNOIN(SELECTDEPTNOFROMDEPT

13、WHERELOC=MELB)高效用SELECT*ROMEM基础表)WHEREEMPNO;0ANDEXISTS(SELECTXFROMDEPTWHEREDEPT.DEPTNO=EMP.DEPTNOANDLOC=MELB)(译者按相对来说用NOTEXISTS替换NOTIN将更显著地提高效率下一节中将指出)重点关注用NOTEXISTS替代NOTIN.重点关注在子查询中NOTIN子句将执行一个内部的排序和合并.无论在哪种情况下NOTIN都是最低效的(因为它对子查询中的表执行了一个全表遍历).为了避免使用NOTIN我们可以把它改写成外连接(O或NOTEXISTS.例如SELECTFROMEMPWHEREDEPT_NONOTIN(SELECTDEPT_NOFROMDEPTWHEREDEPT_CAT=A);为了提高效率.改写为:(方法一:高效)SELECT.FROMEMPA,DEPTBWHEREA.DEPT_NO=B.DEPT(+)ANDB.DEPT_NOISNULLANDB.DEPT_CAT(+)=A(方法二:最高效)SELECT.FROMEMPEWHERENOTEXISTS(SELECTXFROMDEPTDWHERED.DEPT_NO=E.DEPT_NOANDDEPT_CAT=A);当然,最高效率的方法是有表关联.直接两表关系对联的速度是最快的!重点关注识别低效执行的SL