sql优化的方法及思路？ORACLE优化SQL语句,提高效率(2)
ORACLE优化SQL语句,提高效率(2)
索引是表的一个概念部分用来提高检索数据的效率 Oracle使用了一个复杂的自平衡B tree结构通常通过索引查询数据比全表扫描要快当 Oracle找出执行查询和Update语句的最好路径时 Oracle优化器将使用索引同样在联结多个表时使用索引也能提高效率另外一个使用索引的好处是他提供了主键(primary key)的唯一性验证那些LONG或LONG RAW数据类型您可以索引几乎任何的列通常在大型表中使用索引特别有效当然您也会发现在扫描小表时使用索引同样能提高效率虽然使用索引能得到查询效率的提高但是我们也必须留意到他的代价索引需要空间来存储也需要定期维护每当有记录在表中增减或索引列被撰改时索引本身也会被撰改这就意味着每条记录的INSERT DELETE UPDATE将为此多付出次的磁盘I/O因为索引需要额外的存储空间和处理那些不必要的索引反而会使查询反应时间变慢定期的重构索引是很有必要的
ALTER INDEX<INDEXNAME> REBUILD<TABLESPACENAME>
（）用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);
（）SQL语句用大写的因为Oracle总是先解析SQL语句把小写的字母转换成大写的再执行
（）在Java代码中尽可能少用连接符＋连接字符串
（）避免在索引列上使用NOT通常我们需要避免在索引列上使用NOT NOT会产生在和在索引列上使用函数相同的影响当Oracle碰到 NOT他就会停止使用索引转而执行全表扫描
（）避免在索引列上使用计算 WHERE子句中假如索引列是函数的一小部分优化器将不使用索引而使用全表扫描
举例:
低效 SELECT… FROM DEPT WHERE SAL*>;高效: SELECT… FROM DEPT WHERE SAL>/;
（）用>=替代>
高效 SELECT* FROM EMP WHERE DEPTNO>=低效: SELECT* FROM EMP WHERE DEPTNO>
两者的区别在于前者DBMS将会直接跳到第一个DEPT等于的记录而后者将首先定位到DEPTNO=的记录并且向前扫描到第一个DEPT大于的记录
（）用UNION替换OR(适用于索引列)
正常情况下用UNION替换WHERE子句中的OR将会起到较好的效果对索引列使用OR将造成全表扫描留意以上规则只针对多个索引列有效假如果有column没有被索引查询效率也许会因为您没有选择OR而降低在下面的例子中 LOC_ID和REGION上都建有索引
高效 SELECT LOC_ID LOC_DESC REGION FROM LOCATION WHERE LOC_ID= UNION SELECT LOC_ID LOC_DESC REGION FROM LOCATION WHERE REGION= MELBOURNE
低效: SELECT LOC_ID LOC_DESC REGION FROM LOCATION WHERE LOC_ID= OR REGION= MELBOURNE
（）用IN来替换OR
这是一条简单易记的规则但是实际的执行效果还须检验在Oracle i下两者的执行路径似乎是相同的:
低效:
SELECT… FROM LOCATION WHERE LOC_ID= OR LOC_ID= OR LOC_ID=
高效
SELECT… FROM LOCATION WHERE LOC_IN IN();
（）避免在索引列上使用IS NULL和IS NOT NULL
避免在索引中使用任何能为空的列 Oracle将无法使用该索引对于单列索引假如列包含空值索引中将不存在此记录对于复合索引假如每个列都为空索引中同样不存在此记录假如至少有一个列不为空则记录存在于索引中举例假如唯一性索引建立在表的A列和B列上并且表中存在一条记录的 A B值为( null) Oracle将不接受下一条具备相同A B值（ null）的记录(插入)然而假如任何的索引列都为空 Oracle将认为整个键值为空而空不等于空因此您可以插入条具备相同键值的记录当然他们都是空!因为空值不存在于索引列中所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引
低效:(索引失效)
SELECT… FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;
高效(索引有效)
SELECT… FROM DEPARTMENT WHERE DEPT_CODE>=;
（）总是使用索引的第一个列
假如索引是建立在多个列上只有在他的第一个列(leading column)被where子句引用时优化器才会选择使用该索引这亦是一条简单而重要的规则当仅引用索引的第二个列时优化器使用了全表扫描而忽略了索引
（）用UNION ALL替换UNION(假如果有大可能的话)
当SQL语句需要UNION两个查询结果集合时这两个结果集合会以UNION ALL的方式被合并然后在输出最终结果前进行排序假如用 UNION ALL替代UNION这样排序就不是必要了效率就会因此得到提高需要留意的是 UNION ALL将重复输出两个结果集合中相同记录因此各位还是要从业务需求分析使用UNION ALL的可行性 UNION将对结果集合排序这一个操作会使用到SORT_AREA_SIZE这块内存对于这块内存的优化亦是极为重要的下面的SQL可以用来查询排序的消耗量
lishixinzhi/Article/program/Oracle/201311/16789
优化SQL有什么方法
（1）SELECT子句中避免用‘*’：
Oracle在解析的过程中,会将‘*’依次转换成所有的列名,这一个工作是通过查询数据字典完成的,这就意味着将耗费更加多的时间。
（2）尽可能多使用COMMIT：
只要有大可能，在程序中尽可能多使用COMMIT,这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少，COMMIT所释放的资源:
a.回滚段上用于恢复数据的信息。
b.被程序语句获得的锁。
c.redo log buffer中的空间。
（3）用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进行过滤。由此可以看见，要想过滤条件起到正确的作用，首先需要明白这一个条
件应在什么时候起作用，然后再决定放在那里。
（4）用EXISTS替代IN、用NOTEXISTS替代NOT IN：
在许多基于基础表的查询中,为了满足一个条件,往往需要对另外一个表进行联接。
在这种场合下,使用EXISTS(或NOTEXISTS)通常将提高查询的效率。在子查询中,NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下
,NOTIN都是最低效的(因为它对子查询中的表执行了一个全表遍历)。为了避免用NOT IN,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。
例子：
（高效）
SELECT* FROM EMP(基础表)WHERE EMPNO> 0 AND EXISTS(SELECT‘X' FROM DEPT WHEREDEPT.DEPTNO= EMP.DEPTNO AND LOC=‘MELB')
(低效)
SELECT* FROM EMP(基础表) WHERE EMPNO> 0 ANDDEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC=‘MELB')
（5）用索引提高效率：
索引是表的一个概念部分，用来提高检索数据的效率，
Oracle使用了一个复杂的自平衡B-tree结构。通常,通过索引查询数据比全表扫描要快。当Oracle找出执行查询和Update语句的最佳路径时, Oracle优化器将使用索引。同样在联结多个表时使用索引也可提高效率。另外一个使用索引的好处是,它提供了主键(primary key)的唯一性验证。
那些LONG或LONG RAW数据类型,你可以索引几乎所有的列。通常,在大型表中使用索引特别有效.当然,你也会发现,在扫描小表时，使用索引同样能提高效率。虽然使用索引能得到查询效率的提高,但是我们也必须留意到它的代价。
索引需要空间来存储，也需要定期维护,每当有记录在表中增减或索引列被撰改时,索引本身也会被撰改。这就意味着每条记录的INSERT, DELETE, UPDATE将为此多付出4, 5次的磁盘I/O。因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。定期的重构索引是很有必要的：
ALTERINDEX<INDEXNAME> REBUILD<TABLESPACENAME>（18）用EXISTS替换DISTINCT：当提交一个包含一对多表信息(例如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT。一般可考虑用EXIST替换, EXISTS使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果。
例子：
(低效):
SELECTDISTINCT DEPT_NO,DEPT_NAME FROM DEPT D, EMP E WHERE D.DEPT_NO= E.DEPT_NO
(高效):
SELECTDEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS( SELECT‘X' FROM EMP E WHEREE.DEPT_NO= D.DEPT_NO);
