原文地址:http://blog.sina.com.cn/s/blog_7930c09d0100rak9.html
1.SQL语句用大写的;因为Oracle总是先解析SQL语句,把小写的字母转换成大写的再执行。
2.数据表最好起别名;因为便于sql优化器快速分析。
3.尽量不要使用 insert into table value(?,?,?,?,?)格式,要指出具体要赋值的字段。INSERT.....SELECT的效率会有提高。
4.Select与from语句之间只定义返回的字段名,除非返回所有的字段,尽量不要使用 * 。
5.Select字段名应按照表的字段物理顺序编写,字段提取要按照“需多少、提多少”的原则原因是大批量数据的抽取会影响sql缓存的效率。
6.COUNT(*)也是要避免的,因为Count(*)会对全字段做聚集。但一般的观点相反, count(*) 比count(1)稍快 , 当然如果可以通过索引检索,对索引列的计数仍旧是最快的. 例如 COUNT(EMPNO)。
7.条件中使用or 会引起全表扫描,比较影响查询效率,尽可能少用或不用,实在不行可以用UNION代替。
8.尽量避免子查询。假如子查询不可避免,那么要在子查询中过滤掉尽可能多的行。能用直连接代替的就替换掉。
9.在SQL语句中,LIKE关键字支持通配符匹配,但这种匹配非凡耗费时间。例如:cust_nm like '%$cust_nm$%'。即使在cust_nm字段上建立了索引,在这种情况下也还是采用顺序扫描的方式,查询表中有1000条记录,就需要比较1000次。
10.Distinct :使用distinct是为了保证在结果集中不出现重复值,但是distinct会产生一张工作表,并进行排序来删除重复记录,这会大大增加查询和I/O的操作次数。因此应当避免使用distinct关键字。
11.负逻辑
12.使用ORDER
13.join…on 后面慎用 or ,如果用到,请把or的范围用( )括起来。
14.SQL语句包含多表连接时,必须对每个表命名别名,对每个字段的使用都要带上别名。
15.Where 子句中的连接顺序:oracle采用自下而上的顺序解析where子句,根据这个原理,表之间的连接必须写在其他where条件之前,那些可以过滤掉大量记录的条件必须写在where子句的末尾。如:
低效:select * from emp e where sal>5000 and job = ‘manager’ and 25< (select count (*) from emp where mgr=e.empno);
高效:select * from emp e where 25<(select count(*) from emp where mgr=e.empno) and sal>5000 and job=’manager’;
16.尽量不使用右连接。参与左连接的列不能为常量。例如,不允许如下语句: select * from t1 left outer join t2 on t1.f1='A'。
左连接的写法必须带“outer”关键字。例如: select f1 from t1 left outer join t2 on t1.f1 = t2.f1;而不是: select f1 from t1 left join t2 on t1.f1 = t2.f1。
17.在使用UNION或UNION
18.优化group by提高group by语句的效率,可以将不需要的记录在group by之前过滤掉。如:
低效:select job, avg(sal) from emp group by job having job = ‘president’ or job=’manager’;
高效: select job, avg(sal) from emp having
19.将计算从等号左边移到右边,例如:把a*2>4 改为a>4/2;把TO_CHAR(zip) = ‘ 94002’ 改为zip = TO_NUMBER('94002');
20.不要使用Not,如Goods_no != 2,要改为:
21.不要使用is null , 如WHERE DEPT_CODE IS NOT NULL 要改为:
22.尽量多使用commit只要有可能就在程序中对每个delete insert update操作尽量多使用commit,这样系统性能会因为commit所释放的资源而大大提高。
23.尽量把循环的操作封装到PL/SQL写的存储过程里,因为存储过程都在服务端执行,所以没有数据往返的消耗。 有机会,将一些查询封装到函数里,而在普通SQL里使用这些函数,同样是很有效的优化。
24.开发人员编写sql语句时,尽量使用绑定变量,增加sql在sharepool中重用命中的几率。
25.大数据量的操作:大表中删除大记录:分段循环删除,批量绑定删除 ;大表中更新大记录:分段更新。
26.对于开销比较大的SQL查询,要建立数据库索引。数据库索引可以大大加快数据库的查询速度,索引是把表中的逻辑值映射到RowID,因此索引能进行快速定位数据的物理地址,快速返回想要查询的结果集。
27.索引不被使用的几种情况:null,比较,not函数
28.索引列上不要进行计算,如where trunc(order_date)=trunc(sysdate) ,
29.低效:select * from emp where deptno > 3高效:select * from emp where deptno >=4
两者的区别在于,前者dbms将直接跳到第一个deptno等于4的记录,而后者将首先定位到deptno等于3的记录并且向前扫描到第一个deptno大于3的。
30.当插入的数据为数据表中的记录数量的10%以上,首先需要删除该表的索引来提高数据的插入效率,当数据插入后,再建立索引。
31.通常如果一个表有5个字段经常作为查询条件,那么创建5个单独字段的索引,然后再创建一个包含这5个字段的组合索引。索引的列尽量编写在where条件语句的最后,以便执行计划命中索引。
32.删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况).而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.
33.Update 多个Column 例子:
MAX(SAL_RANGE)FROM EMP_CATEGORIES)