oracle SQL开发注意事项

 刚给新项目做了个SQL注意事项，好久没整了，贴出来SQL语句优化注意事项

1 . in 操作符：
in 操作符优点是比较容易写及清晰易懂，但是性能要差些（ORACLE会将其转换成多个表的连接）
在业务密集的SQL当中禁止采用IN操作符。
2 .not in
不能应用表的索引, 性能很差，用NOT EXISTS 替代。

3 .<> 操作符
不能应该表索引，性能很差，可如下替换： a<>0 替换为a>0 or a<0

4. 对索引的字段不能使用函数或运算(将进行全表扫描)
如substr(araecode,1,3)='440’，优化处理：araecode like ‘440%’;
trunc(create_time)=trunc(sysdate)， 优化处理：
create_time>=trunc(sysdate) and create_time<trunc(sysdate+1)

5. 子查询
尽可能使用EXISTST   EXISTS查询
复杂的子查询：使用
with
(
) as T1

再select ... from T1 ...;

6. 重复功能的SQL
   Oracle会将SQL语句解析后存放到内存，同一功能的语句要完全一致，这样可以避免重复解析。 如selelct count(1) from dic_system 和 selelct count(*) from Dic_system

7. 不使用*
   Oracle需要将*转换为各个字段，要查询字典表，影响性能

8.计算记录条数
使用 count（索引列） ，是性能最快的。
-------------------------------------------
1.用EXISTS代替DISTINCT，消除sort operation

　　2.如果在GROUP BY中过滤数据，在WHERE从句中指定条件比在HAVING从句中有更好的性能，因为在GROUP之前已经过滤掉数据，因此更少的行被汇总

　　3.UNION会对两个SELECT语句的结果集执行一个SORT，并消除重复行，成本会昂贵，而UNION ALL则不会。因此如果应用能够处理重复，或者确信没有重复记录，那么考虑使用UNION ALL代替UNION

　　4.能不用UNION就不要用它

　　5.为了避免在一个SQL语句中混合使用LEFT JOIN和RIGHT JOIN产生混淆，应该使用一个一致的视野，例如外连接只使用FULL or LEFT OUTER JOIN，忽略掉RIGHT OUTER JOIN

　　例如：

　　Sql代码

 

SELECT e.lname, j.function, d.name

　　FROM job j LEFT OUTER JOIN employee e ON e.job_id = j.job_id

　　RIGHT OUTER JOIN department d ON e.dept_id = d.dept_id;

　　SELECT e.lname, j.function, d.name

　　FROM job j LEFT OUTER JOIN employee e ON e.job_id = j.job_id

　　RIGHT OUTER JOIN department d ON e.dept_id = d.dept_id;

 

　　
　　应该转换为：

　　Sql代码        

 

SELECT e.lname, j.function, d.name

　　FROM department d LEFT OUTER JOIN

　　(job j LEFT OUTER JOIN employee e

　　ON e.job_id = j.job_id)

　　ON e.dept_id = d.dept_id;

　　SELECT e.lname, j.function, d.name

　　FROM department d LEFT OUTER JOIN

　　(job j LEFT OUTER JOIN employee e

　　ON e.job_id = j.job_id)

　　ON e.dept_id = d.dept_id;

 

   　6.SQL被Oracle执行之前需要被解析。无论一个给定的SQL语句被执行多少次，它仅仅需要一次解析。在解析期间，下面的步骤被执行(不考虑顺序)：

　　(1)SQL语句语法被验证

　　(2)数据字典被搜索用以验证表和列的定义

　　(3)数据字典被搜索用以验证在相关对象上的安全权限

　　(4)相关对象上会获得解析锁

　　(5)决定最佳的执行计划

　　(6)语句被加载到SGA系统全局区中的共享池内的共享SQL区(也叫library cache库高速缓存区)。这个执行计划和解析信息被保存在这里，以防止相同的语句被再次执行

　　一条SQL语句被解析的条件是，仅仅如果Oracle不能在SGA的共享SQL区中找到同样的SQL语句。

　　在解析一条SQL语句之前，Oracle在库高速缓存中搜索相同的SQL语句。如果找到了确切的匹配，则不再需要解析这条语句。然而如果相同的SQL语句没有被找到,那么Oracle会执行上面所述的步骤去解析这条语句。

　　为了成为相同的SQL语句，必须满足下述条件：

　　(1)有相同的大小写字符

　　(2)有相同的空格和换行

　　(3)使用相同的名字引用相同的对象，必须有相同的owner所有者

　　如果应用可能多次执行相同的或相似的SQL语句，尽一切办法尝试避免不必要的解析。这样将会提高应用的所有性能。减少SQL解析的两种技术：

　　(1)使用绑定变量

　　(2)使用表别名

　　6.1使用绑定变量

　　当多用户使用一个应用的时候，会经常反复的执行相同的SQL语句集合，但是会使用不同的数值。例如，一个客户代表经常执行下面语句：

　　Sql代码

　　SELECT * FROM customer WHERE cust_nbr = 121;

　　SELECT * FROM customer WHERE cust_nbr = 121;

　　而另一个客户代表将会执行：

　　Sql代码

　　SELECT * FROM customer WHERE cust_nbr = 328;

　　SELECT * FROM customer WHERE cust_nbr = 328;

　　这两条SQL语句相似，但是不相同，因为cust_nbr的号不同，因此Oracle必须解析两次。可以使用绑定变量重写应用。这样的话，有问题的SQL语句可以改写为：

　　Sql代码

　　SELECT * FROM customer WHERE cust_nbr = :x;

　　SELECT * FROM customer WHERE cust_nbr = :x;

　　Oracle仅仅需要解析一次语句了。多用户并发执行的程序在同时提供不同的cust_nbr的时候，能够共享这条SQL语句相同的拷贝，减少不必要的解析。

　　6.2使用表别名

　　表别名的使用能够帮助提升SQL语句的性能，提供了方便的简化符号，是查询更可读，简明，表别名的长度最大能到30个字符

　　容易犯的错误是在写hint的时候忘记使用表别名，这样的话这些hint将被静态忽略掉

　　一旦定义了别名，在查询语句的任何地方以及任何的hint都必须指定别名，而不是真实的表名。

　　这里阐述使用表别名如何影响性能，如下查询：

　　Sql代码

　　SELECT c.cust_nbr, name, order_nbr

　　FROM customer c, cust_order o

　　WHERE c.cust_nbr = o.cust_nbr;

　　SELECT c.cust_nbr, name, order_nbr

　　FROM customer c, cust_order o

　　WHERE c.cust_nbr = o.cust_nbr;

　　因为name列只在customer表中有，order_nbr列只在cust_order表中存在，所以可以不用别名限定列，这句sql是有效的。但是如果查询没有限定列，那么当解析这个语句找到这些列属于哪个表，Oracle必须搜索customer和cust_order这两个表。对于一个查询来说，这个搜索所需要的时间可能是可以忽略不计的，但是如果你有许多这样的查询要解析的话，那么加起来会很耗资源了。在一个查询中，使用表别名限定所有的列(甚至那些非不明确的列)是好的编程实践，这样的好处是，解析语句的时候Oracle能够避免这种额外的搜索。