-
选择最有效率的表顺序
ORACLE 的解析器按照从右到左的顺序处理FROM 子句中的表名,FROM 子句中写在最后的表(基础表 driving table) 将被最先处理,在FROM 子句中包含多个表的情况下,必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询,就需要选择交叉表作为基础表,交叉表(intersection table)是指被其他表引用的表。
-
where 子句中的连接顺序:oracle 使用从右到左的解析顺序,表之间的连接需要放到WHRER 左边,可以过滤掉大量数据的条件需要放到更右边。
-
select子句中避免使用 " * " 当使用 " * " 号时,数据库会将它转换成所有列命,此过程是通过查询数据字典完成的,会耗费时间。
-
减少数据库访问次数,ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等,减少访问次数可以减少消耗时间;
-
增加数据库访问数量
-
使用DECODE 函数减少处理时间,DECODE 使用方式有两种:
①:比较单项 DECODE(比较项,比较值,返回值1,返回值2);该方式意思为 IF 比较项 = = 比较值 THEN RETURN 返回值1 ELSE RETURN 返回值2;
②比较多项 :DECODE(比较项,比较值1,返回值1,比较值2,返回值2,… 缺省值);该方式意思为:
if (比较项= =比较值1)then
return(返回值1)
elsif (比较项==比较值2)then
return(返回值2)
…
else return(缺省值)
end if -
整合简单无关联的数据库访问
-
删除重复记录
-
尽量多使用 commit
使用commit 时将释放资源,提高资源使用率和效率。 -
使用where 替换 having:避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销. (非oracle中)on、where、having这三个都可以加条件的子句中,on是最先执行,where次之,having最后,因为on是先把不符合条件的记录过滤后才进行统计,它就可以减少中间运算要处理的数据,按理说应该速度是最快的,where也应该比having快点的,因为它过滤数据后才进行sum,在两个表联接时才用on的,所以在一个表的时候,就剩下where跟having比较了。在这单表查询统计的情况下,如果要过滤的条件没有涉及到要计算字段,那它们的结果是一样的,只是where可以使用rushmore技术,而having就不能,在速度上后者要慢如果要涉及到计算的字段,就表示在没计算之前,这个字段的值是不确定的,根据上篇写的工作流程,where的作用时间是在计算之前就完成的,而having就是在计算后才起作用的,所以在这种情况下,两者的结果会不同。在多表联接查询时,on比where更早起作用。系统首先根据各个表之间的联接条件,把多个表合成一个临时表后,再由where进行过滤,然后再计算,计算完后再由having进行过滤。由此可见,要想过滤条件起到正确的作用,首先要明白这个条件应该在什么时候起作用,然后再决定放在哪里。
-
通过内部函数提高sql 效率
-
使用表别名
-
用 EXISTS 替换IN,用NOT EXISTS 替换 NOT IN
IN 关键字的参数如果是一般数值列表,性能还不需要太注意。但是是子查询则需要注意。
举例:(低效): SELECT * FROM CLASS_A WHERE ID IN (SELECT ID FROM CLASS_B) (高效):SELECT * FROM CLASS_A A WHERE EXISTS (SELECT FROM CLASS_B B WHERE A.ID = B.ID)
EXISTS 较快的两点原因:
a.如果连接列(ID)上建立了索引,那么查询CLASS_B 时不需要查询实际的表,只需要查询索引就可以了。
b.使用EXISTS 当查询到第一条记录后会终止查询,不像IN 遍历全表,但如果使用 IN 则要遍历全表。 -
用索引提高效率
-
用EXISTS 替换DISTINCT:因为EXISTS 查询到第一个满足条件的行就返回,所以可以满足过滤重复值的效果。
例:(低效): SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E WHERE D.DEPT_NO = E.DEPT_NO (高效): SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT 任意列 FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);
-
使用大写:ORACLE 碰到非小写时会将其转换为大写,耗费时间。
-
java 中少用连接符“+”连接字符串
-
避免在索引列上使用 NOT
-
避免在索引上使用 计算:如果索引上使用表达式或计算优化器将不会使用索引,而是使用全表扫描。
-
用>=替代>:例
SELECT * FROM EMP WHERE DEPTNO >=4 -- 高效 SELECT * FROM EMP WHERE DEPTNO >3 -- 低效
两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.
- 用UNION 替换 OR(适用于索引列):通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描,当有索引列时优先使用UNION ,没有索引列时可以暂不考虑。
- 避免在索引上使用 IS NULL 和 IS NOT NULL,使用not或null 比较优化器会跳过索引,使用遍历全表进行查询。
- 复合索引中总是使用索引的第一个列(创建时排在最前面的列),因为只有第一列被使用了,数据库优化器才会使用索引,不然将会使用遍历全表进行查询,这样会降低效率。
- 用where 替换 ORDER BY
- 避免排序:下列关键字和函数会默认排序
① GROUP BY
② ORDER BY
③ 聚合函数(SUM MIN MAX AVG COUNT)
④ DISTINCT
⑤ 聚合运算符(UNION INTERSECT EXCEPT(oracle 中为 MINUS)),替换成 UNION ALL 则不会排序,INTERSECT 和EXCEPT 同理。
⑥ 窗口函数 (RANK ROW_NUMBER等) - WHERE 中有索引时条件表达式的左侧应该使用原始字段。
- 多字段需要使用IN 时,可以将其汇总到一处
例:SELECT * FROM 表1 WHERE (字段1,字段2,字段3,...) IN (SELECT 字段1,字段2,字段3,... FROM 表2);
28.避免产生中间表