sql 调优
oracle 优化的规则有RBO(基于损耗) CBO( 基于规则)
1.用什么工具监控oracle的性能
2.oracel的执行计划
3.注意where语句的连接顺序 对于大数据量的语句放置在末尾 尤其是“主键ID=?”(现在oracle好像没有严格要求)
4.避免使用select * from 这样的语句 会对磁盘IO造成大的压力(会先扫描一个oracle视图所以比较慢)
5.避免在索引上使用NOT 遇到not就会停止使用索引
6.避免在索引上进行计算 select * from dept where sal*12>20000
7.避免在索引上使用 is null 和is not null
select * from department where dept_code is not null;(低效,索引失效)
select * from department where dept_code>=0;(高效)
8.加了通配符%可能会停用该索引
9.exists代替distinct
10.能够避免子查询额尽量不用子查询,因为子查询的开销是相当昂贵的
11.最后的条件下才是优化sql
12.order by 语句放在主键上的效率更高;
13 select * from demp where detno>2 (低效) select * from demp where depno>=3 (高效)
14 select * from demp where detno =2 or depno=3(低效) select * from demp where detno in(2,3)(高效)
15 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表
16 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾
17 ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间
18 使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.
19.查看低效的sql
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC;
20 用EXISTS替换DISTINCT:
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换, EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果. 例子:
(低效):
SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E
WHERE D.DEPT_NO = E.DEPT_NO
(高效):
21 用>=替代>
高效:
SELECT * FROM EMP WHERE DEPTNO >=4
低效:
SELECT * FROM EMP WHERE DEPTNO >3
22 避免在索引列上使用计算.
WHERE子句中,如果索引列是函数的一部分.优化器将不使用索引而使用全表扫描.
举例:
低效:
SELECT … FROM DEPT WHERE SAL * 12 > 25000;
高效:
SELECT … FROM DEPT WHERE SAL > 25000/12;
23 优化GROUP BY:
提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下面两个查询返回相同结果但第二个明显就快了许多.
低效:
SELECT JOB , AVG(SAL)
FROM EMP
GROUP JOB
HAVING JOB = ‘PRESIDENT'
OR JOB = ‘MANAGER'
高效:
SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB = ‘PRESIDENT'
OR JOB = ‘MANAGER'
GROUP JOB
24.删除表是先清空表,然后在drop表;
26.使用decode减少处理时间
27.快速删除重复数据的方法
SELECT * FROM Test_Appno e WHERE e.rowid>(SELECT MIN(x.rowid) FROM
Test_Appno x WHERE x.appno=e.appno
)
28.尽量多用commit;
29.sql语句尽量使用大写的,oracle会把大写字母转换成小写字母
31 为了减少I/O的竞争,把不同表空间的数据文件放在不同的硬盘上(待验证)
32. 索引空间同数据表空间分离
33.建议每一个表空间对应的数据文件的大小应小于2G;
34在ORACLE数据库中,数据的存储是以块(block)为基本单位,块的大小即数据库创建时确定的db_block_size的大小。多个块构成一个扩展区域(extent),多个扩展区域构成一个段(segment)
35.那么我想说的是,SQL慢,70%,80%是由于SQL写的不好
36.select * from a where a.no<>'2345' 可以改成 >'2345' or <'2345'