ORACLE SQL语句优化技术分析

ORACLE SQL语句优化技术分析

操作符优化

    IN 操作符

    用IN写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。

    但是用IN的SQL性能总是比较低的，从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别：

    ORACLE试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。

    推荐方案：在业务密集的SQL当中尽量不采用IN操作符。

    NOT IN操作符

    此操作是强列推荐不使用的，因为它不能应用表的索引。

    推荐方案：用NOT EXISTS 或（外连接+判断为空）方案代替

    <> 操作符（不等于）

    不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。

    推荐方案：用其它相同功能的操作运算代替，如

    a<>0 改为 a>0 or a<0

    a<>’’ 改为 a>’’

    IS NULL 或IS NOT NULL操作（判断字段是否为空）

    判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。

    推荐方案：

    用其它相同功能的操作运算代替，如

    a is not null 改为 a>0 或a>’’等。

    不允许字段为空，而用一个缺省值代替空值，如业扩申请中状态字段不允许为空，缺省为申请。

    建立位图索引（有分区的表不能建，位图索引比较难控制，如字段值太多索引会使性能下降，多人更新操作会增加数据块锁的现象）

    > 及 < 操作符（大于或小于操作符）

    大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了，因为A>2时ORACLE会先找出为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引。

    LIKE操作符

    LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE ‘%5400%’ 这种查询不会引用索引，而LIKE ‘X5400%’则会引用范围索引。一个实际例子：用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE ‘%5400%’ 这个条件会产生全表扫描，如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 则会利用YY_BH的索引进行两个范围的查询，性能肯定大大提高。

关键词：Oracle 系统全局区(SGA)，

摘 要：本文主要讲述Oracle 数据库SGA，系统的内存参数、信号参数、init.ora中参数的作用以及其大小的如何确定。

ORACLE数据库的初始参数的配置是否合适，对于数据库的运行有着极其重要的作用，轻则影响数据库的处理速度，时时导致一些操作的失败，重则使数据库崩溃．然一些重要参数究竟有什么意义，对数据库的那些功能产生影响，许多数据库管理人员和开发人员一开始都有雾里看花之感，而各参数的值究竟该如何配置，总是无所依据，无从参考，许多参考书也只是说根据数据库运行需要而定，还是没有得益．只好一切默认设置，听之任之．然安装的默认值多半不能满足系统运行需要，时间一长，随着各种应用程序和功能的展开，系统频频报错，总搞得管理开发人员精神紧张，疲于应付．下文将分析介绍ORACLE的几个重要参数，并对参数配置是否合适做出讨论．

一．在讨论具体的参数前，先介绍一下ORACLE数据库的系统全局区(SGA)．因为只有了解（SGA）才能知道一些相关参数的意义并确定其大小．

系统全局区（SGA）是一个分配给Oracle 的包含一个 Oracle 实例的数据库的控制信息内存段。主要包括数据库高速缓存(the database buffer cache)，重演日志缓存（the redo log buffer）,共享池（the shared pool）,数据字典缓存（the data dictionary cache）以及其它各方面的信息．

数据高速缓存（The database buffer cache ）用来存放从数据文件中读到的数据块的备份，所有该实例的用户进程共享数据库高速缓存重的数据，当数据已在数据库高速缓存中，用户进程就不必从磁盘做I/O．

重演日志是SGA中一个循环使用的缓存，它保留数据库的变化信息以重做数据库的操作．

共享池包含三个主要部分：库缓存（Library cache），数据字典缓存（Dictionary cache）和控制结构（Control structures）．

库缓存包括共享SQL区，私有SQL区，PL/SQL过程和包，以及象锁何苦缓存处理等控制结构． 

OACLE 用共享SQL区和私有SQL区来表示所有SQL语句，当ORACLE发现两个用户是执行统一个语句时，就重复使用共享SQL区的数据．当然，每个用户也必须有自己的SQL区．

共享SQL区为单一的或同一的SQL语句建立分析树和执行计划，ORACLE把同一的DML语句通过共享SQL区保存到内存．尤其当许多用户执行同一个应用程序时，在共享区中总保持有一个共享SQL区．

ORACLE分析完一个语句后就从共享池分配内存给它，分配内存的大小要根据语句的复杂程度而定．如果一个语句要求有新的共享SQL区，而整个共享区已都被分配，ORACLE将用最近使用（LRU）算法处理内存的分配，使共享区中有足够的空余空间来存放新的语句的共享SQL区．

