浅析Oracle SQL性能优化doc 8页.docx
- 文档编号:29769940
- 上传时间:2023-07-26
- 格式:DOCX
- 页数:11
- 大小:51.04KB
浅析Oracle SQL性能优化doc 8页.docx
《浅析Oracle SQL性能优化doc 8页.docx》由会员分享,可在线阅读,更多相关《浅析Oracle SQL性能优化doc 8页.docx(11页珍藏版)》请在冰豆网上搜索。
浅析OracleSQL性能优化doc8页
浅析OracleSQL性能优化(doc8页)
OracleSQL性能优化
1.选择最有效率的表名顺序
例如:
表TAB116,384条记录
表TAB21条记录
选择TAB2作为基础表(最好的方法)
selectcount(*)fromtab1,tab2执行时间0.96秒
选择TAB1作为基础表(不佳的方法)
selectcount(*)fromtab2,tab1执行时间26.09秒
如果有3个以上的表连接查询,那就需要选择交叉表(intersectiontable)作为基础表,交叉表是指那个被其他表所引用的表.
例如:
EMP表描述了LOCATION表和CATEGORY表的交集.
SELECT*
FROMLOCATIONL,
CATEGORYC,
EMPE
WHEREE.EMP_NOBETWEEN1000AND2000
ANDE.CAT_NO=C.CAT_NO
ANDE.LOCN=L.LOCN
将比下列SQL更有效率
SELECT*
FROMEMPE,
LOCATIONL,
CATEGORYC
WHEREE.CAT_NO=C.CAT_NO
ANDE.LOCN=L.LOCN
ANDE.EMP_NOBETWEEN1000AND2000
2.WHERE子句中的连接顺序
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.
例如:
(低效,执行时间156.3秒)
SELECT…
FROMEMPE
WHERESAL>50000
ANDJOB=‘MANAGER'
AND25<(SELECTCOUNT(*)FROMEMP
WHEREMGR=E.EMPNO);
(高效,执行时间10.6秒)
SELECT…
FROMEMPE
WHERE25<(SELECTCOUNT(*)FROMEMP
WHEREMGR=E.EMPNO)
ANDSAL>50000
ANDJOB=‘MANAGER';
3.SELECT子句中避免使用‘*‘
4.用Where子句替换HAVING子句
避免使用HAVING子句,HAVING只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序,总计等操作.如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.
例如:
AND DB_VER=(SELECTDB_VER
FROMTAB_COLUMNS
WHEREVERSION=604)
高效
SELECTTAB_NAME
FROMTABLES
WHERE(TAB_NAME,DB_VER)
=(SELECTTAB_NAME,DB_VER)
FROMTAB_COLUMNS
WHEREVERSION=604)
Update多个Column例子:
低效:
UPDATEEMP
SETEMP_CAT=(SELECTMAX(CATEGORY)FROMEMP_CATEGORIES),
SAL_RANGE=(SELECTMAX(SAL_RANGE)FROMEMP_CATEGORIES)
WHEREEMP_DEPT=0020;
高效:
UPDATEEMP
SET(EMP_CAT,SAL_RANGE)
=(SELECTMAX(CATEGORY),MAX(SAL_RANGE)
FROMEMP_CATEGORIES)
WHEREEMP_DEPT=0020;
5.使用表的别名
当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.
6.用EXISTS替代IN
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下,使用EXISTS(或NOTEXISTS)通常将提高查询的效率.
低效:
SELECT*
FROMEMP(基础表)
WHEREEMPNO>0
ANDDEPTNOIN(SELECTDEPTNO
FROMDEPT
WHERELOC=‘MELB')
高效:
SELECT*
FROMEMP(基础表)
WHEREEMPNO>0
ANDEXISTS(SELECT‘X'
FROMDEPT
WHEREDEPT.DEPTNO=EMP.DEPTNO
ANDLOC=‘MELB')
7.用NOTEXISTS替代NOTIN
在子查询中,NOTIN子句将执行一个内部的排序和合并.无论在哪种情况下,NOTIN都是最低效的(因为它对子查询中的表执行了一个全表遍历).为了避免使用NOTIN,我们可以把它改写成外连接(OuterJoins)或NOTEXISTS.
例如:
SELECT…FROMEMP
WHEREDEPT_NONOTIN(SELECTDEPT_NO
FROMDEPT
WHEREDEPT_CAT='A');
为了提高效率.改写为:
(方法一:
高效)
SELECT….
FROMEMPA,DEPTB
WHEREA.DEPT_NO=B.DEPT(+)
ANDB.DEPT_NOISNULL
ANDB.DEPT_CAT(+)=‘A'
(方法二:
最高效)
SELECT….
FROMEMPE
WHERENOTEXISTS(SELECT‘X'
FROMDEPTD
WHERED.DEPT_NO=E.DEPT_NO
ANDDEPT_CAT=‘A');
8.用表连接替换EXISTS
通常来说,采用表连接的方式比EXISTS更有效率
SELECTENAME
FROMEMPE
WHEREEXISTS(SELECT‘X'
FROMDEPT
WHEREDEPT_NO=E.DEPT_NO
ANDDEPT_CAT=‘A');
(更高效)
SELECTENAME
FROMDEPTD,EMPE
WHEREE.DEPT_NO=D.DEPT_NO
ANDDEPT_CAT=‘A';
9.用EXISTS替换DISTINCT
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT.一般可以考虑用EXIST替换
例如:
低效:
SELECTDISTINCTDEPT_NO,DEPT_NAME
FROMDEPTD,EMPE
WHERED.DEPT_NO=E.DEPT_NO
高效:
SELECTDEPT_NO,DEPT_NAME
FROMDEPTD
WHEREEXISTS(SELECT‘X'
FROMEMPE
WHEREE.DEPT_NO=D.DEPT_NO);
EXISTS使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.
10.基础表的选择
基础表(DrivingTable)是指被最先访问的表(通常以全表扫描的方式被访问).根据优化器的不同,SQL语句中基础表的选择是不一样的.
如果你使用的是CBO(COSTBASEDOPTIMIZER),优化器会检查SQL语句中的每个表的物理大小,索引的状态,然后选用花费最低的执行路径.
如果你用RBO(RULEBASEDOPTIMIZER),并且所有的连接条件都有索引对应,在这种情况下,基础表就是FROM子句中列在最后的那个表.
举例:
SELECTA.NAME,B.MANAGER
FROM WORKERA,
LODGINGB
WHERE A.LODGING=B.LODING;
由于LODGING表的LODING列上有一个索引,而且WORKER表中没有相比较的索引,WORKER表将被作为查询中的基础表.
11.用UNION替换OR(适用于索引列)
通常情况下,用UNION替换WHERE子句中的OR将会起到较好的效果.对索引列使用OR将造成全表扫描.注意,以上规则只针对多个索引列有效.如果有column没有被索引,查询效率可能会因为你没有选择OR而降低.
在下面的例子中,LOC_ID和REGION上都建有索引.
高效:
SELECTLOC_ID,LOC_DESC,REGION
FROMLOCATION
WHERELOC_ID=10
UNION
SELECTLOC_ID,LOC_DESC,REGION
FROMLOCATION
WHEREREGION=“MELBOURNE”
低效:
SELECTLOC_ID,LOC_DESC,REGION
FROMLOCATION
WHERELOC_ID=10ORREGION=“MELBOURNE”
如果你坚持要用OR,那就需要返回记录最少的索引列写在最前面.
注意:
WHEREKEY1=10(返回最少记录)
ORKEY2=20(返回最多记录)
ORACLE内部将以上转换为
WHEREKEY1=10AND
((NOTKEY1=10)ANDKEY2=20)
12.用IN来替换OR
下面的查询可以被更有效率的语句替换:
低效:
SELECT….
FROMLOCATION
WHERELOC_ID=10
ORLOC_ID=20
ORLOC_ID=30
高效
SELECT…
FROMLOCATION
WHERELOC_ININ(10,20,30);
13.避免在索引列上使用ISNULL和ISNOTNULL
避免在索引中使用任何可以为空的列,ORACLE将无法使用该索引.对于单列索引,如果列包含空值,索引中将不存在此记录.对于复合索引,如果每个列都为空,索引中同样不存在此记录. 如果至少有一个列不为空,则记录存在于索引中.
举例:
如果唯一性索引建立在表的A列和B列上,并且表中存在一条记录的A,B值为(123,null),ORACLE将不接受下一条具有相同A,B值(123,null)的记录(插入).然而如果
所有的索引列都为空,ORACLE将认为整个键值为空而空不等于空.因此你可以插入1000
条具有相同键值的记录,当然它们都是空!
因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引.
举例:
低效:
(索引失效)
SELECT…
FROMDEPARTMENT
WHEREDEPT_CODEISNOTNULL;
高效:
(索引有效)
SELECT…
FROMDEPARTMENT
WHEREDEPT_CODE>=0;
14.总是使用索引的第一个列
16.用WHERE替代ORDERBY
ORDERBY子句只在两种严格的条件下使用索引.
ORDERBY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.
ORDERBY中所有的列必须定义为非空.
WHERE子句使用的索引和ORDERBY子句中所使用的索引不能并列.
例如:
表DEPT包含以下列:
DEPT_CODEPKNOTNULL
DEPT_DESCNOTNULL
DEPT_TYPENULL
非唯一性的索引(DEPT_TYPE)
低效:
(索引不被使用)
SELECTDEPT_CODE
FROMDEPT
ORDERBYDEPT_TYPE
EXPLAINPLAN:
SORTORDERBY
TABLEACCESSFULL
高效:
(使用索引)
SELECTDEPT_CODE
FROMDEPT
WHEREDEPT_TYPE>0
EXPLAINPLAN:
TABLEACCESSBYROWIDONEMP
INDEXRANGESCANONDEPT_IDX
译者按:
ORDERBY也能使用索引!
这的确是个容易被忽视的知识点.我们来验证一下:
15.需要当心的WHERE子句
某些SELECT语句中的WHERE子句不使用索引.这里有一些例子.
在下面的例子里,‘!
='将不使用索引.记住,索引只能告诉你什么存在于表中,而不能告诉你什么不存在于表中.
不使用索引:
SELECTACCOUNT_NAME
FROMTRANSACTION
WHEREAMOUNT!
=0;
使用索引:
SELECTACCOUNT_NAME
FROMTRANSACTION
WHEREAMOUNT>0;
下面的例子中,‘||'是字符连接函数.就象其他函数那样,停用了索引.
不使用索引:
SELECTACCOUNT_NAME,AMOUNT
FROMTRANSACTION
WHEREACCOUNT_NAME||ACCOUNT_TYPE='AMEXA';
使用索引:
SELECTACCOUNT_NAME,AMOUNT
FROMTRANSACTION
WHEREACCOUNT_NAME=‘AMEX'
ANDACCOUNT_TYPE='A';
下面的例子中,‘+'是数学函数.就象其他数学函数那样,停用了索引.
不使用索引:
SELECTACCOUNT_NAME,AMOUNT
FROMTRANSACTION
WHEREAMOUNT+3000>5000;
使用索引:
SELECTACCOUNT_NAME,AMOUNT
FROMTRANSACTION
WHEREAMOUNT>2000;
下面的例子中,相同的索引列不能互相比较,这将会启用全表扫描.
不使用索引:
SELECTACCOUNT_NAME,AMOUNT
FROMTRANSACTION
WHEREACCOUNT_NAME=NVL(:
ACC_NAME,ACCOUNT_NAME);
使用索引:
SELECTACCOUNT_NAME,AMOUNT
FROMTRANSACTION
WHEREACCOUNT_NAMELIKENVL(:
ACC_NAME,'%');
17,避免使用耗费资源的操作
带有DISTINCT,UNION,MINUS,INTERSECT,ORDERBY的SQL语句会启动SQL引擎
执行耗费资源的排序(SORT)功能.DISTINCT需要一次排序操作,而其他的至少需要执行两次排序.
例如,一个UNION查询,其中每个查询都带有GROUPBY子句,GROUPBY会触发嵌入排序(NESTEDSORT);这样,每个查询需要执行一次排序,然后在执行UNION时,又一个唯一排序(SORTUNIQUE)操作被执行而且它只能在前面的嵌入排序结束后才能开始执行.嵌入的排序的深度会大大影响查询的效率.
通常,带有UNION,MINUS,INTERSECT的SQL语句都可以用其他方式重写.
16.优化GROUPBY
提高GROUPBY语句的效率,可以通过将不需要的记录在GROUPBY之前过滤掉.下面两个查询返回相同结果但第二个明显就快了许多.
低效:
SELECTJOB,AVG(SAL)
FROMEMP
GROUPJOB
HAVINGJOB=‘PRESIDENT'
ORJOB=‘MANAGER'
高效:
SELECTJOB,AVG(SAL)
FROMEMP
WHEREJOB=‘PRESIDENT'
ORJOB=‘MANAGER'
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 浅析Oracle SQL性能优化doc 8页 浅析 Oracle SQL 性能 优化 doc