常规SQL语句优化

关于SQL优化

1.建议不用“*”来代替所有列名

SELECT语句中可以用“*”来列出某个表的所有列名，但是这样的写法对Oracle系统来说会存在解析的动态问题。Oracle系统会通过查询数据字典来将“*”转换成表的所有列名，这自然会消耗系统时间。建议用户在写SELECT语句时，采用与访问表有关的实际列名。

2.用TRUNCATE代替DELETE

当使用DELETE删除表中的数据行时，Oracle会使用撤销表空间（UNDO TABLESPACE）来存放恢复的信息。在这期间，如果用户没有发出COMMIT语句，而是发出ROLLBACK语句，Oracle系统会将数据恢复到删除之前的状态。当用户使用TRUNCATE语句对表的数据进行删除时，系统不会将被删除的数据写到回滚段（或撤销表空间）中，速度当然要快得多。所以当希望对表或者簇中的所有行全部删除时，采用TRUNCATE命令更加有效，其语法格式如下：

      Truncate [table | cluster] schema.[table_name] [cluster_name] [drop | reuse storage]

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

在PL/SQL块中，经常将几个相互联系的DML语句写在一个BEGIN…END块中，建议在每个块的END前面使用COMMIT语句，这样就可以实现对DML语句的及时提交，同时也释放事务所占用的资源。

COMMIT所释放的资源如下。

（1）回滚段上用于恢复数据的信息，撤销表空间也只做短暂的保留

（2）被程序语句获得的锁

（3）redo log buffer中的空间

（4）Oracle为管理上述资源的内部花费

4.尽量减少表的查询次数

 在含有子查询的SQL语句中，要特别注意减少对表的查询，如下面的两段代码。

      第一种：低效率的SQL查询语句：

SQL> select empno,ename,job from emp

   2  where deptno in (select deptno from dept where loc = 'BEIJING')

   3  or deptno in (select deptno from dept where loc = 'NEW YORK');

      第二种：对上面的代码进行适当修改，高效率的SQL查询语句如下：

SQL> select empno,ename,job from emp

    2  where deptno in (select deptno from dept where loc = 'BEIJING' or loc = 'NEW YORK');

      在“第一种”查询语句中，要对dept表执行两遍的查询，而在“第二种”查询中，仅对dept表执行一遍查询。在大量数据的情况下，显然“第二种”查询要比“第一种”查询快得多。