私有SQL区包含的数据有编译信息，运行缓存等．任何时候产生的SQL语句都有一个私有SQL区，每个用户提交一个SQL语句就有一个独占的SQL区做他的私有SQL区；多个私有SQL区可以拥有同一个共享SQL区．

私有SQL区有一个永久区和一个运行区：永久区包含编译信息和其他一些信息，其大小要看语句中涉及的编译信息和列的多少，如：如果一个查询语句中要显示的列很多，那永久区就会较大；运行区包含一些在语句被执行时所需要的信息，永久区的大小受被执行语句的类型，复杂程度以及查询得到的行的多少的影响．一般INSERT, UPDATE, 和 DELETE语句的运行区比SELECT的运行区要小．ORACLE在语句一执行时建立运行区，对INSERT, UPDATE, 和 DELETE语句来说，执行已结束ORACLE就释放运行区，对于检索语句，则要等所有被检索行都被取完才取消运行区．

数据字典缓存存放数据库的表，视图的信息，数据库的结构，数据库的用户等．ORACLE在分析SQL语句时频繁读取这些数据字典缓存中的数据．

二．通过以上概念的了解，我们可以来分析数据库的参数中相关于系统全局区（SGA）的参数

当一个成功安装后，系统运行的性能好坏或能否正常运行，和SGA 是否足够大，SGA中各个部分是否足够有重要的关系．那么，如何来确定的尺度呢？从概念可知，SGA的最主要两部分是数据库高速缓存和共享池，在系统参数里,影响他们的分别是初始文件中的DB_BLOCK_BUFFERS和SHARED_POOL_SIZE．这是两个最重要的参数，以下逐一分析它们的设置．

DB_BLOCK_BUFFERS 

DB_BLOCK_BUFFERS用于数据库缓冲区高速缓存的缓冲区数目．数据高速缓存＝DB_BLOCK_BUFFERS*DB_BLOCK_SIZE．高速缓存越大，ORACLE 在内存中可装入的数据就越多，所需的I/O才操作就越少，数据库运行的速度性能就越好，所以在操作系统内存的许可下，DB_BLOCK_BUFFERS可尽可能设大．但如果该值设置太大，在许多情况下有负面影响．服务器的内存是一定的，即使是专用的数据库服务器，操作平台本身的进程都需要内存，在ORACLE可用的内存中，ORACLE的几个后台服务进程都需要内存，如果数据高速缓存太大，占据太多内存，就会引起系统转换和交换功能，把部分应用程序或进程移到辅存．一般SGA最好不超过可用内存的５０％．

那么如何确定DB_BLOCK_BUFFERS太大了呢，我们可以在数据库正常运行或高压运行时在SYS下运行下列语句：

SQL> select decode(state,0,’Free’,1,’Read and Modified’,2,’Read and not Modified’,

3,’Currently Being READ’,'Other’) state, 

count(*) count

from x$bh

group by state;

(a) state count

——————— 　　　　　　———-

Free 5718

Read and Modified 682

(b) state count

——————— 　　　　　　———-

Currently Being READ 　 50

Read and Modified 150

在查询结果a)中，可看到有大量闲置的数据高速缓存，意味着DB_BLOCK_BUFFERS太大了，留下太多从未有用过块．在b) 则没有闲置项．

总之，我们尽可能把DB_BLOCK_BUFFERS设大，但如果运行一段时间后系统有许多从没用过的缓存块，则说明该值太大了，应调小该值，以免占用太多内存引起系统交换，反而从别的方面影响系统运行速度．

SHARED_POOL_SIZE 

SHARED_POOL_SIZE是分配给共享SQL区的字节数，应保证共享池足够大，以使ORACLE能在共享池中保持分析和执行语句，以清除不必要的重复扫描．通过以下分析方法我们可知道是否要加大共享池．

SQL〉select sum（pins） Pins，sum（reloads） Reloads，

Sum（reloads）/（sum（pins）+sum（reloads））*100 Percentage

From v$librarycache;

PINS RELOADS PERCENTAGE

———- ———- ———-

6191 8 .129053073

Pins中的值和在此动态性能表中reloads列以及它们的关系, 是SGA中的当前库高速缓存尺寸是否最佳的最好指示．RELOADS应趋于0，PERCENTAGE若>1%，说明高速缓存的成功率太低，需要加大共享池大小来提高成功率．

LOG_BUFFERS 

