Oracle[转]Oracle SQL性能优化

本文转自:http://www.cnblogs.com/rootq/archive/2008/11/17/1334727.html

(1)      选择最有效率的表名顺序(只以冲规则之优化器中行之有效):
 ORACLE的解析器按照从右到左的依次处理FROM子句子被之表名,FROM子句被描绘以最终的说明(基础表
driving
table)将于无限优先处理,在FROM子句被包含多单说明底气象下,你要挑选记录条数最少的表作为基础表。如果发3个以上之表连接查询,
那便待选择交叉表(intersection table)作为基础表,
交叉表是乘好吃另外表所引用的表.
 (2)      WHERE子句被的连天顺序.:
 ORACLE采用自下而上的逐条解析WHERE子句,根据是规律,表中的接连要写以其它WHERE条件之前,
那些可以过滤掉最深数额记录之尺码得写于WHERE子句的末尾.
 (3)      SELECT子句被避免用 ‘ * ‘:
 ORACLE以解析的长河遭到, 会将’*’ 依次转换成所有的列名,
这个工作是透过询问数据字典完成的, 这意味着将消耗更多之光阴
 (4)      减少访问数据库的次数:
 ORACLE以其间实行了成千上万干活: 解析SQL语句, 估算索引的利用率, 绑定变量 ,
读数据块等;
 (5)      在SQL*Plus , SQL*Forms和Pro*C中重新安装ARRAYSIZE参数,
可以加每次数据库访问的检索数据量 ,建议值为200
 (6)      使用DECODE函数来压缩处理时:
 使用DECODE函数可以避免重复扫描相同记录或还连接相同之表.
 (7)      整合简单,无关联的数据库访问:
 如果你产生几乎单简易的数据库查询语句,你可管它们构成到一个询问中(即使它中间从未涉嫌)
 (8)      删除重复记录:
 最迅速之去除重复记录方法 ( 因为使用了ROWID)例子:
 DELETE  FROM  EMP E  WHERE  E.ROWID > (SELECT MIN(X.ROWID)
FROM  EMP X  WHERE  X.EMP_NO = E.EMP_NO);
 (9)      用TRUNCATE替代DELETE:
 当删除表中的笔录时,在平常情况下, 回滚段(rollback segments )
用来存放可以让恢复的信息.
如果您从未COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地即恢复至实施删除命令之前的状况)
而当以TRUNCATE时,
回滚段不再存放任何可叫恢复的信息.当令运行后,数据未可知为恢复.因此很少的资源让调用,执行时呢会见死短.
(译者按: TRUNCATE只以去全表适用,TRUNCATE是DDL不是DML)
 (10) 尽量多以COMMIT:
 只要出或,在次中尽量多使用COMMIT,
这样程序的属性得到加强,需求为会见坐COMMIT所放出的资源使压缩:
COMMIT所释放的资源:
a. 回滚段及用以恢复数据的信息.
b. 给先后报句获得的缉
c. redo log buffer 中之空中
d. ORACLE为管理上述3种植资源被之中间花费
 (11) 用Where子句替换HAVING子句:
 避免用HAVING子句, HAVING 只会以搜寻出富有记录后才对结果集进行过滤.
这个处理要排序,总计等操作.
如果会透过WHERE子句限制记录之数据,那就是可知减当下面的开销.
(非oracle中)on、where、having这三只都可加以条件的子句中,on是长执行,where次之,having最后,因为on是先期把未符合条件的记录过滤后才开展统计,它就是可减少中间运算而拍卖的数据,按理说应该速度是极度抢的,where也应当比having快点的,因为她过滤数据后才进行sum,在有限个表联接时才用on的,所以于一个申明的时候,就剩下where跟having比较了。在马上单表查询统计的图景下,如果要是过滤的标准没关系到如果计算字段,那她的结果是如出一辙的,只是where可以采取rushmore技术,而having就不能够,在速度达到后者要缓慢而要是干到计算的字段,就意味着于尚未算之前,这个字段的值是免确定的,根据达篇写的做事流程,where的图时是在计算之前就是水到渠成的,而having就是当算后才起作用的,所以当这种景象下,两者的结果会不同。在多表联接查询时,on比where更早于作用。系统第一冲各国个表之间的连条件,把多单表合成一个临时表后,再由where进行过滤,然后再计,计算完后再度由having进行过滤。由此可见,要惦记过滤条件由至对的作用,首先要明这法应该以啊时起作用,然后再决定在那里
 (12) 减少对表的查询:
 在含有子查询的SQL语句被,要特别注意减少对表的查询.例子:
      SELECT  TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = (
SELECT
 TAB_NAME,DB_VER FROM  TAB_COLUMNS  WHERE  VERSION = 604)
 (13) 通过内函数提高SQL效率.:
 复杂的SQL往往牺牲了实践效率.
