自己对oracle sql的一些优化总结自己也记录下来也希望对大家有帮助
一使用where少使用having;
二查两张以上表时把记录少的放在右边;
三减少对表的访问次数;
四有where子查询时子查询放在最前;
五select语句中尽量避免使用*(执行时会把*依次转换为列名);
六尽量多的使用commit;
七Decode可以避免重复扫描相同的记录或重复连接相同的表;
八通过内部函数也可提高sql效率;
九连接多个表时使用别名并把别名前缀于每个字段上;
十用exists代替in
十一not exists代替 not in(not in 字句将执行一个内部的排序和合并任何情况下not in是最低效的子查询中全表扫描了为了避免使用not in可以改写成outer joins或not exists);
十二表连接比exists更高效;
十三用exists替换distinct
例
低 高
select distinct dept_no dept_name select dept_no dept_name
from dept d emp e from dept d
where ddept_no = edept_no; where exists (select from emp e where edept_no = ddept_no);
十四使用TKPROF工具来查询sql性能状态;
十五用索引提高效率(代价是索引需要空间而且定期重构索引很有必要ALTER INDEX REBUILD
先介绍下索引的原理方便接下来对索引的优化的理解
通过索引找到rowid然后通过rowid访问表但如果查询的列包括在index中将不在执行第二部操作因为检索数据保存在索引中单单访问索引就可以完全满足查询要求
前提提要在十六例中LODGING列有唯一索引;MANAGER列上有非唯一性索引
十六索引范围查询(INDEX RANGE SACEN):
适用于两种情况
)基于一个范围的查询
SELECT LODGING FROM LODGING WHERE LODGING LIKE M%
(where字句条件包括一系列的值oracle将通过索引范围查询方式查询LODGING_PK)
) 基于非唯一性索引的检索
SELECT LODGING FROM LODGING WHERE MANAGER = LI;
(此查询分两步LODGING$MANAGER的索引范围查询得到所有符合条件记录的rowid然后通过rowid访问表得到LODGING列的值该索引为非唯一性索引数据库不能对它执行索引唯一扫描)
where字句中如果索引列所对应的值的第一个字符由通配符开始索引将不被采用而会全表扫描如 SELECT WHERE MANAGER LIKE %LI
十七基础表的选择
基础表最先访问的表(通常以全表扫描的方式被访问)
根据优化器的不同SQL语句中基础表的选择是不一样的
如果使用CBO优化器会检查SQL语句中的每个表的物理大小索引的状态然后选用话费最低的路径
如果使用RBO并且所有的连接条件都有索引对应这种情况下基础表就是FROM字句中列在最后的表
例
SELECT ANAME BMANAGER FROM WOKER A LODGING B WHERE ALODGING = BLODGING;
由于LODGING列上有一个索引而且WORKER表中没有相比较的索引WORKER表将被作为查询基础表
十八多个平等的索引
当SQL语句的执行路径可以使用分布在多个表上的多个索引时oracle会同事使用多个索引并在运行时对它们的记录合并检索仅对全部索引有效的记录
oracle选择执行路径是唯一索引等级高于非唯一索引只有当where字句中索引列和常量比较才有效如果索引列和其它表的索引列相比较这种字句在优化器中等级非常低;
如果不同表中两个相同等级的索引将被引用根据FROM字句中表的顺序决定哪个先被使用FROM字句中最后的表索引优先级高如果相同表中两个相同等级的索引将被引用where字句中最先被引用的索引将有最高的优先级
例DEPTNO上有非唯一性索引EMP_CAT也有非唯一性索引
SELECT ENAME FROM EMP WHERE DEPT_NO = AND EMP_CAT = A;
DEPTNO索引将被先检索然后同EMP_CAT索引检索出的结果合并执行路径如下
TABLE ACCESS BY ROWID ON EMP
AND _EQUAL
INDEX RANGE SCAN ON DEPT_IDX
INDEX RANGE SCAN ON CAT_IDX
十九等式比较与范围比较
先上例子
SELECT ENAME FROM EMP WHERE DEPT_NO > AND EMP_CAT = A;
(在两个非唯一性索引前提下)此时范围索引不被使用通过EMP_CAT索引查询出记录再与DEPT_NO条件进行比较
注意唯一性所以做范围比较时等级要比非唯一性索引的等式比较低;
二十强制索引失效
如果两个或两个以上索引具有相同的等级可以强制命令oracle优化器使用其中的一个 那何时使用此种策略呢?如果一个索引已接近于唯一而另一索引有很多重复的值排序与合并反而会成为负担此时可以屏蔽后者使其索引失效
(失效方式对索引列加入计算+或||"");
