Oracle SQL地优化

Oracle SQL的优化

标签：oraclesql优化date数据库subquery

2009-10-14 21:18 18149人阅读评论(21) 收藏举报分类：

Oracle Basic Knowledge（208）

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 消除对大型表行数据的顺序存取：

在嵌套查询中，对表的顺序存取对查询效率可能产生致命的影响。比如采用顺