Oracle性能调优：如何对Oracle中的优化器进行评估优化

会计考友

Oracle优化器在任何可能的时候都会对表达式进行评估，并且把特定的语法结构转换成等价的结构，这么做的原因是：
    ·要么结果表达式能够比源表达式具有更快的速度。
    ·要么源表达式只是结果表达式的一个等价语义结构。
7 q( L  n# Y4 }# }+ t    不同的SQL结构有时具有同样的操作（例如：= ANY （subquery） and IN （subquery）），Oracle会把他们映射到一个单一的语义结构。
+ `5 j9 V) R! c8 X: X    下面将讨论优化器如何评估优化如下的情况和表达式：
    常量 LIKE 操作符 IN 操作符 ANY和SOME 操作符 ALL 操作符 BETWEEN 操作符 NOT 操作符
! |0 _# F, _# J! _) H/ z    传递（Transitivity） 确定性（DETERMINISTIC）函数
9 Y7 G! x$ t9 m0 r    常量
    常量的计算是在语句被优化时一次性完成，而不是在每次执行时。下面是检索月薪大于2000的的表达式： · sal > 24000/12
    · sal > 2000
1 C4 e6 s* @: s  a    · sal*12 > 24000
    如果SQL语句包括第一种情况，优化器会简单地把它转变成第二种。
    注意：优化器不会简化跨越比较符的表达式，例如第三条语句，鉴于此，应用程序开发者应该尽量写用常量跟字段比较检索的表达式，而不要将字段置于表达式当中。
' @" T: Y; o- U    LIKE 操作符
# m' [: _! l9 L2 [    优化器把使用LIKE操作符和一个没有通配符的表达式组成的检索表达式转换为一个“=”操作符表达式。
    例如：优化器会把表达式ename LIKE 'SMITH'转换为ename = 'SMITH'优化器只能转换涉及到可变长数据类型的表达式，前一个例子中，如果ENAME字段的类型是CHAR（10）， 那么优化器将不做任何转换。
1 f5 A% \% V, w7 e0 F2 b# s$ l    IN 操作符
, h7 x$ Z% e1 v7 k0 g    优化器把使用IN比较符的检索表达式替换为等价的使用“=”和“OR”操作符的检索表达式。例如，优化器会把表达式ename IN （'SMITH','KING','JONES'）替换为： ename = 'SMITH' OR ename = 'KING' OR ename = 'JONES'
    ANY和SOME 操作符
1 y/ f7 D) A% r! S' _6 ^/ `    优化器将跟随（following）值列表的ANY和SOME检索条件用等价的同等操作符和“OR”组成的表达式替换。
# y4 t1 |/ L8 V; |" p: P    例如，优化器将如下所示的第一条语句用第二条语句替换： · sal > ANY （：first_sal, :second_sal）
6 q( O9 i* N6 Y4 c    · sal > :first_sal OR sal > :second_sal
. z( [4 k- g9 z6 B. M6 A    优化器将跟随子查询的ANY和SOME检索条件转换成由“EXISTS”和一个相应的子查询组成的检索表达式。
/ P3 b( O7 J) g7 d" o7 ?+ J$ D0 a  S    例如，优化器将如下所示的第一条语句用第二条语句替换： · x > ANY （SELECT sal FROM emp WHERE job = 'ANALYST'）
( m  p0 M7 c* K* Y! P6 G) E    · EXISTS （SELECT sal FROM emp WHERE job = 'ANALYST' AND x > sal）
3 Z/ {8 m, j# }, a    ALL 操作符
    优化器将跟随值列表的ALL操作符用等价的“=”和“AND”组成的表达式替换。
    例如，sal > ALL （：first_sal, :second_sal）表达式会被替换为： sal > :first_sal AND sal > :second_sal
    对于跟随子查询的ALL表达式，优化器用ANY和另外一个合适的比较符组成的表达式替换。例如，优化器会把表达式 x > ALL （SELECT sal FROM emp WHERE deptno = 10） 替换为： NOT （x 、=之中的任何一个，常量是指任何一个涉及了操作符、SQL函数、文字、绑定变量（bind variables）或者关联变量（correlation variables）的常量表达式。
$ N) o- q7 p+ }3 n; |( D  O/ R    例如，考虑这样一个包含两个各自使用了字段EMP.DEPTNO的检索条件的WHERE子句的查询： SELECT * FROM emp, dept WHERE emp.deptno = 20 AND emp.deptno = dept.deptno;
% _/ v, `8 @0 T$ c# M1 ~, d6 r    使用传递优化，优化器会推断出如下条件：dept.deptno = 20。如果有索引存在于EMP.DEPTNO字段上，这个条件会使调用这个索引的接口路径有效。注意：优化器只能对字段关联常量的表达式进行推断，而不是 字段关联字段的表达式。例如，包含这样条件的WHERE子句：字段1 字段3 AND 字段1 = 字段2，这种情况不能推断出表达式：字段2 < comp_oper> 字段3。
% K+ V1 K5 @& o8 X2 _    确定性（DETERMINISTIC）函数
4 O& H# e+ W! l$ K7 l    在某些情况下，优化器能够使用先前的函数返回结果而不是重新执行用户定义的函数，这仅仅对那些以限制的方式来执行的函数来说是有效的。这些函数必须 对任何的输入都有同样的返回值，函数的结果必须不能因为包（PACKAGE）变量、数据库或会话（SESSION）的参数（例如NLS参数）不同而变化， 如果函数在将来重新定义，返回值必须对任何参数来说仍然与以前的返回值相同。函数的创建者可以在以CREATE FUNCTION、CREATE PACKAGE或者CREATE TYPE声明函数时根据以上的要求使用DETERMINISTIC关键字向数据库申明该函数为确定性函数，数据库不会对确定性函数的合法性进行校验，即使 一个函数明显的使用了包变量或操作了数据库，仍然可以被定义为确定性函数，这就是说如何安全合法的使用和定义确定性函数是程序员的责任。
- n) u* J9 ^/ P8 [( ]/ \8 {    当确定性函数在同一个查询里被多次调用，或者被基于函数的索引或物化视图（materialized view）调用时，有可能被一个已经计算出的值取代。