Oracle SQL优化

一、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

select * from student a where pro_id in (select pro_id from stu_pro) order by a.id;
select * from student a where exists (select 1 from stu_pro b where a.pro_id=b.pro_id) order by a.id;
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;