Oracle[转]Oracle SQL质量优化

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

(一)      选拔最有效能的表名顺序(只在根据规则的优化器中央银卓有成效):
 ORACLE的解析器遵照从右到左的11处理FROM子句中的表名,FROM子句中写在最终的表(基础表
driving
table)将被初叶处理,在FROM子句中涵盖多个表的情景下,你必须挑选记录条数最少的表作为基础表。假如有二个以上的表连接查询,
那就须要选用交叉表(intersection table)作为基础表,
交叉表是指那些被其它表所引用的表.
 (二)      WHERE子句中的连接顺序.:
 ORACLE采纳自下而上的相继解析WHERE子句,依据这一个规律,表之间的总是必须写在其余WHERE条件此前,
那四个能够过滤掉最大数目记录的准绳必须写在WHERE子句的末尾.
 (三)      SELECT子句中防止使用 ‘ * ‘:
 ORACLE在解析的进程中, 会将’*’ 依次转换到全体的列名,
那么些工作是通过询问数据字典完毕的, 这表示将消耗更加多的时间
 (四)      收缩访问数据库的次数:
 ORACLE在里边实施了更仆难数办事: 解析SQL语句, 估算索引的利用率, 绑定变量 ,
读数据块等;
 (5)      在SQL*Plus , SQL*Forms和Pro*C中重新安装A陆风X8RAYSIZE参数,
能够追加每一次数据库访问的摸索数据量 ,提出值为200
 (六)      使用DECODE函数来压缩处理时间:
 使用DECODE函数能够制止再一次扫描相同记录或再一次连接相同的表.
 (七)      整合简单,无涉及的数据库访问:
 假诺您有多少个不难的数据库查询语句,你能够把它们构成到3个询问中(尽管它们之间一贯不提到)
 (捌)      删除重复记录:
 最火速的去除重复记录方法 ( 因为运用了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)
 (十) 尽量多应用COMMIT:
 只要有希望,在先后中尽量多使用COMMIT,
那样程序的性质拿到抓好,供给也会因为COMMIT所释放的能源而缩减:
COMMIT所释放的能源:
a. 回滚段上用来恢复生机数据的音讯.
b. 被先后语句得到的锁
c. redo log buffer 中的空间
d. ORACLE为治本上述三种能源中的内部开支
 (1一) 用Where子句替换HAVING子句:
 幸免选择HAVING子句, HAVING 只会在搜索出具有记录之后才对结果集进行过滤.
这些处理须要排序,总结等操作.
若是能因而WHERE子句限制记录的数目,那就能减弱那方面包车型大巴费用.
(非oracle中)on、where、having那多个都得以加条件的子句中,on是起首执行,where次之,having最终,因为on是先把不符合条件的笔录过滤后才开始展览计算,它就能够收缩中间运算要处理的数量,按理说应该速度是最快的,where也理应比having快点的,因为它过滤数据后才开始展览sum,在三个表联接时才用on的,所以在3个表的时候,就剩下where跟having相比较了。在那单表查询总括的气象下,要是要过滤的基准从不关联到要总计字段,那它们的结果是均等的,只是where能够采纳rushmore技术,而having就无法,在速度上后者要慢假使要提到到总结的字段,就象征在没总计从前,那个字段的值是不分明的,依据上篇写的办事流程,where的机能时间是在盘算在此之前就达成的,而having正是在测算后才起效果的,所以在这种意况下,两者的结果会不一样。在多表联接查询时,on比where更早起效果。系统率先遵照各样表之间的连结条件,把四个表合成3个一时表后,再由where进行过滤,然后再总括,总结完后再由having举行过滤。总之,要想过滤条件起到科学的效劳,首先要领会这几个标准应该在什么样时候起效果,然后再决定放在这里
 (1二) 减弱对表的询问:
 在含有子查询的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往往捐躯了推行作用.
能够控制上面包车型客车施用函数化解难点的措施在实质上中国人民解放军海军工程学院业作中是很是有意义的
 (1四) 使用表的别称(Alias):
 当在SQL语句中总是多个表时,
