高效率优化Oracle SQL
2023-04-19 16:10:51
许多学生似乎对SQL的优化知之甚少。最近，他们总结了几篇文章仅供参考，但除了少数可能取决于情况外，其中大多数都相当有效。
[标注：下面说的(低效)和(高效)都是相当的。]
1、Where子句中的连接顺序：
ORACLE采用自下而上的顺序分析WHERE子句。
根据这一原理，表之间的连接必须写在其他WHERE条件之前， 能够过滤掉最大数量记录的条件必须写在WHERE句子的末尾。
举例：
(低效)
select … from table1 t1 where t1.sal > 300 and t1.jobtype = ’0001′ and 20 < (select count(*) from table1 t2 where t2.pno = t1.tno;
(高效)
select … from table1 t1 where 20 < (select count(*) from table1 t2 where t2.pno = t1.tno and t1.sal > 300 and t1.jobtype = ’0001′;
2、避免使用Select句子 “ * ”：
当您想在select句子中列出所有column时，使用动态SQL列引用 ‘*’ 这是一种方便的方法。
不幸的是，这是一种非常低效的方法。
事实上，ORACLE在分析过程中会出现 ‘*’ 依次转换为所有列名， 通过查询数据字典完成这项工作， 这意味着它将花费更多的时间。
3、减少访问数据库的次数：
ORACLE在执行每个SQL语句时，在内部执行了许多工作：
对SQL语句进行分析，估算索引的利用率，绑定变量，读取数据块等。
因此，减少访问数据库的次数，实际上可以减少ORACLE的工作量。
举例：
主题-我想找到001号码、0002学生信息。
（低效）
select name,age,gender,address from t_student where id = ’0001′;
select name,age,gender,address from t_student where id = ’0002′;
（高效）
select a.name,a.age,a.gender,a.address,b.name,b.age,b.gender,b.address from t_student a,t_student b where a.id = ’0001′ and b.id = ’0002′;
4、使用Decode函数来减少处理时间：
使用DECODE函数可避免重复扫描相同的记录或重复连接相同的表。
举例：
（低效）
select count(*), sum(banace) from table1 where dept_id = ’0001′ and name like ‘anger%’;
select count(*), sum(banace) from table1 where dept_id = ’0002′ and name like ‘anger%’;
（高效）
select count(decode(dept_id,’0001′,’XYZ’,null)) count_01,count(decode(dept_id,’0002′,’XYZ’,null)) count_02,
sum(decode(dept_id,’0001′,dept_id,null)) sum_01,sum(decode(dept_id,’0002′,dept_id,null)) sum_02
from table1
where name like ‘anger%’;
5、集成简单，无关数据库访问：
如果您有几个简单的数据库查询语句，您可以将其整合到查询中（即使它们之间没有关系）
举例：
（低效）
select name from table1 where id = ’0001′;
select name from table2 where id = ’0001′;
select name from table3 where id = ’0001′;
（高效）
select t1.name, t2.name, t3.name
from table1 t1, table2 t2, table3 t3
where t1.id(+) = ’0001′ and t2.id(+) = ’0001′ and t3.id(+) = ’0001′
【注:上面的例子虽然高效，但可读性差，需要量情而定！】
6、删除重复记录：
删除重复记录最有效的方法 ( 使用ROWID是因为使用ROWID)
举例：
delete from table1 t1
where t1.rowid > (select min(t2.rowid) from table1 t2 where t1.id = t2.id);
7、尽量不要使用having子句，可以考虑用where替换。
检索出所有记录后，having只会过滤结果集. 这种处理需要排序、总计等操作。
如果记录的数量可以通过where句限制，这方面的费用可以降低。
8、尽量用表格的别名：
在SQL语句中连接多个表时，请使用表的别名，并在每个Column上前缀别名。
这样可以减少Column歧义造成的分析时间和语法错误。
9、用exists代替in(发现很多程序员不知道怎么用)：
在很多基于基础表的查询中，为了满足一个条件，往往需要连接另一个表。
在这种情况下，使用exists(或nots) exists)通常会提高查询效率。
举例：
（低效）
select … from table1 t1 where t1.id > 10 and pno in (select no from table2 where name like ‘www%’);
（高效）
select … from table1 t1 where t1.id > 10 and exists (select 1 from table2 t2 where t1.pno = t2.no and name like ‘www%’);
10、用not 替代notts的exists in：
在子查询中，not in子句将进行内部排序和合并。
无论在什么情况下，not in效率最低 (因为它在对子查询中执行了一个全表遍历)。
为避免使用not in，我们可以把它改写成外部连接(Outer Joins)或not exists。
11、distinctttt取代exists替代distists：
提交包含一对多表信息的查询时，避免在select句子中使用distinctt. 一般可以考虑用exists替换
举例：
（低效）
select distinct d.dept_no, d.dept_name from t_dept d, t_emp e where d.dept_no = e.dept_no;
（高效）
select d.dept_no, d.dept_name from t_dept d where exists (select 1 from t_emp where d.dept_no = e.dept_no);
exists使查询更快，因为一旦满足子查询条件，RDBMS核心模块将立即返回结果.
12、用表连接替换existsts：
一般来说，表连接比exists更有效。
举例：
（低效）
select ename from emp e where exists (select 1 from dept where dept_no = e.dept_no and dept_cat = ‘W’);
SELECT ENAME
（高效）
select ename from dept d, emp e where e.dept_no = d.dept_no and dept_cat = ‘W’;
13、避免在索引列上使用is null和is not null
ORACLE将无法使用该索引，以避免在索引中使用任何可以空的列。
对于单列索引，如果列中包含空值，则该记录将不存在于索引中；
对于复合索引，如果每列都是空的，则该记录在索引中不存在；
如果至少有一个列不是空的，则在索引中存在记录。
举例:
如果表中的A列和B列中建立了唯一性索引， 而且表中有一个记录的A，B值为(123，null)，
ORACLE将不接受下一个A，B值相同(123，null）记录(插入)，
然而，如果所有索引都是空的，ORACLE将认为整个键值是空的，而不是空的。
所以你可以插入10000 条有相同键值的记录，当然都是空的！
由于空值不存在于索引列中，WHERE句中对索引列的空值比较将使ORACLE停止索引。
14、最好看看复杂的sql执行计划，这样可以帮助你分析和了解你的sql效率。
以上资料也是我长期积累的，很多都体现在项目中，尤其是大数据量上。
Sql语句优化Oracle：http://www.linuxidc.com/Linux/2014-08/106005.htm