LOG_BUFFERS也影响到SGA，它是分给重演日志缓冲区的字节数．如果系统的应用执行的事务较大，提交时数据量较大，则应在默认值基础上加大该值，以减少对重演日志的I/O操作．

三．在初始参数中还有两个极重要的参数即DML_LOCKS和PROCESSES.这两个参数设置不妥，就会引起数据库操作的失败．

DML_LOCKS 

DML_LOCKS是用户一次可对表设定锁的最大数目．如果有三个用户修改６个表，则需１８个DML锁来实现并行操作．如果设定DML_LOCKS不够大，操作时执行将中断．你可以通过你的应用程序的操作规模和最大的并行使用的用户数来估算系统所需要的DML_LOCKS的值．但该值的大小对数据库的其他性能没有影响，所以一般都把它设得很大，远超过实际中可能达到的值．如果在系统运行中经常发生表锁死的现象，就应该考虑加大该值．

PROCESSES

PROCESSES定义了可与ORACLE同时连接的进程数的最大值．该进程数包括ORACLE的后台进程的数据，如果用sql*plus连上数据库，则每一个连接就会产生一个进程；如果用客户端的应用程序，那么每启动一个应用至少要建一个连接，至少要产生一个进程，如果应用程序中有多个连接则产生多个进程．这个值也可根据应用程序和最大的并行用户数在加几个冗余量就可确定．系统的连接产生的进程一旦达到这最大数，系统将出错误提示搞告诉用户连接数已满．如果连接数时常达到最大值，给用户造成极大的不便，应加大该值的设置，除非管理者需要限制用户数．该值可以设得较大，但并非没有限制．对于PROCESSES的限制来自系统的资源．系统的用户数越多，系统要处理的数据库操作就多，相应的就需要有较大的数据高速缓存和共享池，才能保证用户操作数据库的速度，而系统的内存是有限的；还有，ORACLE通过自己的信号来控制协调进程，进程越多，需要的信号数就越多，如果只一味加大PROCESSES，而不加大信号参数，系统将无法启动．所以要调整PROCESSES，必须相应调整SGA和信号参数．当然调大的SGA也就要调大系统中ORACLE 可用的共享内存．该参数在操作系统的/etc/system中． 

以上是 ORACLE最主要的几个参数，当然影响ORACLE性能的因素很多，主要还是考在实践摸

Oracle SQL 性能优化技巧 

1.选用适合的ORACLE优化器 

ORACLE的优化器共有3种 

A、RULE (基于规则) b、COST (基于成本) c、CHOOSE (选择性) 

设置缺省的优化器，可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明，如RULE，COST，CHOOSE，ALL_ROWS，FIRST_ROWS 。 你当然也在SQL句级或是会话(session)级对其进行覆盖。 

为了使用基于成本的优化器(CBO， Cost-Based Optimizer) ， 你必须经常运行analyze 命令，以增加数据库中的对象统计信息(object statistics)的准确性。 

如果数据库的优化器模式设置为选择性(CHOOSE)，那么实际的优化器模式将和是否运行过analyze命令有关。 如果table已经被analyze过， 优化器模式将自动成为CBO ， 反之，数据库将采用RULE形式的优化器。 

在缺省情况下，ORACLE采用CHOOSE优化器， 为了避免那些不必要的全表扫描(full table scan) ， 你必须尽量避免使用CHOOSE优化器，而直接采用基于规则或者基于成本的优化器。 

2.访问Table的方式 

ORACLE 采用两种访问表中记录的方式： 

A、 全表扫描 

全表扫描就是顺序地访问表中每条记录。ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描。 

B、 通过ROWID访问表 

你可以采用基于ROWID的访问方式情况，提高访问表的效率， ROWID包含了表中记录的物理位置信息。ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系。通常索引提供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能上的提高。 

3.共享SQL语句 

为了不重复解析相同的SQL语句，在第一次解析之后，ORACLE将SQL语句存放在内存中。这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享。 因此，当你执行一个SQL语句(有时被称为一个游标)时，如果它和之前的执行过的语句完全相同， ORACLE就能很快获得已经被解析的语句以及最好的执行路径。ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用。 

可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering)，这个功能并不适用于多表连接查询。 

数据库管理员必须在init.ora中为这个区域设置合适的参数，当这个内存区域越大，就可以保留更多的语句，当然被共享的可能性也就越大了。 

当你向ORACLE提交一个SQL语句，ORACLE会首先在这块内存中查找相同的语句。这里需要注明的是，ORACLE对两者采取的是一种严格匹配，要达成共享，SQL语句必须完全相同(包括空格，换行等)。 

