oracle sql语句优化

一般的SQL优化技巧

1.避免使用“*”代替所有列

在使用select语句查询一个表的所有信息时，用户会经常选择使用“*”来代替所有列，尤其是在表中的列比较多，而且列名又难记的时候。而使用“*”代替列名，会给SQL语句的编写带来方便，却会给SQL语句的执行带来麻烦。首先了解SQL语句的执行步骤：

1.语法分析与编译

在语法分析与编译阶段，oracle会使用哈希函数为该SQL语句在库缓存中分配一个SQL区，然后在该SQL区搜索该语句是否存在，如果已经存在则查询数据字典，检查必须的存取权限等；如果不存在，则分析、编译用户的SQL语句。

2.执行

对SQL语句进行分析与编译，了解SQL语句的内容后，执行该SQL语句。

3.存取数据

将数据从数据文件中读取出来并存放到数据缓冲区中，或者将数据缓冲区中的数据写入数据文件中。

小结：用“*”查询的时候oracle系统需要首先通过数据字典来将语句中的“*”转换成相应表的所有列名，这自然要比直接使用列名花费更多的时间。

2.用truncate代替delete

在使用delete语句删除表中的所有数据行时，oracle会对这些行进行逐行的删除，并且使用回滚段来记录删除操作。

提示：通过上面的介绍可以发现两个问题，比较明显的问题是，在使用delete删除时，oracle需要花费时间去记录删除操作；而另一个问题则不是那么明显，那就是delete删除采取的是逐行删除的形式，也就是说所有的数据行并不是同一时间被删除的，这就要求在数据行的删除过程中，oracle系统不能出现什么意外情况导致删除操作中止。

如果确定要删除表中的所有行，建议使用truncate语句。使用truncate语句删除表中的所有数据行时，oracle不会在撤销表空间中记录删除操作。这就提高了语句的执行速度，而且这种删除时一次性的，也就是所有的数据行是在同一时间被删除。

3.在确保完整性的情况下多用commit语句

在前面的内容中已经介绍过，当用户执行DML操作后，如果不使用commit命令进行提交，则oracle会在回滚段中记录DML操作，以便用户在使用rollback命令时对数据进行恢复。oracle实现这种数据回滚功能，自然要花费相应的时间与空间。

所以，在确保数据完整性的情况下，尽量及时的使用commit命令对DML操作进行提交。

提示：使用commit命令后，系统将释放回滚段上的记录DML操作信息、被程序语句获得的锁、redo log buffer中的空间以及oracle系统管理前面3种资源所需要的其他花费。

4.减少表的查询次数

尽量减少表的查询次数，主要是指能使用一次查询获得的数据尽量不要去通过两次或更多次的查询获得。

例如，有两个表：学生表student和教师表teacher，现在想知道某个学生（sname）的教师名称（tname），可以通过两种方式实现，一种是先从学生表中查询出这个学生所对应的教师编号（tid），然后从教师表中查询出这个教师编号所对应的教师名称，如下：

select tname from teacher
where tid=(select tid from student where sname='羊羊');

这个过程，先从student表中查出tid，再从teacher表中查出相对应的数据，共使用了两次查询。

另一种查询方式是使用表的连接方式，如下：

select tname from teacher t full join student s on t.tid=s.tid where s.sname='羊羊';

5.用exists替代in

select distinct tname from teacher t left join student s on t.tid=s.tid where t.tid in(select tid from student);

上例是判断某名教师是否教了学生时，采用的in操作符，这时oracle会遍历查询学生表中所有学生的TID，判断该教师的编号是否在这个TID列表中。而如果采用exist操作符，示例如下：

select distinct tname from teacher t left join student s on t.tid=s.tid where exists (select tid from teacher where t.tid=s.tid);

1.in

确定给定的值是否与子查询或列表中的值相匹配。使用in时，子查询先产生结果集，然后主查询再去结果集中寻找符合要求的字段列表，符合要求的输出，反之则不输出。

2.exists

