《SQL优化原则和技巧.ppt》由会员分享，可在线阅读，更多相关《SQL优化原则和技巧.ppt（30页珍藏版）》请在三一办公上搜索。
1、SQL优化原则和技巧 2,撰写人:石霖,本次培训的一些说明,本次内容是对上次培训的一些补充感谢王相提供的SQL性能优化的一些技巧本次培训会讲到一个和SQL性能无关的话题，但很有用，即Oracle对时间的应用,共享SQL语句,为了不重复解析相同的SQL语句,在第一次解析之后,ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存.可以被所有的数据库用户共享.因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同,ORACLE就能很快获得已经被解析的语句以及最
2、好的执行路径.ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用,共享SQL语句,我们来看下面的例子SQL select count(*)from report_sale_full;COUNT(*)-663066Executed in 0.86 secondsSQL select count(*)from report_sale_full;COUNT(*)-663066Executed in 0.062 seconds,共享SQL语句,可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering),这个功能并不适用于多表连接查询再来看一个例子SQL selec
3、t count(*)from dictionary_distributor,report_stock;COUNT(*)-310506564Executed in 29.563 secondsSQL select count(*)from dictionary_distributor,report_stock;COUNT(*)-310506564Executed in 33.188 seconds这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须完全相同(包括空格换行、大小写等),整合简单,无关联的数据库访问,如果你有几个简单的数据库查询语句,你可以把它们整合到
4、一个查询中(即使它们之间没有关系)例如：SELECT NAME from dictionary_channel where code=D;SELECT NAME from dictionary_cable where code=2;SELECT NAME from dictionary_brand where code=HYDR;,整合简单,无关联的数据库访问,可以整合为：SELECT E.NAME,D.NAME,C.NAME FROM dictionary_brand C,dictionary_cable D,dictionary_channel E,DUAL XWHERE NVL(X,X.
5、DUMMY)=NVL(X,E.ROWID(+)AND NVL(X,X.DUMMY)=NVL(X,D.ROWID(+)AND NVL(X,X.DUMMY)=NVL(X,C.ROWID(+)AND E.code(+)=D AND D.code(+)=2 AND C.code(+)=HYDR;注意:虽然采取这种方法,效率得到提高,但是程序的可读性大大降低,所以读者还是要权衡之间的利弊.,删除重复记录,最高效的删除重复记录方法(因为使用了ROWID)看下面的例子：DELETE FROM TEMP_GOODS E WHERE E.ROWID(SELECT MIN(X.ROWID)FROM TEMP_GO
6、ODS X WHERE X.CODE=E.CODE AND X.OUCODE=E.OUCODE AND X.DISTRIBUTOR=E.DISTRIBUTOR);,总是使用索引的第一个列,如果索引是建立在多个列上,只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引.这是一条简单而重要的规则当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引,避免改变索引列的类型,当比较不同数据类型的数据时,ORACLE自动对列进行简单的类型转换例如：假设EMPNO是一个数值类型的索引列 SELECT*FROM EMP WHERE EMPNO=123;实际上
7、,经过ORACLE类型转换,语句转化为:SELECT*FROM EMPWHERE EMPNO=TO_NUMBER(123);幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变.,避免改变索引列的类型,现在,假设EMP_TYPE是一个字符类型的索引列.SELECT*FROM EMP WHERE EMP_TYPE=123;这个语句被ORACLE转换为:SELECT*FROM EMP WHERE TO_NUMBER(EMP_TYPE)=123;因为内部发生的类型转换,这个索引将不会被用到!为了避免ORACLE对你的SQL进行隐式的类型转换,最好把类型转换用显式表现出来.注意当字符和数值比较时
8、,ORACLE会优先转换字符类型.,需要当心的WHERE子句,某些SELECT语句中的WHERE子句不使用索引.这里有一些例子不使用索引:SELECT*FROM REPORT_SALE_ACCOUNT WHERE SL 0;使用索引:SELECT*FROM REPORT_SALE_ACCOUNTWHERE SL 0 OR SL 0;,需要当心的WHERE子句,不使用索引:SELECT*FROM REPORT_SALE_ACCOUNT WHERE OUCODE|DISTRIBUTOR=1171027336;使用索引:SELECT*FROM REPORT_SALE_ACCOUNT WHERE OU
9、CODE=117 AND DISTRIBUTOR=1027336;,需要当心的WHERE子句,不使用索引:SELECT*FROM REPORT_SALE_ACCOUNT WHERE SL+5 20;使用索引:SELECT*FROM REPORT_SALE_ACCOUNT WHERE SL 15;,基于函数的索引,如果一定要对使用函数的列启用索引,ORACLE新的功能:基于函数的索引(Function-Based Index)也许是一个较好的方案例如:SELECT*FROM REPORT_SALE_ACCOUNT WHERE NVL(OUCODE,#)=117;,Oracle对时间的应用（1）,
10、认识转换函数与时间操作关系最大有两个转换函数TO_DATE()作用将字符类型按一定格式转化为日期类型TO_CHAR()将日期转按一定格式换成字符类型,Oracle对时间的应用（2）,TO_DATE()函数格式：TO_DATE(XXX,YYY)XXX:字符型的日期YYY:字符型日期格式返回日期型举例：TO_DATE(2007-10-18,YYYY-MM-DD)2007-10-18:字符型日期YYYY-MM-DD:字符型日期格式,Oracle对时间的应用（3）,TO_CHAR()函数格式：TO_CHAR(XXX,YYY)XXX:日期YYY:字符型日期格式返回字符型举例：TO_CHAR(SYSDAT
11、E,YYYY-MM-DD)SYSDATE:日期YYYY-MM-DD:字符型日期格式,Oracle对时间的应用（4）,常用的日期格式YYYY：四位表示的年份 YYY，YY，Y：年份的最后三位、两位或一位，缺省为当前世纪 MM：0112的月份编号 MONTH：九个字符表示的月份，右边用空格填补 MON：三位字符的月份缩写 WW：一年中的星期 D：星期中的第几天 DD：月份中的第几天 DDD：年所中的第几天 DAY：九个字符表示的天的全称，右边用空格补齐 HH，HH12：一天中的第几个小时，12进制表示法 HH24：一天中的第几个小时，取值为0023 MI：一小时中的分钟 SS：一分钟中的秒 SSS
12、S：从午夜开始过去的秒数,Oracle对时间的应用（5）,日期函数应用的几个例子求某天是星期几SELECT TO_CHAR(SYSDATE,DAY)FROM DUAL;返回值是：星期四SELECT TO_CHAR(SYSDATE,DAY,NLS_DATE_LANGUAGE=American)FROM DUAL;返回值是：THURSDAY 好，那么求某天处于今年的第几周呢？,Oracle对时间的应用（6）,两个日期间的天数 select floor(sysdate-to_date(20070405,yyyymmdd)from dual;提问：这里为什么要用到FLOOR函数？,Oracle对时间的
13、应用（7）,Next_day的用法语法：Next_day(date,day)作用：求DATE后的下一个星期几是多少DATE:日期型参数DAY:1-7 1为星期日7为星期一问题：求今天后的下一个星期一是多少？select next_day(sysdate,7)from dual;,Oracle对时间的应用（8）,Trunc对日期的用法语法：Truanc(date,XXX)DATE:日期型参数XXX:日期格式,Oracle对时间的应用（9）,Trunc对日期的一些例句转换当前日期为纯日期型select trunc(sysdate)from dual求今天所在周的星期一select trunc(sy
14、sdate,day)from dual;求今天所在月的第一天select trunc(sysdate,month)from dual;求今天所在年的第一天select trunc(sysdate,year)from dual;,Oracle对时间的应用（10）,ADD_MONTHS的用法语法：ADD_MONTHS(DATE,NUMBER)DATE:日期型参数NUMBER:数字型参数作用:从一个日期值增加或减少一些月份,Oracle对时间的应用（11）,ADD_MONTHS的例句今天后过三个月的日期select ADD_MONTHS(sysdate,3)from dual今天后前五个月的日期select ADD_MONTHS(sysdate,-5)from dual,Oracle对时间的应用（12）,LAST_DAY的用法语法：LAST_DAY(DATE)DATE:日期型参数作用:返回包含了日期参数的月份的最后一天的日期例句：SELECT LAST_DAY(SYSDATE)FROM DUAL,Oracle对时间的应用（13）,给大家的一个问题请求出以今天为参数的以下表格值：,Oracle对时间的应用（13）,公布答案,完,
