SQL脚本编写注意事项【不定时补充】

-------分界线--------

To myself : 在写sql脚本老是会忘记一些脚本编写规范与技巧，导致脚本健壮性并不强，每每说要及时总结、吸取经验，可是工作一忙起来转眼就会忘，所以强制给自己定个时间来整理这方面的东东，算是立个flag吧@_@

1. IN 与 EXISTS 的区别

① IN 适用于子表数据量较小的子查询；EXISTS 适用于子表数据量较大的子查询；

eg: 主表A,n条记录；子表B,m条记录；

a. select * from A where A.id in (select B.id from B );

in()只执行一次，它先把子查询 (select B.id from B ) 的结果集找出来放在缓存，然后再检查判断主表A.id是否与B.id相等，如果相等则把记录添加进结果集，直到遍历完主表A；

所以，如果子表B的数据量很大，它会先遍历子表B，再遍历主表A，至多遍历n*m次， 不仅耗费资源，且效率差；

b. select * from A where A.id exists (select 1 from B.id = A.id);

与 a 不同的是，EXISTS()会执行n次(A有n条记录)，但子查询结果集并不重要，也不会保存在缓存，它只是验证子表B中是否有该记录，如果该记录存在返回true，没有则false。

所以，如果子表B数据量很大，远大于主表A，适合使用EXISTS()，因为它没有遍历操作,只需要再执行一次查询就行。

2. WHERE条件中使用LIKE ，可以走索引，前提是不使用 like '%..%' 格式；

--参考https://jeffkemponoracle.com/2008/01/17/like-with-wildcard-at-start-can-use-an-index/

① 对于 like '..%' (以 % 结尾)，可以走colunm上的index;

② 对于 like '..%..' (不以 % 开头，均以常量结尾)，Oracle可以应用 colunm上的index;

③ 对于 like '%...' 的 (不以 % 结尾，以常量结尾)，可以利用 reverse + function index 的形式，变化成 like '..%' 代码

eg：create index xxx on table(table.xx); ---> create index xxx on table(reverse(table.xx));

      reverse函数：可以实现将一个对象反向转换；

      eg:select reverse('abc') from dual;--> 'cba'

3. SELECT 字句中避免使用 '*'

    ORACLE在解析的过程中, 会将 '*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间

4. 避免在字段上使用函数

    在字段上使用函数，在查询时不会使用该字段的索引。

    如where to_char(sqsj,’yyyymmdd’)>=’20150101’，将不会使用索引。

5. 尽量避免使用 IS NULL 或IS NOT NULL操作

    IS NULL和IS NOT NULL不会使用索引。

    NULL值是关系数据库系统布尔型(true,false,unknown)中比较特殊类型的一种值，通常称为UNKNOWN或空值，即是未知的，不确定的。

  由于NULL存在无数可能，因此NUll值也不等于NULL值，所以与NULL值相关的操作同样都是NULL值。

  正是基于这样一个特性，对于值列上的B树索引导致了is null/is not null不走索引的情形，下面描述了NULL值与索引以及索引NULL列上的执

  行计划，如何使得NULL值走索引的情形。