数据库管理员必须在init.ora中为这个区域设置合适的参数，当这个内存区域越大，就可以保留更多的语句，当然被共享的可能性也就越大了。 

共享的语句必须满足三个条件： 

A、 字符级的比较： 当前被执行的语句和共享池中的语句必须完全相同。 

B、 两个语句所指的对象必须完全相同： 

C、 两个SQL语句中必须使用相同的名字的绑定变量(bind variables)。 

4.选择最有效率的表名顺序(只在基于规则的优化器中有效) 

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表(基础表 driving table)将被最先处理。在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。当ORACLE处理多个表时， 会运用排序及合并的方式连接它们。首先，扫描第一个表(FROM子句中最后的那个表)并对记录进行派序，然后扫描第二个表(FROM子句中最后第二个表)，最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。 

如果有3个以上的表连接查询， 那就需要选择交叉表(intersection table)作为基础表， 交叉表是指那个被其他表所引用的表。 

5.WHERE子句中的连接顺序 

ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前， 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。 

6.SELECT子句中避免使用 ‘ * ‘ 

当你想在SELECT子句中列出所有的COLUMN时，使用动态SQL列引用 ‘*’ 是一个方便的方法。不幸的是，这是一个非常低效的方法。实际上，ORACLE在解析的过程中， 会将’*’ 依次转换成所有的列名， 这个工作是通过查询数据字典完成的， 这意味着将耗费更多的时间。 

7.减少访问数据库的次数 

当执行每条SQL语句时，ORACLE在内部执行了许多工作：解析SQL语句，估算索引的利用率，绑定变量，读数据块等等。由此可见，减少访问数据库的次数，就能实际上减少ORACLE的工作量。 

8.使用DECODE函数来减少处理时间 

使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。 

9.整合简单，无关联的数据库访问 

如果你有几个简单的数据库查询语句，你可以把它们整合到一个查询中(即使它们之间没有关系) 

10.删除重复记录 

11.用TRUNCATE替代DELETE 

当删除表中的记录时，在通常情况下， 回滚段(rollback segments ) 用来存放可以被恢复的信息。 如果你没有COMMIT事务，ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)。 

而当运用TRUNCATE时， 回滚段不再存放任何可被恢复的信息。当命令运行后，数据不能被恢复。因此很少的资源被调用，执行时间也会很短。 

12.尽量多使用COMMIT 

只要有可能，在程序中尽量多使用COMMIT，这样程序的性能得到提高，需求也会因为COMMIT所释放的资源而减少 

COMMIT所释放的资源： 

A、 回滚段上用于恢复数据的信息。 

B、被程序语句获得的锁。 

C、 redo log buffer 中的空间。 

D、ORACLE为管理上述3种资源中的内部花费。 

13.计算记录条数 

和一般的观点相反，count(*) 比count(1)稍快，当然如果可以通过索引检索，对索引列的计数仍旧是最快的。例如 COUNT(EMPNO) 

14.用Where子句替换HAVING子句 

避免使用HAVING子句，HAVING 只会在检索出所有记录之后才对结果集进行过滤。 这个处理需要排序，总计等操作。如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。 

15.减少对表的查询 

在含有子查询的SQL语句中，要特别注意减少对表的查询。 

16.通过内部函数提高SQL效率。 

17.使用表的别名(Alias) 

当在SQL语句中连接多个表时， 请使用表的别名并把别名前缀于每个Column上。这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。 

18.用EXISTS替代IN 

在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用EXISTS(或NOT EXISTS)通常将提高查询的效率。 

19.用NOT EXISTS替代NOT IN 

在子查询中，NOT IN子句将执行一个内部的排序和合并。 无论在哪种情况下，NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。为了避免使用NOT IN ，我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。 

20.用表连接替换EXISTS 

通常来说 ， 采用表连接的方式比EXISTS更有效率 

21.用EXISTS替换DISTINCT 

当提交一个包含一对多表信息(比如部门表和雇员表)的查询时，避免在SELECT子句中使用DISTINCT。 一般可以考虑用EXIST替换

1。已经检验的语句和已在共享池中的语句之间要完全一样
2。变量名称尽量一致
3。合理使用外联接
4。少用多层嵌套
5。多用并发

语句的优化步骤一般有：
1。调整sga区，使得sga区的是用最优。
2。sql语句本身的优化，工具有explain,sql trace等
3。数据库结构调整
4。项目结构调整
写语句的经验：
1。对于大表的查询使用索引
2、少用in,exist等
3、使用集合运算

