Oracle查询性能优化（⾯试题：数据库查询优化也是常问的问
题）
原则⼀：注意WHERE⼦句中的连接顺序：
ORACLE采⽤⾃下⽽上的顺序解析WHERE⼦句，根据这个原理，表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最⼤数量记录的条件必须写在WHERE⼦句的末尾.
尤其是“主键ID=？”这样的条件。
原则⼆： SELECT⼦句中避免使⽤ ‘ * ‘：
ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个⼯作是通过查询数据字典完成的, 这意味着将耗费更多的时间。
简单地讲，语句执⾏的时间越短越好（尤其对于系统的终端⽤户来说）。⽽对于查询语句，由于全表扫描读取的数据多，尤其是对于⼤型表不仅查询速度慢，⽽且对磁盘IO造成⼤的压⼒，通常都要避免，⽽避免的⽅式通常是使⽤索引Index。
使⽤索引的优势与代价。
优势：
1）索引是表的⼀个概念部分,⽤来提⾼检索数据的效率，ORACLE使⽤了⼀个复杂的⾃平衡B-tree结构. 通常，通过索引查询数据⽐全表扫描要快. 当ORACLE出执⾏查询和Update语句的最佳路径时, ORACLE优化器将使⽤索引. 同样在联结多个表时使⽤索引也可以提⾼效率. 2）另⼀个使⽤索引的好处是，它提供了主键(primary key)的唯⼀性验证.。那些LONG或LONG RAW数据类型, 你可以索引⼏乎所有的列. 通常, 在⼤型表中使⽤索引特别有效. 当然，你也会发现, 在扫描⼩表时，使⽤索引同样能提⾼效率.
代价：虽然使⽤索引能得到查询效率的提⾼，但是我们也必须注意到它的代价. 索引需要空间来存储，也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本⾝也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理，那些不必要的索引反⽽会使查询反应时间变慢.。⽽且表越⼤，影响越严重。
使⽤索引需要注意的地⽅：
1、避免在索引列上使⽤NOT ，　
我们要避免在索引列上使⽤NOT, NOT会产⽣在和在索引列上使⽤函数相同的影响. 当ORACLE”遇到”NOT，他就会停⽌使⽤索引转⽽执⾏全表扫描.
2、避免在索引列上使⽤计算．
WHERE⼦句中，如果索引列是函数的⼀部分．优化器将不使⽤索引⽽使⽤全表扫描．举例:
低效:SELECT … FROM DEPT WHERE SAL *12>25000;
⾼效:SELECT … FROM DEPT WHERE SAL >25000/12;
3、避免在索引列上使⽤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;
4、注意通配符%的影响
使⽤通配符的情况下Oracle可能会停⽤该索引。如 :
SELECT…FROM DEPARTMENT WHERE DEPT_CODE like ‘%123456%'（⽆效）。
SELECT…FROM DEPARTMENT WHERE DEPT_CODE = ‘123456'（有效）
5、避免改变索引列的类型.:
当⽐较不同数据类型的数据时, 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会优先转换数值类型到字符类型
6、索引的⼀些“脾⽓”
a. 如果检索数据量超过30%的表中记录数.使⽤索引将没有显著的效率提⾼.
b. 在特定情况下, 使⽤索引也许会⽐全表扫描慢, 但这是同⼀个数量级上的区别. ⽽通常情况下，使⽤索引⽐全表扫描要块⼏倍乃⾄⼏千倍!
除了使⽤索引，我们还有其他能减少资源消耗的⽅法：
1、⽤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
And E.sex =man
(⾼效): SELECT DEPT_NO,DEPT_NAME FROM DEPT D
WHERE EXISTS
( SELECT ‘X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO
And E.sex =man
sql语句优化方式
);
2、⽤(UNION)UNION ALL替换OR (适⽤于索引列)
通常情况下, ⽤UNION替换WHERE⼦句中的OR将会起到较好的效果. 对索引列使⽤OR将造成全表扫描.
注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR⽽降低. 在下⾯的例⼦中, LOC_ID 和REGION上都建有索引.
如果你坚持要⽤OR, 那就需要返回记录最少的索引列写在最前⾯.
⾼效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID =10UNION AL
L
SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION = “MELBOURNE”
低效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID =10OR REGION = “MELBOURNE”
3、⽤UNION-ALL 替换UNION ( 如果有可能的话)：
当 SQL语句需要UNION两个查询结果集合时，这两个结果集合会以UNION-ALL的⽅式被合并, 然后在输出最终结果前进⾏排序. 如果⽤UNION ALL替代UNION, 这样排序就不是必要了. 效率就会因此得到提⾼. 需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录.因此各位还是要从业务需求分析使⽤UNION ALL的可⾏性. UNION 将对结果集合排序，这个操作会使⽤到SORT_AREA_SIZE这块内存. 对于这块内存的优化也是相当重要的.
4、Order By语句加在索引列，最好是主键PK上。
SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE（低效）
SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_CODE （⾼效）
5、避免使⽤耗费资源的操作:
带有DISTINCT,UNION,MINUS,INTERSECT的SQL语句会启动SQL引擎执⾏耗费资源的排序(SORT)功能. DISTINCT需要⼀次排序操作,⽽其他的⾄少需要执⾏两次排序. 通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以⽤其他⽅式重写. 如果你的数据库的
SORT_AREA_SIZE调配得好, 使⽤UNION , MINUS, INTERSECT也是可以考虑的, 毕竟它们的可读性很强
6、使⽤Where替代Having（如果可以的话）
优化GROUP BY:
提⾼GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下⾯两个查询返回相同结果但第⼆个明显就快了许多.
低效:
SELECT JOB , AVG(SAL)
FROM EMP GROUP JOB HAVING JOB = ‘PRESIDENT'AND AVG(SAL)>XXX
⾼效:
SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB = ‘PRESIDENT'
OR JOB = ‘MANAGER' GROUP JOB Having AND AVG(SAL)>XXX
7、通常来说，如果语句能够避免⼦查询的使⽤，就尽量不⽤⼦查询。因为⼦查询的开销是相当昂贵的。具体的例⼦在后⾯的案例“⼀条SQL 的优化过程”中。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。