Oracle PL SQL程序开发与性能调优_

资源共享欢迎交流产品工程中心:吴志鹏Email:[e-mail]:966846SQL语句性能优化什么是ROWID•ROWNID是row在数据文件中的具体位置：64位编码的数据，A-Z,a-z,0-9,+,和/。如：SELECTROWID,*FROMemp什么是索引•索引是与表或视图关联的磁盘上结构，可以加快从表或视图中检索行的速度。索引包含由表或视图中的一列或多列生成的键。•它与类似书的目录结构。Oracle的“索引”对象，与表关联的可选对象，提高SQL查询语句的速度。索引直接指向包含所查询值的行的位置，减少磁盘I/O。•与所索引的表是相互独立的物理结构创建索引：createindexemp_nm_idxonemp(name);索引与表的关系SQL语句性能调优用NOTEXISTS替换NOTIN将更显著地提高效率，尽量用NOTEXISTS替代NOTIN。在子查询中，NOTIN子句将执行一个内部的排序和合并.无论在哪种情况下，NOTIN都是最低效的(因为它对子查询中的表执行了一个全表遍历).为了避免使用NOTIN，我们可以把它改写成NOTEXISTS。例如，查询所有非正常状态客户下的用户：用NOTIN：select*fromserv_2t1wheret1.cust_idnotin(selectt2.cust_idfromcust_2t2wheret2.state='70A');已选择126行。已用时间:00:01:15.12执行计划----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10FILTER21TABLEACCESS(FULL)OF'SERV_2'31TABLEACCESS(FULL)OF'CUST_2'SQL语句性能调优用NOTEXISTS：select*fromserv_2t1wherenotexists(select1fromcust_2t2wheret2.cust_id=t1.cust_idandt2.state='70A');已选择126行。已用时间:00:00:00.78执行计划----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10FILTER21TABLEACCESS(FULL)OF'SERV_2'31TABLEACCESS(BYINDEXROWID)OF'CUST_2'43INDEX(UNIQUESCAN)OF'SYS_C0017303'(UNIQUE)尽量避免在索引列上进行计算、避免在WHERE子条件对字段使用计算。WHERE子句中，如果索引列是函数的一部分。优化器将不使用索引而使用全表扫描。例如，cust_2表上有索引cust_id，现在要查询所有cust_id大于978000-1：低效的写法：select*fromcust_2twheret.cust_id+1978000;已选择164行。已用时间:00:00:00.35执行计划----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(FULL)OF'CUST_2'高效的写法：select*fromcust_2twheret.cust_id978000-1;已选择164行。已用时间:00:00:00.34执行计划----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(BYINDEXROWID)OF'CUST_2'21INDEX(RANGESCAN)OF'SYS_C0017303'(UNIQUE)不管在什么情况下，总是使用索引的第一个列（对于组合索引而言）。如果索引是建立在多个列上,只有在它的第一个列(leadingcolumn)被where子句引用时,优化器才会选择使用该索引。例如，表serv上建有组合索引“CUST_ID,SERV_TYPE,STATE”，现在要查询所有销户用户：没有使用组合索引：select*fromservtwheret.state='F0X';已选择1412行。已用时间:00:00:01.75执行计划----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(FULL)OF'SERV'使用组合索引：select*fromservtwheret.cust_id0andt.state='F0X';已选择1412行。已用时间:00:00:00.71执行计划----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(BYINDEXROWID)OF'SERV'21INDEX(RANGESCAN)OF'IDX_STATE_CUSTID_SERVTYPE'(NON-UNIQUE)在From子句中，选择有效的表名顺序(只在基于规则的优化器中有效)ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表drivingtable)将被最先处理.在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时,会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。例如，对表cust_2（10000条记录）和serv（136540条记录），按字段cust_id进行相等连接：SQL语句性能调优方法一：select*fromcust_2t1,servt2wheret1.cust_id=t2.cust_id;已选择11976行。已用时间:00:00:04.09执行计划----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10NESTEDLOOPS21TABLEACCESS(FULL)OF'SERV'31TABLEACCESS(BYINDEXROWID)OF'CUST_2'43INDEX(UNIQUESCAN)OF'SYS_C0017303'(UNIQUE)方法二：select*fromservt1,cust_2t2wheret1.cust_id=t2.cust_id;已选择11976行。已用时间:00:00:03.20执行计划----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(BYINDEXROWID)OF'SERV'21NESTEDLOOPS32TABLEACCESS(FULL)OF'CUST_2'42INDEX(RANGESCAN)OF'IDX_SERV_CUST_ID'(NON-UNIQUE)•WHERE子句中的连接顺序．ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。例如，要查询所有文圣区销户用户数：方法一：select*fromservtwheret.state='F0X'andt.organ_code='@@LYWS';已选择850行。已用时间:00:00:01.57执行计划----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(FULL)OF'SERV'方法二：select*fromservtwheret.organ_code='@@LYWS'andt.state='F0X';已选择850行。已用时间:00:00:00.64执行计划----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(FULL)OF'SERV'•使用DECODE函数来减少处理时间。使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。例如，统计文圣区的数字电视用户数和宽带用户数：方法一select*fromservtwheret.serv_type='1'andt.organ_code='@@LYWS';方法二，可以用DECODE函数高效地得到相同结果，selectcount(decode(t.serv_type,'1',1,null)),count(decode(t.serv_type,'2',1,null))fromservtwheret.organ_code='@@LYWS';类似的，DECODE函数也可以运用于GROUPBY和ORDERBY子句中。减少对表的查询在含有子查询的SQL语句中,要特别注意减少对表的查询。例如：低效的写法：select*fromcustt1wheret1.cust_id=(selectt2.cust_idfromservt2wheret2.serv_id=969303)andt1.organ_code=(selectt3.organ_codefromservt3wheret3.serv_id=969303);已用时间:00:00:00.34执行计划----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10FILTER21TABLEACCESS(BYINDEXROWID)OF'CUST'32INDEX(UNIQUESCAN)OF'SYS_C0017300'(UNIQUE)43TABLEACCESS(BYINDEXROWID)OF'SERV'54INDEX(UNIQUESCAN)OF'SYS_C0017301'(UNIQUE)61TABLEACCESS(BYINDEXROWID)OF'SERV'76INDEX(UNIQUESCAN)OF'SYS_C0017301'(UNIQUE)高效的写法select*fromcustt1where(t1.cust_id,t1.organ_code)=(selectt2.cust_id,t2.organ_codefromservt2wheret2.serv_id=969303);已用时间:00:00:00.32执行计划----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10FILTER21TABLEACCESS(BYINDEXROWID)OF'CUST'32INDEX(UNIQUESCAN)OF'SYS_C0017300'(UNIQUE)43TABLEACCESS(BYINDEXROWID)OF'SERV'54INDEX