{转} Oracle SQL的优化

SQL的优化应该从 5 个方面进行 调整：

1.去掉不必要的大型表的全表扫描
2.缓存小型表的全表扫描
3.检验优化索引的使用
4.检验优化的连接技术
5.尽可能减少执行计划的 Cost

SQL语句：
是对数据库( 数据 ) 进行操作 的惟一途径；
消耗了70%~90% 的数据库资源；独立于程序设计逻辑，相对于对程序源代码的优化，对 SQL 语句的 优化在时间成本和风险上的代价都很低；
可以有不同的写法；易学，难精通。

SQL优化：
固定的SQL 书写习 惯，相同的查询尽量保持相同，存储过程的效率较高。
应该编写与其格式一致的语句，包括字母的大小写、标点符号、换 行的位置等都要一致

ORACLE优化器：
在任何可能的时候都会对 表达式进行评估，并且把特定的语法结构转换成等价的结构，这么做的原因是
要么结果表达式能够比源表达式具有更快的速度
要么源表达式只是结果表 达式的一个等价语义结构
不同的SQL 结构有时具有同样的操作（例如： = ANY (subquery) and IN (subquery) ）， ORACLE 会把他们映射到一个单一的语义结构。
1 常量优化：
常量的计算是在语句被优 化时一次性完成，而不是在每次执行时。下面是检索月薪大于2000 的的表达式：
sal > 24000/12
sal > 2000
sal*12 > 24000
如果SQL 语句包 括第一种情况，优化器会简单地把它转变成第二种。
优化器不会简化跨越比较符的表达式，例如第三条语句，鉴于此， 应尽量写用常量跟字段比较检索的表达式，而不要将字段置于表达式当中。否则没有办法优化，比如如果sal 上有索引，第一和第二就可以使用，第三就难以使 用。

2 操作符优化：
优化器把使用LIKE 操 作符和一个没有通配符的表达式组成的检索表达式转换为一个 “ = ” 操作符表达式。
例如：优化器会把表达式ename LIKE 'SMITH' 转换为 ename = 'SMITH'
优化器只能转换涉及到可 变长数据类型的表达式，前一个例子中，如果ENAME 字段的类型是 CHAR(10) ， 那么优化器将不做任何转换。

 

一般来讲LIKE 比较 难以优化。
其中：
~~ IN 操作符优化：
    优化器把使用IN 比较符 的检索表达式替换为等价的使用 “ = ” 和 “ OR ” 操作符的检索表达式。
    例如，优化器会 把表达式ename IN ('SMITH','KING','JONES') 替换为
ename = 'SMITH' OR ename = 'KING' OR ename = 'JONES‘

oracle 会将 in 后面的东西生成一张内存中的临时表。然后进行查询。

如何编写高效的SQL:
    当然要考虑sql 常量 的优化和操作符的优化啦，另外，还需要：
1 合 理的索引设计：
例：表record 有 620000 行，试看在不同的索引下，下面几个 SQL 的运行 情况：
语句A
SELECT count(*) FROM record
WHERE date >'19991201' and date <'19991214‘  and amount >2000
语句B
SELECT count(*) FROM record
WHERE date >'19990901' and place IN ('BJ','SH')
语句C
SELECT date,sum(amount) FROM record
group by date
1 在 date 上建有一个非聚集索引
A： (25 秒 )
B： (27 秒 )
C： (55 秒 )
分析：
date上有大量的重复值，在非聚集索引下，数据在物理上随机存放在数据页上，在范围查找时，必须执行一次表 扫描才能找到这一范围内的全部行。
2 在 date 上 的一个聚集索引
A：（ 14 秒）
B：（ 14 秒）
C：（ 28 秒）
分析：
在聚集索引下，数据在物 理上按顺序在数据页上，重复值也排列在一起，因而在范围查找时，可以先找到这个范围的起末点，且只在这个范围内扫描数据页，避免了大范围扫描，提高了查询 速度。
3 在 place ， date ， amount 上 的组合索引
A：（ 26 秒）
C：（ 27 秒）
B：（ <1 秒）
分析：
这是一个不很合理的组合 索引，因为它的前导列是place ，第一和第二条 SQL 没有引用 place ，因此也没有利用上索引；第三个 SQL 使用了 place ， 且引用的所有列都包含在组合索引中，形成了索引覆盖，所以它的速度是非常快的。
4 在 date ， place ， amount 上的组合索引
A：  (<1 秒 )
B：（ <1 秒）
C：（ 11 秒）
分析：
这是一个合理的组合索 引。它将date 作为前导列，使每个 SQL 都可以利用索引，并且在第一和第三个 SQL 中形 成了索引覆盖，因而性能达到了最优。