指定一个子查询，检测行的存在。它不返回列表的值，只返回一个true或false。其运行方式是先运行主查询一次，再去子查询中查询与其对应的结果，如果子查询返回true则输出，反之则不输出。再根据主查询中的每一行去子查询中查询。

提示：由于in操作符需要进行确切的比较，而exists只需要验证存不存在，所以使用in将会比使用exists花费更多的查询成本，因此能使用exists代替in的地方，应该尽量使用exists。另外，尽量使用not exists替代not in。

6.用where替代having

统计student表中除“java 1班”以外的每个班级的学生人数，如下：

select sclass,count(*) from student where sclass !='java 1班' group by sclass;

上面按sclass列对行进行分组，并使用where子句对行进行过滤，不统计"java 1班"的学生人数。而使用having子句同样可以实现这种过滤，如下：

select sclass,count(*) from student group by sclass having sclass !='java 1班';

虽然where子句与having子句都可以用来过滤数据行，但having子句只会在检索出所有记录后才对结果集进行过滤，这种处理需要排序、总计等操作，而使用where子句就会减少这方面的开销。因此，那些简单的过滤条件应该让where实现，而尽量让having子句对统计函数的结果进行过滤。

7.使用“<=”替代“<”

如果想查询不及格的学生，where条件中可以写出score<60或者score<=59。这两条语句的区别就在于如果使用“<60”，则oracle会首先定位到60，然后再去寻找比60小的数，而如果使用“<=59”，则oracle会直接定位到第一个比60小的数，也就是59。虽然这种优化显得差别不大，但是在查询量大较大，尤其是在循环语句中使用这两个比较操作符时，区别还是很明显的。

表的连接

1.选择from表的顺序

所谓from表的顺序，是指select语句的from子句中的表与表之间的先后顺序。例如查询某名学生的教师名称，需要连接查询student和teacher表，语句如下：

select tname from student s,teacher t where s.tid=t.tid and s.sid=1;

上述示例中，将teacher表放在student表之前。如果单从查询结果来说，哪个表放在前面都一样，但是如果从查询效率这个角度来考虑，表之间的顺序就不能是随意的。

一般来说，oracle的解析器在处理from子句中的表时，是按照从右到左的顺序，也就是说from子句中最后指定的表将被oracle首先处理，oracle将它作为驱动表（Driving Table），并对该表的顺序进行排序，之后再扫描倒数第二个表，最后将所有从第二个表中检查出来的记录与第一个表中的合适记录进行合并。

因此，建议在使用表的连接查询时，选择记录条数最少的表作为驱动表，也就是将它作为from子句中的最后一个表。

2.where子句的连接顺序

在查询语句中，经常需要使用where子句为查询添加条件，对结果进行筛选，很多时候条件不仅仅只有一个，甚至还需要在where子句中使用子查询。例如，查询student表中学生成绩大于60，且教师姓名不是“周建明”的学生的信息。如下：

select * from student s where s.sscore>60 and '周建明' !=(select t.tname from teacher t where t.tid=s.tid);

当where子句中所指的条件不止一个时，应该将表之间的连接子查询放在其他条件的前面。 因为oracle解析where子句的顺序是从下至上，所以应该将那些可以过滤掉最大数量记录的条件放在where子句的末尾。因此上述的SQL语句需要进行调整，修改后的语句如下：

select * from student s where '周建明' !=(select t.tname from teacher t where t.tid=s.tid) and s.sscore>60;

3.使用表的别名

上述示例中的tid列，student表与teacher表中都有它，所以必须指定该列所属的表名，否则oracle会出现解析错误。虽然有些列只属于一个表，例如sid列只存在于student表中，但是如果在SQL语句中不指明它所属的表，那么这部分工作将会由oracle自己去完成，这显然会增加oracle的负担。所以能够使用表的别名时就尽量使用。

有效使用索引

1.使用索引的基本事项

