Oracle中编写Sql语句注意事项-蒲公英云

基本的Sql编写注意事项（SQL优化法则）

1.尽量少用IN操作符，基本上所有的IN操作符都可以用EXISTS代替。
原因：这篇文章讲解的生动形象。http://www.cnblogs.com/iceword/archive/2011/02/15/1955337.html
2.不用NOT IN操作符，可以用NOT EXISTS或者外连接+替代。
原因：因为NULL值并没有被定义。在SQL语句中使用NULL会有很多的麻烦。
因此建议开发人员在建表时，把需要索引的列设成NOT NULL。      如果被索引的列在某些行中存在NULL值，就不会使用这个索引（除非索引是一个位图索引，详情请查阅oracle中的位图索引资料）。
3.不用“<>”或者“!=”操作符。对不等于操作符的处理会造成全表扫描，可以用“<” or “>”代替。
原因：下面的查询即使在cust_rating列有一个索引，查询语句仍然执行一次全表扫描。
select cust_Id,cust_name
from customers
where cust_rating <> 'aa';
把上面的语句改成如下的查询语句，这样，在采用基于规则的优化器而不是基于代价的优化器（更智能）时，将会使用索引。
select cust_Id,cust_name
from customers
where cust_rating < 'aa' or cust_rating > 'aa';
特别注意：通过把不等于操作符改成OR条件，就可以使用索引，以避免全表扫描
4.应该使用索引的第一个列 
如果索引是建立在多个列上 , 只有在它的第一个列 (leading column) 被 where 子句引用时 , 优化器才会选择使用该索引 .
这也是一条简单而重要的规则，当仅引用索引的第二个列时 , 优化器使用了全表扫描而忽略了索引
5.Where子句中出现IS NULL或者IS NOT NULL时，Oracle会停止使用索引而执行全表扫描。
办法：可以考虑在设计表时，对索引列设置为NOT NULL。这样就可以用其他操作来取代判断NULL的操作。
------------------------------------------------------------------------------------
查询表中存在的索引
select * from user_indexes where table_name = 'table_name';
6.当通配符“%”或者“_”作为查询字符串的第一个字符时，索引不会被使用。
7.对于有连接的列“||”，最后一个连接列索引会无效。尽量避免连接，
可以分开连接或者使用不作用在列上的函数替代。 
8.如果索引不是基于函数的，那么当在Where子句中对索引列使用函数时，索引不再起作用。
select empno,ename,deptno
from emp
where trunc(hiredate)='01-MAY-81';
把上面的语句改成下面的语句，这样就可以通过索引进行查找。
select empno,ename,deptno
from emp
where hiredate<(to_date('01-MAY-81')+0.9999); 
9.Where子句中避免在索引列上使用计算，否则将导致索引失效而进行全表扫描。 
10.对数据类型不同的列进行比较时，会使索引失效。
比较不匹配的数据类型也是比较难于发现的性能问题之一。
注意下面查询的例子，account_number是一个VARCHAR2类型，在account_number字段上有索引。下面的语句将执行全表扫描。
select bank_name,address,city,state,zip
from banks
where account_number = 990354;
Oracle可以自动把where子句变成to_number(account_number)=990354，这样就限制了索引的使用,改成下面的查询就可以使用索引：
select bank_name,address,city,state,zip
from banks
where account_number ='990354';
特别注意：不匹配的数据类型之间比较会让Oracle自动限制索引的使用，即便对这个查询执行Explain Plan也不能让您明白为什么做了一次“全表扫描”。
11.用“>=”替代“>”。 
12.UNION操作符会对结果进行筛选，消除重复，数据量大的情况下可能会引起磁盘排序。
如果不需要删除重复记录，应该使用UNION ALL。 
13.Oracle从下到上处理Where子句中多个查询条件，所以表连接语句应写在其他Where条件前，
可以过滤掉最大数量记录的条件必须写在Where子句的末尾。 
14.Oracle从右到左处理From子句中的表名，所以在From子句中包含多个表的情况下，
将记录最少的表放在最后。
15.Order By语句中的非索引列会降低性能，可以通过添加索引的方式处理。
严格控制在Order By语句中使用表达式。 
16.不同区域出现的相同的Sql语句，要保证查询字符完全相同，以利用SGA共享池，
防止相同的Sql语句被多次分析。 
17.当在Sql语句中连接多个表时，使用表的别名，并将之作为每列的前缀。这样可以减少解析时间。
18.SELECT 子句中避免使用 ‘ * ‘ ：
ORACLE 在解析的过程中 , 会将 '*' 依次转换成所有的列名 , 这个工作是通过查询数据字典完成的 , 这意味着将耗费更多的时间
19.sql 语句用大写的 ；因为 oracle 总是先解析 sql 语句，把小写的字母转换成大写的再执行    
20.在 java 代码中尽量少用连接符“＋”连接字符串！
21.优化 GROUP BY:
提高 GROUP BY 语句的效率 , 可以通过将不需要的记录在 GROUP BY 之前过滤掉 . 下面两个查询返回相同结果但第二个明显就快了许多 .
低效 : 
SELECT JOB , AVG(SAL) 
FROM EMP 
GROUP JOB 
HAVING JOB = ‘PRESIDENT' 
OR JOB = ‘MANAGER' 
高效 : 
SELECT JOB , AVG(SAL) 
FROM EMP 
WHERE JOB = ‘PRESIDENT' 
OR JOB = ‘MANAGER' 
GROUP JOB
22. 用 TRUNCATE 替代 DELETE ：
当删除表中的记录时 , 在通常情况下 , 回滚段 (rollback segments ) 用来存放可以被恢复的信息 . 如果你没有 COMMIT 事务 ,
ORACLE 会将数据恢复到删除之前的状态 ( 准确地说是 恢复到执行删除命令之前的状况 )而当运用 TRUNCATE 时 , 
回滚段不再存放任何可被恢复的信息 . 
当命令运行后 , 数据不能被恢复 . 因此很少的资源被调用 , 执行时间也会很短 .( 译者按 : TRUNCATE 只在删除全表适用 ,TRUNCATE 是 DDL 不是 DML)    
23.尽量多使用 COMMIT ：
只要有可能 , 在程序中尽量多使用 COMMIT, 这样程序的性能得到提高 , 需求也会因为 COMMIT 所释放的资源而减少 : 
COMMIT 所释放的资源 : 
a. 回滚段上用于恢复数据的信息 . 
b. 被程序语句获得的锁 
c. redo log buffer 中的空间 
d. ORACLE 为管理上述 3 种资源中的内部花费    
......待续