OracleSQL优化技巧：提升数据库性能的关键策略-优快云博客

常规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查询语句：

select empno,ename,job from emp
where deptno in (select deptno from dept where loc = 'BEIJING')
or deptno in (select deptno from dept where loc = 'NEW YORK');
--要对dept表执行两遍的查询

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

select empno,ename,job from emp
where deptno in (select deptno from dept where loc = 'BEIJING' or loc = 'NEW YORK')

　5、子查询建议使用 [NOT] EXISTS 代替 [NOT] IN

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

--低效SQL
SELECT empno,ename,job FROM EMP WHERE  DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = 'NANJING')
--高效SQL
SELECT empno,ename,job FROM EMP WHERE EXISTS (SELECT deptno FROM DEPT WHERE LOC != 'NANJING')

　　一分为二看[not] in，当[not] in后面跟子查询，并且查询的结果集较多时，不宜使用[not] in；如果[not] in后面的括号内时列表或子查询所满足结果集很少时，也是可以使用的。

　　6、用>=替代>

--低效:
   SELECT empno,ename,job FROM EMP WHERE DEPTNO > 3

--高效:
   SELECT empno,ename,job FROM EMP  WHERE DEPTNO >= 4

　　DEPTNO > 3时ORACLE会先找出为3的记录索引再进行比较，而DEPTNO >= 4时ORACLE则直接找到=4的记录索引

　　7、用in代替or

-- 低效
select.. from emp where empno = 10 or empno = 20 or empno = 30

-- 高效:
select.. from emp where empno in (10,20,30);

　　8、避免使用耗费资源的操作

　　带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序.

　　例如,一个UNION查询,其中每个查询都带有GROUP BY子句, GROUP BY会触发嵌入排序(NESTED SORT) ; 这样, 每个查询需要执行一次排序, 然后在执行UNION时, 又一个唯一排序(SORT UNIQUE)操作被执行而且它只能在前面的嵌入排序结束后才能开始执行. 嵌入的排序的深度会大大影响查询的效率.

　　9、order by

　　任何在Order by语句的非索引项或者有计算表达式都将降低查询速度

　　经常用于排序的字段应加上索引

　　10、避免在索引列上使用计算

　　WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．

--低效：
SELECT … FROM  EMP WHERE SAL * 12 > 25000;

--高效:
SELECT … FROM EMP WHERE SAL > 25000/12;

　　11、通过使用>=、<=等，避免使用NOT命令

select * from employee where salary <> 3000; --不能使用索引

对这个查询，可以改写为不使用NOT：

select * from employee where salary<3000 or salary>3000; --允许Oracle对salary列使用索引

　　12、字符型字段的引号

　　比如表中PHONE_NO字段是CHAR型,而且创建有索引，但在WHERE条件中忘记了加引号，就不会用到索引。

　　WHERE PHONE_NO=‘13920202022’

　　WHERE PHONE_NO=13920202022

　　13、避免在索引列上使用IS NULL和IS NOT NULL

　　14、模糊查询

　　下面模糊查询也将导致全表扫描：
　　select id from t where name like '%abc%' ;