总结1
缺省情况下建立的索引是 非聚集索引，但有时它并不是最佳的；合理的索引设计要建立在对各种查询的分析和预测上。一般来说：
有大量重复值、且经常有 范围查询（between, >,< ， >=,<= ）和 order by 、 group by 发生的列，考虑建立聚集索引；
经 常同时存取多列，且 每列都含有重复值可考虑建立组合索引；在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引。比如在雇员 表的“ 性别 ” 列上只有 “ 男 ” 与 “ 女 ” 两个不同值，因此就无必要建立索引。如果建立索引不但 不会提高查询效率，反而会严重降低更新速度。
组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最 频繁的列。
2 避免使用不兼容的数据类型：
例如float 和 INt 、 char 和 varchar 、 bINary 和 varbINary 是不兼容的。数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。例如 :
SELECT name FROM employee WHERE salary ＞  60000
在这条语句中, 如 salary 字 段是 money 型的 , 则优化器很难对其进行优化 , 因为 60000 是 个整型数。我们应当在编程时将整型转化成为钱币型 , 而不要等到运行时转化。
3 IS NULL 与 IS NOT NULL ：
不 能用null 作索引，任何包含 null 值的列都将不会被包含在索引中。即使索引有多列 这样的情况下，只要这些列中有一列含有 null ，该列就会从索引中排 除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。任何在 WHERE 子 句中使用 is null 或 is not null 的语句优化器是不允 许使用索引的。
5 IN、 OR 子句常会使用工作表，使索引失效：
如果不产生大量重复值， 可以考虑把子句拆开。拆开的子句中应该包含索引。
6 避免或简化排序：
应当简化或避免对大型表 进行重复的排序。当能够利用索引自动以适当的次序产生输出时，优化器就避免了排序的步骤。以下是一些影响因素：
索引中不包括一个或几个 待排序的列；
group by或 order by 子句中列的次序与索引的次序不一样；
排序的列来自不同的表。
为了避免不必要的排序， 就要正确地增建索引，合理地合并数据库表（尽管有时可能影响表的规范化，但相对于效率的提高是值得的）。如果排序不可避免，那么应当试图简化它，如缩小排 序的列的范围等。

7 消除对大型表行数据的顺序存取：
在 嵌套查询中，对表的 顺序存取对查询效率可能产生致命的影响。比如采用顺序存取策略，一个嵌套3 层的查询，如果每层都查询 1000 行，那么这个查询就要查询  10 亿行数 据。避免这种情况的主要方法就是对连接的列进行索引。例如，两个表：学生表（学号、姓名、年龄 ?? ）和选课 表（学号、课程号、成绩）。如果两个 表要做连接，就要在 “ 学号 ” 这个连接字段上建立索引。
还可以使用并集来避免顺序存取。尽管在所有的检查列上都有索引，但某些形式的WHERE 子 句强迫优化器使用顺序存取。下面的查询将强迫对 orders 表执行顺序操作：
SELECT ＊  FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008
虽然在customer_num 和 order_num 上建有索引，但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。因为这个语句要检索的是分离 的行的集合，所以应该改为如下语句：
SELECT ＊  FROM orders WHERE customer_num=104 AND order_num>1001
UNION
SELECT ＊  FROM orders WHERE order_num=1008
这样就能利用索引路径处 理查询。

8 避免相关子查询：
一个列的标签同时在主查 询和WHERE 子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越 多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。
9 避免困难的正规表达式：
MATCHES和 LIKE 关键字支持通配符匹配，技术上叫正规表达式 。但这种匹配特别耗费时 间。

例如：SELECT  ＊  FROM customer WHERE zipcode LIKE  “ 98_ _ _ ”
即使在zipcode 字 段上建立了索引，在这种情况下也还是采用顺序扫描的方式。如果把语句改为 SELECT  ＊  FROM customer WHERE zipcode > “ 98000 ” ，在执行查询时就会利用索引来查询，显然会大大提高速度。
另外，还要避免非开始的 子串。例如语句：SELECT  ＊  FROM customer WHERE zipcode[2 ， 3] > “ 80 ” ，在 WHERE 子句中采用了非开始子串，因而这个语句也不会使用索引。

 