通过索引查询要比全表扫描快得多，当oracle找出执行查询的最佳路径时，oracle优化器就会使用索引。但是，在认识到索引的优点的同时，也要注意使用索引所需要付出的代价。索引需要占据存储空间，需要定期进行维护，每当表中有记录增减或索引列被修改时，索引本身也会被修改，这就意味着每条记录的insert、delete、update操作都要使用更多的磁盘I/O。而且很多已经不必要的索引，甚至会影响查询效率。

所以在使用索引时，应该注意什么情况下使用它，一般创建索引有如下几个基本原则：

1.对于经常以查询关键字为基础的表，并且该表中的数据行是均匀分布的。

2.以查询关键字为基础，表中的数据行随机排序。

3.表中包含的列数相对比较少。

4.表中的大多数查询都包含相对简单的where子句。

除了需要知道什么情况下使用索引以外，还需要知道在创建索引时选择表中的哪些列作为索引列。一般选择索引列有如下几个原则：

1.经常在where子句中使用的列。

2.经常在表连接查询中用于表之间连接的列。

3.不宜将经常修改的列作为索引列。

4.不宜将经常在where子句中使用，但与函数或操作符相结合的列作为索引列。

5.对于取值较少的列，应考虑建立位图索引，而不应该采用B树索引。

提示：除了所查询的表没有索引，或者需要返回表中的所有行时，oracle会进行全表扫描以外，如果查询语句中带like关键字，并使用了通配符“%”，或者对索引列使用了函数，oracle同样会对全表扫描。

2.避免对索引列使用NOT关键字

在索引列上使用not关键字，与在索引列上使用函数一样，都会导致oracle进行全表扫描。实际上索引的作用是快速地告诉用户在表中有什么数据，而不能用来告诉用户在表中没有什么数据。所以，类似的“!=”等操作符也应该避免在索引列上使用。

3.避免对唯一索引列使用is(not) null

使用unique关键字可以为列添加唯一性索引，也就是说列的值不允许有重复值，但是，多个null值却可以同时存在，同时oracle认为两个空值是不相等的。

所以，在向表中的唯一索引列添加数据时，可以添加无数条null记录，但是由于这些记录都是空值，所以在索引中并不存在这些记录。因此，当在where子句中使用is null或is not null对唯一索引列进行空值比较时，oracle将会停止使用该列上的唯一索引，这就会导致oracle进行全表扫描。

4.选择复合索引主列

索引不仅仅可以基于单独的列，还可以基于多个列，在多个列上创建的索引叫复合索引。例如为student表中的sname列与sscore列建立复合索引，如下：

create index name_score_index on student(sname,sscore);

在创建复合索引时，不可避免的要面对多个列的前后顺序，这个顺序并不是随意的，它会影响索引的使用效率。一般在创建复合索引时，应该注意如下几个原则：

1.选择经常在where子句中使用且由and操作符连接的列作为复合索引列。

2.选择where子句中使用频率相对较高的列作为复合索引的主列。

例如，如果student表中的sscore列在where子句中的使用频率比sname高，则创建索引时应该将sscore列放在sname前面。

注意：只有当复合索引的第一列，也就是主键列，被where子句使用时，oracle才会使用该复合索引。例如，如果在where子句中只使用了sscore列，则oracle不会使用上面创建的复合索引name_score_index。

另外，在使用复合索引时，where子句中列的顺序应该与复合索引中索引列的顺序保持一致，例如有如下两条select语句：

select * from student where sname='羊羊' and sscore=94;

select * from student where sscore=94 and sname='羊羊';

合理的where子句应该与上述第一条select语句中的where子句相似，也就是保持where子句中列的顺序与复合索引中列的顺序一致。

5.监视索引是否被使用

因为不必要的索引会对表的查询效率起副作用，所以在实际应用中应该经常检查索引是否被使用，这需要用到索引的监视功能。

对name_score_index索引创建监视：

alter index name_score_index monitoring usage;

上述语句添加了对name_score_index索引的监视，然后就可以通过V$OBJECT_USAGE视图来了解该索引的使用状态，如下：

select table_name,index_name,monitoring from v$object_usage;

上面table_name字段描述索引所在表，index_name字段描述索引名称，monitoring字段表示索引是否处于激活状态。