请使用表的外号并把别称前缀于每一个Column上.那样一来,就能够收缩解析的时光并减少那么些由Column歧义引起的语法错误.
 (15) 用EXISTS替代IN、用NOT EXISTS替代NOT IN:
 在无数依照基础表的查询中,为了满意2个规范,往往须要对另三个表进行联接.在这种情状下,
使用EXISTS(或NOT EXISTS)平时将抓牢查询的成效. 在子查询中,NOT
IN子句将履行三个里头的排序和合并. 无论在哪一类情景下,NOT IN都以最低效的
(因为它对子查询中的表执行了3个全表遍历). 为了防止采取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’)
 (1六) 识别’低效执行’的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;
 
 
 
(一柒) 用索引进步作用:
 索引是表的2个概念部分,用来提高法索数据的频率,ORACLE使用了3个犬牙交错的自平衡B-tree结构.
平日,通过索引查询数据比全表扫描要快.
当ORACLE找出执行查询和Update语句的极品途径时, ORACLE优化器将使用索引.
同样在集合五个表时使用索引也得以进步效用.
另三个运用索引的便宜是,它提供了主键(primary
key)的唯一性验证.。那个LONG或LONG RAW数据类型, 你可以索引大约全部的列.
经常, 在大型表中使用索引尤其有效. 当然,你也会发觉,
在扫描小表时,使用索引同样能进步功能.
固然选择索引能博得查询效能的增长,不过大家也亟须注意到它的代价.
索引要求空间来囤积,也亟需定期维护, 每当有记录在表中增减或索引列被涂改时,
索引本身也会被修改. 那象征每条记下的INSE哈弗T , DELETE ,
UPDATE将为此多付出4 , 5 次的磁盘I/O .
因为索引须要额外的囤积空间和拍卖,那个不供给的目录反而会使查询反应时间变慢.。定期的重构索引是有不可缺少的.:
 ALTER  INDEX <INDEXNAME> REBUILD <TABLESPACENAME>
 (18) 用EXISTS替换DISTINCT:
 当提交一个带有1对多表音信(比如单位表和雇员表)的询问时,防止在SELECT子句中动用DISTINCT.
一般能够设想用EXIST替换, EXISTS
使查询更为高效,因为奥迪Q伍DBMS核心模块将在子查询的准绳一旦满足后,立即回去结果.
例子:
        (低效):
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);
 (1九)
sql语句用小写的;因为oracle总是先解析sql语句,把小写的字母转换来大写的再实践
 (20) 在java代码中尽量少用连接符“+”连接字符串!
 (二一) 避免在索引列上选用NOT 常常, 
 大家要幸免在索引列上使用NOT, NOT会发出在和在索引列上应用函数相同的影响.
当ORACLE”遭遇”NOT,他就会结束使用索引转而实施全表扫描.
 (2贰) 制止在索引列上选用总结.
 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将一贯跳到第3个DEPT等于4的记录而后人将率先定位到DEPTNO=三的笔录同时向前扫描到第2个DEPT大于三的记录.
 (二4) 用UNION替换O汉兰达 (适用于索引列)
 平日状态下, 用UNION替换WHERE子句中的OHummerH二将会起到较好的效应.
对索引列使用O兰德Highlander将造成全表扫描. 注意, 以上规则只针对三个索引列有效.
要是有column未有被索引, 查询功能只怕会因为你未有选用OSportage而降低.
在下边包车型大巴例子中, 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”
借使你百折不回要用OPRADO, 那就供给回到记录最少的索引列写在最前面.
 (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);
 (二陆) 幸免在索引列上运用IS NULL和IS NOT NULL
 制止在目录中选用其他可以为空的列,ORACLE将一点都不大概使用该索引.对于单列索引,倘诺列包含空值,索引中校不存在此记录.
对于复合索引,若是各类列都为空,索引中一样不设有此记录. 若是至少有一个列不为空,则记录存在于索引中.举例:
固然唯一性索引建立在表的A列和B列上,
并且表中设有一条记下的A,B值为(1二三,null) ,
ORACLE将不收受下一条具有相同A,B值(1贰三,null)的记录(插入).
不过只要全部的索引列都为空,ORACLE将认为凡事键值为空而空不等于空.
因而你能够插入一千 条具有同样键值的记录,当然它们都以空!
因为空值不设有于索引列中,所以WHERE子句中对索引列实行空值比较将使ORACLE停用该索引.
 低效: (索引失效)