10 不充份的连接条件：
例：表card 有 7896 行， 在 card_no 上有一个非聚集索引，表 account 有 191122 行， 在 account_no 上有一个非聚集索引，试看在不同的表连接条件下，两个 SQL 的执行 情况：
SELECT sum(a.amount) FROM account a,card b WHERE a.card_no = b.card_no
（20 秒）
将SQL 改为：
SELECT sum(a.amount) FROM account a,card b WHERE a.card_no = b.card_no and a.account_no=b.account_no
（<1 秒）
分析：
在第一个连接条件下，最 佳查询方案是将account 作外层表， card 作内层表，利用 card 上的索引，其 I/O 次数可 由以下公式估算为：
外层表account 上的 22541 页 + （外层表 account 的 191122 行 * 内层表 card 上对应外层表第一行所要查找的 3 页） =595907 次 I/O
在第二个连接条件下，最 佳查询方案是将card 作外层表， account 作内层表，利用 account 上的索引，其 I/O 次数可 由以下公式估算为：
外层表card 上的 1944 页 + （外层表 card 的 7896 行 * 内层表 account 上对应外层表每一行所要查找的 4 页） = 33528 次 I/O
可见，只有充份的连接条 件，真正的最佳方案才会被执行。
多表操作在被实际执行前，查询优化器会根据连接条件，列出几组可能的连接方案并 从中找出系统开销最小的最佳方案。连接条件要充份考虑带有索引的表、行数多的表；内外表的选择可由公式：外层表中的匹配行数* 内层表中每 一次查找的次数确定，乘积最小为最佳方案。
不可优化的WHERE 子 句
例1
下列SQL 条件语 句中的列都建有恰当的索引，但执行速度却非常慢：
SELECT * FROM record WHERE substrINg(card_no,1,4)='5378'
(13秒 )
SELECT * FROM record WHERE amount/30<1000
（11 秒）
SELECT * FROM record WHERE convert(char(10),date,112)='19991201'
（10 秒）
分析：
WHERE子句中对列的任何操作结果都是在 SQL 运行时逐列计算得到的，因此它不得不进行表搜 索，而没有使用该列上面的索引；如果这些结果在查询编译时就能得到，那么就可以被 SQL 优化器优化，使用索引，避免表搜索，因此将 SQL 重写 成下面这样：
SELECT * FROM record WHERE card_no like '5378%'
（<1 秒）
SELECT * FROM record WHERE amount<1000*30
（<1 秒）
SELECT * FROM record WHERE date= '1999/12/01'
（<1 秒）
11 存储过程中，采用临时表优化查询：
例
1．从 parven 表中按 vendor_num 的次序读数据：
SELECT part_num， vendor_num ， price FROM parven ORDER BY vendor_num
INTO temp pv_by_vn
这个语句顺序读parven （ 50 页）， 写一个临时表（ 50 页），并排序。假定排序的开销为 200 页，总共是 300 页。
2．把临时表和 vendor 表连接，把结果输出到一个临时表，并按 part_num 排序：
SELECT pv_by_vn，＊  vendor.vendor_num FROM pv_by_vn ， vendor
WHERE pv_by_vn.vendor_num=vendor.vendor_num
ORDER BY pv_by_vn.part_num
INTO TMP pvvn_by_pn
DROP TABLE pv_by_vn
这 个查询读取pv_by_vn(50 页 ) ，它通过索引存取 vendor 表 1.5 万次， 但由于按 vendor_num 次序排列，实际上只是通过索引顺序地读  vendor 表 （ 40 ＋ 2=42 页），输出的表每页约 95 行，共 160 页。写 并存取这些页引发 5 ＊ 160=800 次的读写，索引共读写 892 页。
3．把输出和 part 连接得到最后的结果：
SELECT pvvn_by_pn.＊， part.part_desc FROM pvvn_by_pn ， part
WHERE pvvn_by_pn.part_num=part.part_num
DROP TABLE pvvn_by_pn
这样，查询顺序地读pvvn_by_pn(160 页 ) ，通过索引读 part 表 1.5 万次，由于建有索引，所以实际上进行 1772 次磁 盘读写，优化比例为 30 ∶ 1 。 ~~ ANY和 SOME  操作符优化 :
    优化器将跟随值 列表的ANY 和 SOME 检索条件用等价的同等操作符和 “ OR ” 组成的表达式替换。
    例如，优化器将如下所示的第一条语句用第二条语句替换：
    sal > ANY (:first_sal, :second_sal)
    sal > :first_sal OR sal > :second_sal
    优化器将跟随子 查询的ANY 和 SOME 检索条件转换成由 “ EXISTS ” 和一个相应的子查询组成的检索表达式。
    例如，优化器将如下所示的第一条语句用第二条语句替 换：
    x > ANY (SELECT sal FROM emp WHERE job = 'ANALYST')
    EXISTS (SELECT sal FROM emp WHERE job = 'ANALYST' AND x > sal)
~~ 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 = ANY (SELECT sal FROM emp WHERE deptno = 10))
    接下来优化器会 把第二个表达式适用ANY 表达式的转换规则转换为下面的表达式：
    NOT EXISTS (SELECT sal FROM emp WHERE deptno = 10 AND x <= sal)
~~ BETWEEN 操作符优化 :
    优化器总是用“ >= ” 和 “ <= ” 比较符来等价的代替 BETWEEN 操作符。
    例如：优化器会 把表达式sal BETWEEN 2000 AND 3000 用 sal >= 2000 AND sal <= 3000 来代替。

~~ NOT 操 作符优化 :
    优化器总是试 图 简化检索条件以消除“ NOT ” 逻辑操作符的影响，这将涉及到 “ NOT ” 操作符的消除以及代以相应的比较运算符。
    例如，优化器 将 下面的第一条语句用第二条语句代替：
    NOT deptno = (SELECT deptno FROM emp WHERE ename = 'TAYLOR')
    deptno <> (SELECT deptno FROM emp WHERE ename = 'TAYLOR')
    通常情况下一 个 含有NOT 操作符的语句有很多不同的写法，优化器的转换原则是使 “ NOT ” 操作符后边的子句尽可能的简单，即使可能会使结果表达式包含了更多的 “ NOT ” 操作符。
    例如，优化器 将如下所示的第一条语句用第二条语句代替：
    NOT (sal <1000 OR comm IS NULL)
    NOT sal <1000 AND comm IS NOT NULL sal >= 1000 AND comm IS NOT NULL