SQL调整与优化方案

/*******************************************************主题：SQL调整与优化方案**创建：王林**时间：2014-08-30********************************************************SQL调整的过程1.定位高频使用的SQL语句2.调整SQL语句3.添加适当的索引4.更改优化器模式5.添加适当的提示6.将调整持久化*SQL调整的目标1.去掉不必要的大表的全表扫描2.缓存小型表的全表扫描3.检验优化索引的使用4.检验优化的连接技术5.合理的减少结果集的大小******************************************************/--0.备用sql--------------------------------------------------------------------------------------------------------------------------------------------查询当前的回滚段selectvaluefromv$mystat,v$statnamewherev$mystat.statistic#=v$statname.statistic#andv$statname.name='redosize';--1.INSERT/*+append*/nologging直接路径插入-----------------------------------------------------------------------------------------------------------(a)在程序中插入会话临时表时可以采用此方法----如果想INSERT语句执行的更快，产生更少的redo，分两种情况：--(1)非归档模式：在INSERT语句中使用HINT/*+append*/--(2)归档模式：将目标表修改为NOLOGGING（altertablexxxnologging），然后在INSERT语句中使用HINT/*+append*/----由于不写日志，故插入后不进行数据备份，本操作生成的数据将不可恢复。----查看oracle数据库是否为归档模式：selectname,log_modefromv$database;--insert/*+append*/intotest_tableselect*fromdba_objects;--2.TRUNCATE表数据清空--------------------------------------------------------------------------------------------------------------------------------(a)在程序中需要清空一个表可以采用此方法----truncate是ddl操作,而delete是dml操作,delete语句删除记录时候,记录是逐条删除的,而Truncate语句删除数据时不产生回退信息;--所以如果需要删除大量数据的时候使用delete则占用较多的系统资源，而如果使用Truncate则会快的多,因为很少的资源被调用，执行时间也会很短。--delete需要把每个有数据的数据块读出来，写上delete标记,另外还要把delete的内容拷一份到undo，还要把它的动作记到redo。--而truncate,就是在系统表（或者tablespacebitmap）里面,把相关的extent的标记为未使用，另外把dba_objects(obj$)里的data_object_id换一下,--data_object_id可以理解为segment_id,truncate把以前segment丢弃，重建一个空的segment,所以object和segment的对应关系变了truncatetabletest_table;--3.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子句中避免使用“*”--------------------------------------------------------------------------------------------------------------------------Oracle在解析的过程中，会将“*”依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。--5.使用DECODE函数来减少处理时间------------------------------------------------------------------------------------------------------------------------使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。--类似的,DECODE函数也可以运用于GROUPBY和ORDERBY子句中--例如:selectcount(*),sum(sal)fromempwheredept_no='0020'andenamelike'SMITH%';selectcount(*),sum(sal)fromempwheredept_no='0030'andenamelike'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,0))d0020_sal,sum(decode(dept_no,0030,sal,0))d0030_salfromempwhereenamelike'smith%';--如果DECODE取值为NULL，SUM(NULL)的值是NULL，不会正常求和的。--6.减少访问数据库的次数-------------------------------------------------------------------------------------------------------------------------------当执行每条SQL语句时,ORACLE在内部执行了许多工作:--解析SQL语句,估算索引的利用率,绑定变量,读数据块等等.由此可见,减少访问数据库的次数,就能实际上减少ORACLE的工作量.--注意:在SQL*Plus,SQL*Forms和Pro*C中重新设置ARRAYSIZE参数,可以增加每次数据库访问的检索数据量,建议值为200--例如:以下有三种方法可以检索出雇员号等于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=342orb.emp_no=291;--7.删除重复记录---------------------------------------------------------------------------------------------------------------------------------------最高效的删除重复记录方法(因为使用了ROWID)deletefromempewheree.rowid(selectmin(x.rowid)fromempxwherex.emp_no=e.emp_no);--8.尽量多使用COMMIT-----------------------------------------------------------------------------------------------------------------------------------只要有可能，在程序中尽量多使用COMMIT，这样程序的性能得到提高，需求也会因为COMMIT所释放的资源而减少。--COMMIT所释放的资源:--a.回滚段上用于恢复数据的信息；--b.被程序语句获得的锁；--c.RedoLogBuffer中的空间；--d.Oracle为管理上述3种资源中的内部花费--在使用COMMIT时必须要注意到事务的完整性,现实中效率和事务完整性往往是鱼和熊掌不可得兼。--如果在游标里进行循环dml动作，可以考虑循环特定次数提交一次--9.减少对表的查询-------------------------------------------------------------------------------------------------------------------------------------在含有子查询的SQL语句中，要特别注意减少对表的查询。--例子：--低效selecttab_namefromtableswheretab_name=(selecttab_namefromtab_columnswhereversion=604)anddb_ver=(selectdb_verfromtab_columnswhereversion=604)--高效selecttab_namefromtableswhere(tab_name,db_ver)=(selecttab_name,db_ver)fromtab_columnswhereversion=604)--Update多个Column例子:--低效:updateempsetemp_cat=(selectmax(category)fromemp_categories),sal_range=(selectmax(sal_range)fromemp_categories)whereemp_dept=0020;--高效:updateempset(emp_cat,sal_range)=(selectmax(category),max(sal_range)fromemp_categories)whe