能够掌握上面的运函数解决问题之道在其实工作着凡异常有含义的
 (14) 使用表的别名(Alias):
 当在SQL语句被连续多个表时,
请使用表的别名并拿别名前缀于每个Column上.这样一来,就好减解析的日子连缩减那些由Column歧义引起的语法错误.
 (15) 用EXISTS替代IN、用NOT EXISTS替代NOT IN:
 在很多基于基础表的询问中,为了满足一个准,往往得对其它一个说明进行联接.在这种情景下,
使用EXISTS(或NOT EXISTS)通常以加强查询的频率. 在子查询中,NOT
IN子句以行一个中的排序和合并. 无论在啊种情景下,NOT IN都是最低效的
(因为其对查询中之阐明执行了一个全表遍历). 为了避免采用NOT IN
,我们好将其改变写成他接连(Outer Joins)或NOT EXISTS.
 例子:
 (高效)SELECT * FROM  EMP (基础表)  WHERE  EMPNO > 0  AND  EXISTS
(SELECT ‘X’  FROM DEPT  WHERE  DEPT.DEPTNO = EMP.DEPTNO  AND  LOC =
‘MELB’)
 (低效)SELECT  * FROM  EMP (基础表)  WHERE  EMPNO > 0  AND  DEPTNO
IN(SELECT DEPTNO  FROM  DEPT  WHERE  LOC = ‘MELB’)
 (16) 识别’低效执行’的SQL语句:
 虽然手上各种有关SQL优化的图形化工具层出不穷,但是写有团结之SQL工具来化解问题始终是一个极好之点子:
 SELECT  EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM  V$SQLAREA