SELECT … FROM  DEPARTMENT  WHERE  DEPT_CODE IS NOT NULL;
急忙: (索引有效)
SELECT … FROM  DEPARTMENT  WHERE  DEPT_CODE >=0;
 (27) 总是采用索引的率先个列:
 如若索引是树立在多个列上, 只有在它的首先个列(leading
column)被where子句引用时,优化器才会选用使用该索引.
那也是一条不难而根本的条条框框,当仅援引索引的第三个列时,优化器使用了全表扫描而忽略了目录
 (2八) 用UNION-ALL 替换UNION ( 如果有望的话):
 当SQL语句要求UNION三个查询结果集合时,那七个结果集合会以UNION-ALL的方法被统一,
然后在出口最后结果前举办排序. 假如用UNION ALL替代UNION,
那样排序就不是不可或缺了. 效能就会由此赢得抓实. 要求小心的是,UNION ALL
将重新输出八个结实集合中同样记录. 由此各位还是要从工作必要分析应用UNION
ALL的动向. UNION
将对结果集合排序,这么些操作会采用到SORubiconT_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:
 O纳瓦拉DE宝马X5 BY 子句只在三种严格的尺度下使用索引.
O冠道DELX570 BY中保有的列必须含有在同壹的目录中并有限援救在目录中的排列顺序.
OHavalDE帕杰罗 BY中负有的列必须定义为非空.
WHERE子句使用的目录和ORAV4DEQX56 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是3个数值类型的目录列.
SELECT …  FROM EMP  WHERE  EMPNO = ‘123′
实在,经过ORACLE类型转换, 语句转化为:
SELECT …  FROM EMP  WHERE  EMPNO = TO_NUMBER(‘123′)
侥幸的是,类型转换未有发生在索引列上,索引的用处尚未被改变.
现在,假设EMP_TYPE是一个字符类型的目录列.
SELECT …  FROM EMP  WHERE EMP_TYPE = 123
其1讲话被ORACLE转换为:
SELECT …  FROM EMP  WHERETO_NUMBER(EMP_TYPE)=123
因为个中发生的类型转换, 那一个目录将不会被用到!
为了防止ORACLE对您的SQL举行隐式的类型转换, 最棒把类型转换用显式表现出来.
注意当字符和数值相比时, ORACLE会优先转换数值类型到字符类型
 (3一) 须求警惕的WHERE子句:
 有些SELECT 语句中的WHERE子句不使用索引. 那里有一些例子.
在底下的事例里, (壹)‘!=’ 将不使用索引. 记住,
索引只好告诉您如何存在于表中, 而不能够告诉您什么不设有于表中. (2)
‘||’是字符连接函数. 就象别的函数那样, 停用了索引. (3) ‘+’是数学函数.
就象其余数学函数那样, 停用了索引.
(四)相同的索引列不能够相互比较,那将会启用全表扫描.
 (3贰) a.
如若搜索数据量超越十分之三的表中记录数.使用索引将尚未强烈的频率提升.
b. 在特定情景下, 使用索引恐怕会比全表扫描慢, 但那是同三个数码级上的区别.
而平日状态下,使用索引比全表扫描要块好多倍甚至几千倍!
 (3三) 防止使用开支财富的操作:
 带有DISTINCT,UNION,MINUS,INTE本田UR-VSECT,O瑞虎DEXC90 BY的SQL语句会运维SQL引擎
履行开销能源的排序(SO大切诺基T)成效. DISTINCT必要三回排序操作,
而其余的起码必要实施五回排序. 平时, 带有UNION, MINUS ,
INTE猎豹CS陆SECT的SQL语句都足以用别样格局重写.
固然你的数据库的SORT_AREA_SIZE调配得好, 使用UNION , MINUS,
INTE大切诺基SECT也是足以思量的, 终究它们的可读性很强
 (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

 

相关文章