Oracle SQL性能优化深入浅出 2

SQL 语句的编写原则：
1.不要让Oracle做得太多；
   1）避免复杂的多表关联

   2）避免使用 ‘ * ‘
当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*’ 是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间；
只提取你所要使用的列；
使用别名能够加快解析速度；

   3）避免使用耗费资源的操作
带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能.DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序.

   4）用EXISTS替换DISTINCT
低效:
    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);

   5）用UNION-ALL 替换UNION ( if possible)

2.给优化器更明确的命令；
    1）自动选择索引
如果表中有两个以上（包括两个）索引，其中有一个唯一性索引，而其他是非唯一性．在这种情况下，ORACLE将使用唯一性索引而完全忽略非唯一性索引．

    2）至少要包含组合索引的第一列
如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引.

   3）避免在索引列上使用函数
WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．
低效：
SELECT …
FROM DEPT
WHERE SAL * 12 > 25000;

高效:
SELECT …
FROM DEPT
WHERE SAL  > 25000/12;

   4）避免使用前置通配符
WHERE子句中, 如果索引列所对应的值的第一个字符由通配符(WILDCARD)'%'开始, 索引将不被采用.在这种情况下，ORACLE将使用全表扫描.
例如：
SELECT USER_NO,USER_NAME,ADDRESS
FROM USER_FILES
WHERE USER_NO LIKE '%109204421';

   5）避免在索引列上使用NOT
通常，我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描.

   6）避免在索引列上使用 IS NULL和IS NOT NULL
避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引 ．对于单列索引，如果列包含空值，索引中将不存在此记录. 对于复合索引，如果每个列都为空，索引中同样不存在此
记录.　如果至少有一个列不为空，则记录存在于索引中．
如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入). 然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空. 因此你可以插入多条具有相同键值的记录,当然它们都是空!
因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引.
任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。

   7）避免出现索引列自动转换
当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换.因为内部发生的类型转换, 这个索引将不会被用到!

   8）在查询时尽量少用格式转换

3.减少访问次数；
    1）使用DECODE来减少处理时间

     2）减少对表的查询
在含有子查询的SQL语句中,要特别注意减少对表的查询.
低效
          SELECT TAB_NAME
          FROM TABLES
          WHERE TAB_NAME = ( SELECT TAB_NAME
                                FROM TAB_COLUMNS
                                WHERE VERSION = 604)
          AND　DB_VER= ( SELECT DB_VER
                           FROM TAB_COLUMNS
                           WHERE VERSION = 604)
高效
       SELECT TAB_NAME
          FROM TABLES
          WHERE  (TAB_NAME,DB_VER)
= ( SELECT TAB_NAME,DB_VER)
                   FROM TAB_COLUMNS
                   WHERE VERSION = 604)   

4.细节上的影响；
   1）WHERE子句中的连接顺序
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理, 当在WHERE子句中有多个表联接时，WHERE子句中排在最后的表应当是返回行数可能最少的表，有过滤条件的子句应放在WHERE子句中的最后。

   2）WHERE子句 ——函数、表达式使用
最好不要在WHERE子句中使用函或表达式，如果要使用的话，最好统一使用相同的表达式或函数，这样便于以后使用合理的索引。

   3）Order by语句
ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中（象联接或者附加等）。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。
仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。

   4）联接列
对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。

select * from employss 　 where 　 first_name||''||last_name ='Beill Cliton';

系统优化器对基于last_name创建的索引没有使用。

当采用下面这种SQL语句的编写，Oracle系统就可以采用基于last_name创建的索引。 　 select * from employee 　　 where 
         first_name ='Beill' and last_name ='Cliton';

   5）带通配符（%）的like语句
通配符（%）在搜寻词首出现，Oracle系统不使用last_name的索引。
select * from employee where last_name like '%cliton%';

在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了使用： 
  select * from employee where last_name like 'c%';

   6）用Where子句替换HAVING子句
避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.
低效:
     SELECT REGION，AVG(LOG_SIZE)
       FROM LOCATION
       GROUP BY REGION
       HAVING REGION REGION != ‘SYDNEY’
       AND REGION != ‘PERTH’
高效:
     SELECT REGION，AVG(LOG_SIZE)
       FROM LOCATION
       WHERE REGION REGION != ‘SYDNEY’
       AND REGION != ‘PERTH’
       GROUP BY REGION
顺序:
     WHERE > GROUP > HAVING

   7）用NOT EXISTS 替代 NOT IN