WHERE  EXECUTIONS>0
AND  BUFFER_GETS > 0
AND  (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY  4 DESC;
 
 
 
(17) 用索引提高效率:
 索引是说明的一个定义有,用来增强检索数据的效率,ORACLE使用了一个扑朔迷离的自平衡B-tree结构.
通常,通过索引查询数据较全表扫描要快.
当ORACLE找有尽查询以及Update语句之特级路线时, ORACLE优化器将使用索引.
同样于集合多只表时使用索引也堪提高效率.
另一个运索引的功利是,它提供了主键(primary
key)的唯一性验证.。那些LONG或LONG RAW数据类型, 你得索引几乎拥有的列.
通常, 在大型表中使用索引特别有效. 当然,你啊会见发现,
在围观小表时,使用索引同样会提高效率.
虽然应用索引能赢得查询效率的增进,但是咱吧不能不小心到它们的代价.
索引需要空间来储存,也需要定期维护, 每当发生记录在表明中增减或探寻引列被涂改时,
索引自吗会见被修改. 这表示各国条记下之INSERT , DELETE ,
UPDATE将为是多付出4 , 5 次的磁盘I/O .
因为索引需要分外的囤积空间和拍卖,那些无必要之目反而会如查询反应时间变慢.。定期的重构索引是产生必不可少之.:
 ALTER  INDEX <INDEXNAME> REBUILD <TABLESPACENAME>
 (18) 用EXISTS替换DISTINCT:
 当提交一个分包一针对性多表信息(比如单位表和雇员表)的查询时,避免在SELECT子句被采取DISTINCT.
一般可考虑用EXIST替换, EXISTS
使查询更为迅速,因为RDBMS核心模块将在子查询的原则而满足后,立刻返结果.
例子:
        (低效):
SELECT  DISTINCT  DEPT_NO,DEPT_NAME  FROM  DEPT D , EMP E
WHERE  D.DEPT_NO = E.DEPT_NO
(高效):
SELECT  DEPT_NO,DEPT_NAME  FROM  DEPT D  WHERE  EXISTS ( SELECT ‘X’
FROM  EMP E  WHERE E.DEPT_NO = D.DEPT_NO);
 (19)
sql语句用小写的;因为oracle总是先解析sql语句,把小写的假名转换成大写的双重实施
 (20) 在java代码中尽量少用连接符“+”连接字符串!
 (21) 避免在索引列上利用NOT 通常, 
 我们只要避以索引列上行使NOT, NOT会来在与在索引列上用函数相同之影响.
当ORACLE”遇到”NOT,他虽会停止使用索引转而推行全表扫描.
 (22) 避免在索引列上动计算.
 WHERE子句被,如果搜索引列是函数的一律总统分.优化器将无行使索引而使全表扫描.
举例:
低效:
SELECT … FROM  DEPT  WHERE SAL * 12 > 25000;
高效:
SELECT … FROM DEPT WHERE SAL > 25000/12;
 (23) 用>=替代>
 高效:
SELECT * FROM  EMP  WHERE  DEPTNO >=4
低效:
SELECT * FROM EMP WHERE DEPTNO >3
双方的界别在,
前者DBMS将直接跨越到第一独DEPT等让4之笔录而后者以第一定位到DEPTNO=3的记录同时上扫描到第一只DEPT大于3之记录.
 (24) 用UNION替换OR (适用于索引列)
 通常状态下, 用UNION替换WHERE子句被的OR将见面起至比好之效益.
对索引列使用OR将导致全表扫描. 注意, 以上规则仅对多个寻引列有效.
如果发生column没有被索引, 查询效率可能会见以你无选OR而降低.
在底下的例子中, LOC_ID 和REGION上还建来追寻引.
高效:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10
UNION
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE REGION = “MELBOURNE”
低效:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10 OR REGION = “MELBOURNE”
设若你坚持要为此OR, 那便待回到记录最少的索引列写于无限前面.
 (25) 用IN来替换OR 
 这是一致长长的简单好记之平整,但是其实的施行效果还须检验,在ORACLE8i下,两者的实践路径似乎是一模一样之. 
 低效:
SELECT…. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID =
30
高效
SELECT… FROM LOCATION WHERE LOC_IN  IN (10,20,30);
 (26) 避免在索引列上使用IS NULL和IS NOT NULL
 避免以目中行使其它可以为空的排列,ORACLE将无法使用该索引.对于单列索引,如果列包含空值,索引中以未设有是记录.
对于复合索引,如果每个列都为空,索引中一致无在这记录. 如果至少发生一个列不也空,则记录在于索引中.举例:
如果唯一性索引建立在表的A列和B列上,
并且表中设有一样漫漫记下的A,B值为(123,null) ,
ORACLE将未收受下一致久有相同A,B值(123,null)的笔录(插入).
然而使有的索引列都为空,ORACLE将当整个键值为空而空不等于空.
因此你可以插1000 长具有同样键值的笔录,当然她还是空!
因为空值不在于索引列中,所以WHERE子句被对索引列进行空值比较将如ORACLE停用该索引.
 低效: (索引失效)
SELECT … FROM  DEPARTMENT  WHERE  DEPT_CODE IS NOT NULL;
疾: (索引有效)
SELECT … FROM  DEPARTMENT  WHERE  DEPT_CODE >=0;
 (27) 总是用索引的率先个列:
 如果索引是建以多独列上, 只有在它们的首先个列(leading
column)被where子句引用时,优化器才见面择采取该索引.
这也是如出一辙长简单而重要之平整,当就援引索引的第二单列时,优化器使用了全表扫描而忽略了目录
 (28) 用UNION-ALL 替换UNION ( 如果发或的语):
 当SQL语句需要UNION两只查询结果集合时,这有限独结实集合会以UNION-ALL的不二法门受合,
然后在输出最终结果眼前开展排序. 如果因此UNION ALL替代UNION,
这样排序虽无是必备了. 效率就会就此收获增强. 需要注意的凡,UNION ALL
将重输出两只结果集合中平等记录. 因此各位还是要打事情需分析利用UNION
ALL的来头. UNION
将针对结果集合排序,这个操作会动用到SORT_AREA_SIZE这块内存.
对于这块内存的优化也是一对一重要之. 下面的SQL可以为此来查询排序的消耗量
 低效:
SELECT  ACCT_NUM, BALANCE_AMT
FROM  DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ’31-DEC-95′
UNION
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ’31-DEC-95′
高效:
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ’31-DEC-95′
UNION ALL
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ’31-DEC-95′
 (29) 用WHERE替代ORDER BY:
 ORDER BY 子句只在少种植严格的准下使用索引.
ORDER BY中保有的排必须包含在同等的目录中连维持以目中之排顺序.
ORDER BY中负有的排必须定义也非空.
WHERE子句以的目录和ORDER BY子句被所运用的目录不克连列.
 例如:
表DEPT包含以下列:
DEPT_CODE PK NOT NULL
DEPT_DESC NOT NULL
DEPT_TYPE NULL
 低效: (索引不吃利用)
SELECT DEPT_CODE FROM  DEPT  ORDER BY  DEPT_TYPE
快捷: (使用索引)
SELECT DEPT_CODE  FROM  DEPT  WHERE  DEPT_TYPE > 0
 (30) 避免改变索引列的类型.:
 当比较不同数据类型的数量时, ORACLE自动对列进行简短的类型转换.
如果 EMPNO是一个数值类的目列.
SELECT …  FROM EMP  WHERE  EMPNO = ‘123′
实际上,经过ORACLE类型转换, 语句转化为:
SELECT …  FROM EMP  WHERE  EMPNO = TO_NUMBER(‘123′)
有幸的凡,类型转换没有发出在索引列上,索引的用尚未受改变.
现在,假设EMP_TYPE是一个字符类型的目列.
SELECT …  FROM EMP  WHERE EMP_TYPE = 123
这讲话被ORACLE转换为:
SELECT …  FROM EMP  WHERETO_NUMBER(EMP_TYPE)=123
以中发生的类型转换, 这个目录将不见面吃用到!
为了避免ORACLE对君的SQL进行隐式的类型转换, 最好把类型转换用显式表现出来.
注意当字符和数值比较时, ORACLE会优先转换数值类及字符类型
 (31) 需要警惕的WHERE子句:
 某些SELECT 语句被之WHERE子句不使用索引. 这里发出局部例子.
每当下面的事例里, (1)‘!=’ 将不使用索引. 记住,
索引只能报您啊在于表中, 而非克告诉你呀不有于表中. (2)
‘||’是字符连接函数. 就象外函数那样, 停用了查找引. (3) ‘+’是数学函数.
就象外数学函数那样, 停用了搜索引.
(4)相同之索引列不可知相互比较,这将会启用全表扫描.
 (32) a.
如果搜索数据量超过30%底表中记录数.使用索引将无明白的频率增高.
b. 在一定情景下, 使用索引也许会较全表扫描慢, 但这是跟一个数量级及的区别.
而平凡情况下,使用索引比全表扫描要块几倍增甚至几千倍增!
 (33) 避免使耗费资源的操作:
 带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎
执行耗费资源的排序(SORT)功能. DISTINCT需要平等不好排序操作,
而其他的至少需要实行两不行排序. 通常, 带有UNION, MINUS ,
INTERSECT的SQL语句都可以为此外方法再次写.
如果您的数据库的SORT_AREA_SIZE调配得好, 使用UNION , MINUS,
INTERSECT也是得设想的, 毕竟她的可读性很强
 (34) 优化GROUP BY:
 提高GROUP BY 语句子的频率, 可以经过以无待之笔录在GROUP BY
之前过滤掉.下面两单查询返回相同结果但次只引人注目就是急匆匆了许多.
 低效:
SELECT JOB , AVG(SAL)
FROM EMP
GROUP JOB
HAVING JOB = ‘PRESIDENT’
OR JOB = ‘MANAGER’
高效:
SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB = ‘PRESIDENT’
OR JOB = ‘MANAGER’
GROUP JOB

 

相关文章