１．对于大表查询中的列应尽量避免进行诸如
 Ｔｏ＿ｃｈａｒ，ｔｏ＿ｄａｔｅ，ｔｏ＿ｎｕｍｂｅｒ
 等转换
２．有索引的尽量用索引，有用到索引的条件写在前面
 如有可能和有必要就建立一些索引 
３．尽量避免进行全表扫描，限制条件尽可能多，以便更快
 搜索到要查询的数据

如何让你的SQL运行得更快
交通银行长春分行电脑部 
任亮 
—- 人们在使用SQL时往往会陷入一个误区，即太关注于所得的结果是否正确，而忽略了不同的实现方法之间可能存在的性能差异，这种性能差异在大型的或是复杂的数据库环境中（如联机事务处理OLTP或决策支持系统DSS）中表现得尤为明显。笔者在工作实践中发现，不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的where子句。在对它们进行适当的优化后，其运行速度有了明显地提高！下面我将从这三个方面分别进行总结：

—- 为了更直观地说明问题，所有实例中的SQL运行时间均经过测试，不超过１秒的均表示为（< 1秒）。

—- 测试环境– 
—- 主机：HP LH II 
—- 主频：330MHZ 
—- 内存：128兆 
—- 操作系统：Operserver5.0.4 
—-数据库：Sybase11.0.3

一、不合理的索引设计
—-例：表record有620000行，试看在不同的索引下，下面几个 SQL的运行情况： 
—- 1.在date上建有一非个群集索引

select count(*) from record where date >
19991201 and date < 19991214and amount > 
2000 (25秒)
select date,sum(amount) from record group by date
(55秒) 
select count(*) from record where date >
19990901 and place in (BJ,SH) (27秒)
—- 分析： 
—-date上有大量的重复值，在非群集索引下，数据在物理上随机存放在数据页上，在范围查找时，必须执行一次表扫描才能找到这一范围内的全部行。

—- 2.在date上的一个群集索引

select count(*) from record where date > 
19991201 and date < 19991214 and amount >
2000 （14秒）
select date,sum(amount) from record group by date
（28秒）
select count(*) from record where date >
19990901 and place in (BJ,SH)（14秒）
—- 分析： 
—- 在群集索引下，数据在物理上按顺序在数据页上，重复值也排列在一起，因而在范围查找时，可以先找到这个范围的起末点，且只在这个范围内扫描数据页，避免了大范围扫描，提高了查询速度。

—- 3.在place，date，amount上的组合索引

select count(*) from record where date >
19991201 and date < 19991214 and amount >
2000 （26秒）
select date,sum(amount) from record group by date
（27秒）
select count(*) from record where date >
19990901 and place in (BJ, SH)（< 1秒）
—- 分析： 
—- 这是一个不很合理的组合索引，因为它的前导列是place，第一和第二条SQL没有引用place，因此也没有利用上索引；第三个SQL使用了place，且引用的所有列都包含在组合索引中，形成了索引覆盖，所以它的速度是非常快的。

—- 4.在date，place，amount上的组合索引

select count(*) from record where date > 
19991201 and date < 19991214 and amount >
2000(< 1秒)
select date,sum(amount) from record group by date
（11秒）
select count(*) from record where date >
19990901 and place in (BJ,SH)（< 1秒）
—- 分析： 
—- 这是一个合理的组合索引。它将date作为前导列，使每个SQL都可以利用索引，并且在第一和第三个SQL中形成了索引覆盖，因而性能达到了最优。

—- 5.总结：

—- 缺省情况下建立的索引是非群集索引，但有时它并不是最佳的；合理的索引设计要建立在对各种查询的分析和预测上。一般来说：

—- ①.有大量重复值、且经常有范围查询

（between, >,< ，>=,< =）和order by
、group by发生的列，可考虑建立群集索引；
—- ②.经常同时存取多列，且每列都含有重复值可考虑建立组合索引；

—- ③.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。

二、不充份的连接条件：
—- 例：表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

—- 可见，只有充份的连接条件，真正的最佳方案才会被执行。

—- 总结：

—- 1.多表操作在被实际执行前，查询优化器会根据连接条件，列出几组可能的连接方案并从中找出系统开销最小的最佳方案。连接条件要充份考虑带有索引的表、行数多的表；内外表的选择可由公式：外层表中的匹配行数*内层表中每一次查找的次数确定，乘积最小为最佳方案。