在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历).使用NOT EXISTS 子句可以有效地利用索引。尽可能使用NOT EXISTS来代替NOT IN，尽管二者都使用了NOT（不能使用索引而降低速度），NOT EXISTS要比NOT IN查询效率更高。
低效:

SELECT dname, deptno FROM dept WHERE
deptno NOT IN (SELECT deptno FROM emp);

高效：

SELECT dname, deptno FROM dept WHERE
NOT EXISTS
    (SELECT deptno FROM emp WHERE dept.deptno = emp.deptno);

2要比1的执行性能好很多。因为1中对emp进行了full table scan,这是很浪费时间的操作。而且1中没有用到emp的index， 因为没有where子句。而2中的语句对emp进行的是缩小范围的查询。

   8）用索引提高效率
索引是表的一个概念部分,用来提高检索数据的效率，ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证。
通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率. 虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引是有必要的。

   9）用>= 替代 >
高效:

   SELECT *
   FROM EMP
   WHERE DEPTNO >=4
  
低效:

   SELECT *
   FROM EMP
   WHERE DEPTNO >3
  
   10）外部联接"+"的用法
外部联接"+"按其在"="的左边或右边分左联接和右联接。若不带"+"运算符的表中的一个行不直接匹配于带"+"预算符的表中的任何行，则前者的行与后者中的一个空行相匹配并被返回。利用外部联接"+"，可以替代效率十分低下的 not in 运算，大大提高运行速度。
例如，下面这条命令执行起来很慢：

select a.empno from emp a where a.empno not in
(select empno from emp1 where job='SALE');

利用外部联接，改写命令如下:

select a.empno from emp a ,emp1 b
where a.empno=b.empno(+)
and b.empno is null
and b.job='SALE';

这样运行速度明显提高.

SQL 语句的执行步骤：
当一个用户与数据库建立了连接后，会向数据库发出操作请求，也就是向数据库送过去一条（或是几条或一个PL/SQL包）SQL语句。Oracle在接到这条SQL之后，首先会将这个SQL做一个Hash函数运算，得到一个Hash值，然后到共享池中寻找是否有和这个Hash值匹配的SQL存在。如果找到了，Oracle将直接使用已经存在的SQL的执行计划去执行当前的SQL,然后将结果返回给用户。如果在共享池中没有找到相同Hash值的SQL,Oracle会认为这是一条新的SQL,将会按照下面的顺序来执行：

a.语法分析
主要看这条SQL是否符合Oracle规定的语法规则，如果发现语法有误，将向用户抛出一个错误信息.

b.语义分析
当语法分析通过以后，Oracle将对这条SQL做一些对象、权限方面的检查，查看SQL中操作的表是否存在，表中的列是否正确，用户是否有操作这个对象的权限等。

c.生成执行计划
这个过程Oracle将通过一系列的操作，来做出最后SQL的执行计划，比如查看操作对象的统计信息，动态采样等。

d.SQL的执行
Oracle按照上一步生成的执行计划，实际地执行SQL语句，并将结果返回给用户。至此，一条SQL语句执行完毕。

对于OLTP系统来说，相同的SQL重复频率非常高，如果优化器反复解析SQL,必然极大地消耗系统资源；另外，OLTP系统用户请求的结果集都非常小，所以基本上都会考虑使用索引，那么既然大家的执行计划都一致，为什么要对SQL做重复分析呢？bind peeking在第一次获得了一个正确的执行计划之后，后续的所有SQL都按照这个执行计划来执行，可以极大地改善系统的性能，这是由OLTP系统的特性决定的。

对于OLAP系统，他的SQL执行计划和谓词的值关系极大，谓词的值不同，很可能执行计划就不同，如果都采用相同的执行计划，SQL的执行效率必然非常低；另外，一个OLAP系统数据库每天执行的SQL数量远远比OLTP少，并且SQL的重复率远远低于OLTP,这种情况下，SQL
解析花费的代价和SQL的执行花费的代价来比，完全可以忽略。

因此，对于OLAP系统，我觉得不需要做变量绑定，这样做可能发生执行计划选择错误的严重后果。另外，如果事实上的确做了变量绑定，bing peeking也只能保证第一条硬分析的SQL能够选择正确的执行计划，如果后面的谓词变量改变，很可能还是会出现SQL选择错误的执行计划，因此在OLAP系统中，不要绑定变量。