一、SQL优化的一般性原则
① 目标：减少服务器资源消耗（主要是磁盘I/O）
② 设计：尽量依赖Oracle优化器；创建合适的索引
③ 编码：利用索引；合理利用临时表；避免写过于复杂的sql； 尽量减小事务的粒度
二、注意事项
① 查询时尽量使用确定的列名；
② 少使用嵌套的子查询，这种查询很消耗cpu资源；
③ 多表查询的时候，选择最有效率的表名顺序（oracle解析器对表的处理顺序从右到左，所以记录少的表放在右边（最右边的表为基础表，drivering table最先被处理）， 如果3个以上的表连接查询，则要选择交叉表作为基础表）；
④ or 比较多时分为多个查询，使用union all（尽量用union all代替union）联结；
⑤ 尽量多用commit提交事务，可以及时释放资源、解锁、释放日志；
⑥ 访问频繁的表可以放置在内存中；
⑦ 避免复杂的多表关联；
⑧ 避免distinct,union（并集）,minus（差集）,intersect（交集）,order by等耗费资源的操作，因为会执行耗费资源的排序功能；
⑨ 使用exists替代distinct；
⑩ 删除全表时利用truncate代替delete（delete删除时，没有commit前可以回滚；truncate后不能回滚，执行时间较短）；
⑪ 使用表的别名，可以减少解析时间；
⑫ exists和in的选择问题，不同时候区分对待；
⑬ 合理使用索引。
三、SQL语句的执行顺序
⑧ SELECT ⑨DISTINCT (11)
① FROM ③ JOIN ② ON
④ WHERE ⑤ GROUP BY ⑥ WITH {CUBE | ROLLUP} ⑦ HAVING
⑩ ORDER BY
描述：
① FROM：对FROM子句中的前两个表执行笛卡尔积(交叉联接)，生成虚拟表VT1。
② ON：对VT1应用ON筛选器，只有那些使为真才被插入到TV2。
③ OUTER (JOIN):如果指定了OUTER JOIN(相对于CROSS JOIN或INNER JOIN)，保留表中未找到匹配的行将作为外部行添加到VT2，生成TV3。如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3，直到处理完所有的表为止。（是不是说除OUTER JOIN，其他JOIN都是跟相当于和ON同时执行，不会先计算笛卡尔积，或者是说先是JOIN，然后ON）
④ WHERE：对TV3应用WHERE筛选器，只有使为true的行才插入TV4。
⑤ GROUP BY：按GROUP BY子句中的列列表对TV4中的行进行分组，生成TV5。
⑥ CUTE | ROLLUP：把超组插入VT5，生成VT6。
⑦ HAVING：对VT6应用HAVING筛选器，只有使为true的组插入到VT7。
⑧ SELECT：处理SELECT列表，产生VT8。
⑨ DISTINCT：将重复的行从VT8中删除，产品VT9。
⑩ ORDER BY：将VT9中的行按ORDER BY子句中的列列表顺序，生成一个游标(VC10)，生成表TV11，并返回给调用者。
四、SQL语句
1、union 和 union all
① union 多用于合并两个或者多个 select 的结果集。
② union 会去掉重复的结果，union all 会保存 select 查出来的所有结果。
③ union 内部的 select 结果集必须拥有相同数量的列，列的顺序必须相同；列也必须是相同的数据类型，像 '111' 和 111 这样的结果集也不能合并，会报 “ORA-00900：无效SQL语句”。
④ union 结果集的列明等于第一个 select 结果集的列名。
2、where 和 having
① where 不能放在 group by 后面。
② where 后面的条件不能有聚合函数。比如 sum()。
③ having 是跟 group by 放在一起使用的，放在 group by 的后面，作用相当于 where 。
④ where 和 having 都是对查询结果的一种筛选。
⑤ where 字句用于从 from 返回的值。where 子句中可以使用比较运算符(>、>=、<、<=、=、<>、!=)和逻辑运算符(and、or、not)。from 返回的每一行数据都会用 where 字句中的条件进行筛选。
⑥ having 对 group by 的结果进行进一步的筛选。在查询过程中 聚合函数 比 having 语句优先执行。where 字句优先于聚合函数执行。having 可以对 group by 、where、from 的返回结果进行筛选。
3、exists 和 in
1 select * from student a where pro_id in (select pro_id from stu_pro) order by a.id; 2 select * from student a where exists (select 1 from stu_pro b where a.pro_id=b.pro_id) order by a.id; 3 select * from student a,stu_pro b where a.pro_id=b.pro_id order by a.id;
① exists 对外表做循环，每次循环对内表进行查询；in 将内表和外表进行hash连接。
② 使用 exists Oracle会先检查主查询；使用in，优先执行子查询，并将结果存到内存中。
③ 如上述代码，in使用的是A表的索引，exists使用的是B的索引，C同时使用A和B表的索引。
④ 尽量不要使用not in。
⑤ 结论：如果A和B的数据量不大，那么三条语句的执行效率相差不大；如果A表大，B表小，使用in；如果B表大，A表小，使用exists；无论如何，第三行语句单扫描次数是笛卡尔乘积，效率最差。
4、with as
语法
with tempName as (select ....) select ... --多个with table as 一起使用时用逗号隔开，并且只能使用一个with with tempName1 as (select ....), tempName2 as (select ....), tempName3 as (select ....) select ...
① 在真正进行查询之前预先构造了一个临时表TT，之后便可多次使用它做进一步的分析和处理；
② 对于复杂查询，使用with table as可以抽取公共查询部分，多次查询时可以提高效率；
③ 使用子查询的方法表被扫描两次，而使用WITH Clause方法，表仅被扫描一次。这样可以大大的提高数据分析和查询的效率。
④ 增加了代码的易读性。
5、delete 和 truncate
① 两者都可以用来删除表中所有的记录；
② truncate是DDL操作，它移动HWK，不需要 rollback segment ；
③ 而Delete是DML操作, 需要rollback segment 且花费较长时间；
④ 介于上面三点，全表删除时使用 truncate。
6、绑定变量(bind variable)
绑定变量是查询中的一个占位符：如&variable。
例如要获取 student 表中的 stu_id = ‘0001’ 的学生信息：
① select * from student where stu_id = ‘0001’;
② 先绑定变量&stu_id，&stu_id 的值设为 ‘0001’，再执行查询 select * from student where stu_id = &stu_id;
第一个查询直接使用 ‘0001’ 这个常量，如果有多个这样的查询，则每一个查询对数据库来说都是一个全新的查询。Oracle每次都会对查询进行解析，限定（命名解析）、安全性检查、优化等（简单来说，就是每次执行前都要先编译）。
第二个查询使用了&stu_id这样一个绑定变量，变量在执行时动态指定，这个查询只会在第一次时编译，随后oralce会把查询计划存储在一个共享池中方便以后重用，以后在传入&stu_id不同的值进行查询时，Oracle会使用第一次解析好的这个查询计划执行，查询效率大幅提升。
7、null 值、'' 空字符串、空格
① 空值等价于没有任何值，是未知数。Oracle中 null 和 '' （空字符串）是一个意思，不能使用 = '' 作为查询条件，只能使用is null，不为空为is not null。
② 对 null 值做加、减、乘、除等运算操作，结果仍为空。
③ 控制可以进行拼接字符串操作，结果正常。
④ 空值的处理使用 nvl 函数，给null值赋值后在进行使用。
⑤ 空值排序时比其他数据都大（索引默认是降序排列），所以null值总是排在最后。
⑥ 空格当做正常的字符串处理，不能插入到数值类型列中，排序时排在最前面。
insert into student (stu_id, stu_name, sex, credit) values ('0012', '一一', ' ', '79'); insert into student (stu_id, stu_name, sex, credit) values ('0013', '二二', '', '55'); insert into student (stu_id, stu_name, sex, credit) values ('0014', '三三', null, '76'); insert into student (stu_id, stu_name, sex, credit) values ('0015', '四四', '', ' ');--插入报错，数值类型不能插入空格 --空值和空字符串是一样的 select * from student; --查询到 3 条记录 select * from student where sex =''; --查询到 0 条记录 select * from student where sex is null; --查询到 2 条记录
--空值不能被索引 select count(sex) from student where stu_id ='0014';--查询到 0 条记录 --空值需要nvl处理后再查 select count(nvl(sex,0)) from student where stu_id ='0014';--查询 到 1 条记录 --空值排序时候排在最后、空格排在最前 select * from student order by sex; --使用降序将空值排到最前，此时，空格排在最后 select * from student order by sex desc; --使用nulls first （不管asc或者desc，null记录排在最前）或者nulls last 可以控制null的位置。不影响空格位置 select * from student order by sex nulls first;