—- 2.查看执行方案的方法– 用set showplanon，打开showplan选项，就可以看到连接顺序、使用何种索引的信息；想看更详细的信息，需用sa角色执行dbcc(3604,310,302)。

三、不可优化的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秒）

—- 你会发现SQL明显快起来！

—- 2.例：表stuff有200000行，id_no上有非群集索引，请看下面这个SQL：

select count(*) from stuff where id_no in(0,1) 
（23秒）
—- 分析： 
—- where条件中的in在逻辑上相当于or，所以语法分析器会将in (0,1)转化为id_no =0 or id_no=1来执行。我们期望它会根据每个or子句分别查找，再将结果相加，这样可以利用id_no上的索引；但实际上（根据showplan）,它却采用了“OR策略“，即先取出满足每个or子句的行，存入临时数据库的工作表中，再建立唯一索引以去掉重复行，最后从这个临时表中计算结果。因此，实际过程没有利用id_no上索引，并且完成时间还要受tempdb数据库性能的影响。

—- 实践证明，表的行数越多，工作表的性能就越差，当stuff有620000行时，执行时间竟达到220秒！还不如将or子句分开：

select count(*) from stuff where id_no=0
select count(*) from stuff where id_no=1
—- 得到两个结果，再作一次加法合算。因为每句都使用了索引，执行时间只有3秒，在620000行下，时间也只有4秒。或者，用更好的方法，写一个简单的存储过程： 
create proc count_stuff as
declare @a int
declare @b int
declare @c int
declare @d char(10)
begin
select @a=count(*) from stuff where id_no=0
select @b=count(*) from stuff where id_no=1
end
select @c=@a+@b
select @d=convert(char(10),@c)
print @d
—- 直接算出结果，执行时间同上面一样快！ 
—- 总结：

—- 可见，所谓优化即where子句利用了索引，不可优化即发生了表扫描或额外开销。

—- 1.任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。

—- 2.in、or子句常会使用工作表，使索引失效；如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引。

—- 3.要善于使用存储过程，它使SQL变得更加灵活和高效。

—- 从以上这些例子可以看出，SQL优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充份利用索引，减少表扫描的I/O次数，尽量避免表搜索的发生。其实SQL的性能优化是一个复杂的过程，上述这些只是在应用层次的一种体现，深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。

ORACLE的解析从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个基础表并对记录进行派序,然后扫描第二个基础表,最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并. 

Oracle 在解析计划时涉及到的条件

Where a.id=b.dptid and b.dptid=c.empid

And a.name=&pName 

And b.dptname=&pdpName 

And c.salays>&pNum

先从下再到上解析和运行。我们得能把表数据量减少的条件放在下面，把表之间的连接放到最上面

UNION操作符

    UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：

    select * from gc_dfys

    union

    select * from ls_jg_dfys

    这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。

    推荐方案：采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。

    select * from gc_dfys

    union all

    select * from ls_jg_dfys

 

    SQL书写的影响

    同一功能同一性能不同写法SQL的影响

    如一个SQL在A程序员写的为

    Select * from zl_yhjbqk

    B程序员写的为

    Select * from dlyx.zl_yhjbqk（带表所有者的前缀）

    C程序员写的为

    Select * from DLYX.ZLYHJBQK（大写表名）

    D程序员写的为

    Select *  from DLYX.ZLYHJBQK（中间多了空格）

    以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的，但是从ORACLE共享内存SGA的原理，可以得出ORACLE对每个SQL 都会对其进行一次分析，并且占用共享内存，如果将SQL的字符串及格式写得完全相同则ORACLE只会分析一次，共享内存也只会留下一次的分析结果，这不仅可以减少分析SQL的时间，而且可以减少共享内存重复的信息，ORACLE也可以准确统计SQL的执行频率。

    WHERE后面的条件顺序影响

    WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响，如

    Select * from zl_yhjbqk where dy_dj = ’1KV以下’ and xh_bz=1

    Select * from zl_yhjbqk where xh_bz=1  and dy_dj = ’1KV以下’

    以上两个SQL中dy_dj（电压等级）及xh_bz（销户标志）两个字段都没进行索引，所以执行的时候都是全表扫描，第一条SQL的dy_dj = ’1KV以下’条件在记录集内比率为99%，而xh_bz=1的比率只为0.5%，在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较，而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较，因此可以得出第二条SQL的CPU占用率明显比第一条低。