oracle_SQL优化

Oraclesql性能优化调整1.访问Table的方式ORACLE采用两种访问表中记录的方式:a.全表扫描全表扫描就是顺序地访问表中每条记录.ORACLE采用一次读入多个数据块(databaseblock)的方式优化全表扫描.b.通过ROWID访问表你可以采用基于ROWID的访问方式情况,提高访问表的效率,,ROWID包含了表中记录的物理位置信息..ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系.通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高.2.选择最有效率的表名顺序ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表drivingtable)将被最先处理.在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时,会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.例如:表TAB116,384条记录表TAB21条记录选择TAB2作为基础表(最好的方法)selectcount(*)fromtab1,tab2执行时间0.96秒选择TAB2作为基础表(不佳的方法)selectcount(*)fromtab2,tab1执行时间26.09秒如果有3个以上的表连接查询,那就需要选择交叉表(intersectiontable)作为基础表,交叉表是指那个被其他表所引用的表.例如:EMP表描述了LOCATION表和CATEGORY表的交集.SELECT*FROMLOCATIONL,CATEGORYC,EMPEWHEREE.EMP_NOBETWEEN1000AND2000ANDE.CAT_NO=C.CAT_NOANDE.LOCN=L.LOCN将比下列SQL更有效率SELECT*FROMEMPE,LOCATIONL,CATEGORYCWHEREE.CAT_NO=C.CAT_NOANDE.LOCN=L.LOCNANDE.EMP_NOBETWEEN1000AND20003.WHERE子句中的连接顺序．ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.例如:(低效,执行时间156.3秒)SELECT…FROMEMPEWHERESAL50000ANDJOB=‘MANAGER’AND25(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO);(高效,执行时间10.6秒)SELECT…FROMEMPEWHERE25(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO)ANDSAL50000ANDJOB=‘MANAGER’;4.SELECT子句中避免使用‘*‘当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用‘*’是一个方便的方法.不幸的是,这是一个非常低效的方法.实际上,ORACLE在解析的过程中,会将’*’依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间.5.减少访问数据库的次数当执行每条SQL语句时,ORACLE在内部执行了许多工作:解析SQL语句,估算索引的利用率,绑定变量,读数据块等等.由此可见,减少访问数据库的次数,就能实际上减少ORACLE的工作量.例如,以下有三种方法可以检索出雇员号等于0342或0291的职员.方法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);FETCHC1INTO…,..,..;CLOSEC1;END;方法3(高效)SELECTA.EMP_NAME,A.SALARY,A.GRADE,B.EMP_NAME,B.SALARY,B.GRADEFROMEMPA,EMPBWHEREA.EMP_NO=342ANDB.EMP_NO=291;注意:在SQL*Plus,SQL*Forms和Pro*C中重新设置ARRAYSIZE参数,可以增加每次数据库访问的检索数据量,建议值为200.6.使用DECODE函数来减少处理时间使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.例如:SELECTCOUNT(*)，SUM(SAL)FROMEMPWHEREDEPT_NO=0020ANDENAMELIKE‘SMITH%’;SELECTCOUNT(*)，SUM(SAL)FROMEMPWHEREDEPT_NO=0030ANDENAMELIKE‘SMITH%’;你可以用DECODE函数高效地得到相同结果SELECTCOUNT(DECODE(DEPT_NO,0020,’X’,NULL))D0020_COUNT,COUNT(DECODE(DEPT_NO,0030,’X’,NULL))D0030_COUNT,SUM(DECODE(DEPT_NO,0020,SAL,NULL))D0020_SAL,SUM(DECODE(DEPT_NO,0030,SAL,NULL))D0030_SALFROMEMPWHEREENAMELIKE‘SMITH%’;类似的,DECODE函数也可以运用于GROUPBY和ORDERBY子句中.7.整合简单,无关联的数据库访问如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)例如:SELECTNAMEFROMEMPWHEREEMP_NO=1234;SELECTNAMEFROMDPTWHEREDPT_NO=10;SELECTNAMEFROMCATWHERECAT_TYPE=‘RD’;上面的3个查询可以被合并成一个:SELECTE.NAME,D.NAME,C.NAMEFROMCATC,DPTD,EMPE,DUALXWHERENVL(‘X’,X.DUMMY)=NVL(‘X’,E.ROWID(+))ANDNVL(‘X’,X.DUMMY)=NVL(‘X’,D.ROWID(+))ANDNVL(‘X’,X.DUMMY)=NVL(‘X’,C.ROWID(+))ANDE.EMP_NO(+)=1234ANDD.DEPT_NO(+)=10ANDC.CAT_TYPE(+)=‘RD’;(虽然采取这种方法,效率得到提高,但是程序的可读性大大降低,所以还是要权衡之间的利弊)8.删除重复记录最高效的删除重复记录方法(因为使用了ROWID)DELETEFROMEMPEWHEREE.ROWID(SELECTMIN(X.ROWID)FROMEMPXWHEREX.EMP_NO=E.EMP_NO);9.用TRUNCATE替代DELETE当删除表中的记录时,在通常情况下,回滚段(rollbacksegments)用来存放可以被恢复的信息.如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)而当运用TRUNCATE时,回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.(TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML)10.尽量多使用COMMIT只要有可能,在程序中尽量多使用COMMIT,这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:COMMIT所释放的资源:a.回滚段上用于恢复数据的信息.b.被程序语句获得的锁c.redologbuffer中的空间d.ORACLE为管理上述3种资源中的内部花费(在使用COMMIT时必须要注意到事务的完整性,现实中效率和事务完整性往往是鱼和熊掌不可得兼)11.计算记录条数和一般的观点相反,count(*)比count(1)稍快,当然如果可以通过索引检索,对索引列的计数仍旧是最快的.例如COUNT(EMPNO)(在CSDN论坛中,曾经对此有过相当热烈的讨论,作者的观点并不十分准确,通过实际的测试,上述三种方法并没有显著的性能差别)12用Where子句替换HAVING子句避免使用HAVING子句,HAVING只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序,总计等操作.如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.例如:低效:SELECTREGION，AVG(LOG_SIZE)FROMLOCATIONGROUPBYREGIONHAVINGREGIONREGION!=‘SYDNEY’ANDREGION!=‘PERTH’高效SELECTREGION，AVG(LOG_SIZE)FROMLOCATIONWHEREREGIONREGION!=‘SYDNEY’ANDREGION!=‘PERTH’GROUPBYREGION(HAVING中的条件一般用于对一些集合函数的比较,如COUNT()等等.除此而外,一般的条件应该写在WHERE子句中)Having用法：数据表：student表结构：FieldNameDataTypeLenidint20namevarchar25majorvarchar25scoreint20sexvarchar20rn表数据：编号/姓名/专业/学分/性别idnamemajorscoresex1jakChinese40f2rainMath89m3leoPhy78f4jakMath76f5rainChinese56m6leoMath97f7jakPhy45f8jakDraw87f9leoChinese45f现在我们要得到一个视图：要求查询性别为男生，并且列出每个学生的总成绩：SQL：selects.*,sum(s.score)fromstudentswheresex=''f''groupbys.nameResult:idnamemajorscoresexsum(s.score)1jakChinese40f2483leoPhy78f220可以看到总共查到有两组，两组的学生分别是jak和leo，每一组都是同一个学生，这样我们就可以使用聚合函数了。只有使用了groupby语句，才能使用如：count()、sum()之类的聚合函数。下面我们再对上面的结果做进一步的筛选，只显示总分数大于230的学生：SQL：selects.*,sum(s.score)fromstudentswheresex=''f''groupbys.namehavingsum(s.score)230Result:idnamemajorscoresexsum(s.score)1jakChinese40f248可见having于where的功能差不多。结论：1.WHERE子句用来筛选FROM子句中指定的操作所产生的行。2.GROUPBY子句用来分组WHERE子句的输出。3.HAVING子句用来从分组的结果中筛选行。13.减少对表的查询在含有子查询的SQL语句中,要特别注意减少对表的查询.例如:低效SELECTTAB_NAMEFROMTABLESWHERETAB_NAME=(SELECTTAB_NAMEFROMTAB_COLUMNSWHEREVERSION=604)ANDDB_VER=(SELECTDB_VERFROMTAB_COLUMNSWHEREVERSION=604)高效SELECTTAB_NAMEFROMTABLESWHERE(TAB_NAME,DB_VER)=(SE