序优化方法Oracle解析原理1SQL语句尽量使用大写oracle总是先解析SQL语句，把小写的字母转换成大写的再执行2使用表的别名当在SQL语句中连接多个表时, 尽量使用表的别名并把别名前缀于每个列上。这样一来,就可以减少解析的时间并减少那些由列歧义引起的语法错误。3指定查询列名,不使用*ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。4避免使用前置通配符select name from tb where name like ‘%1001’;5WHERE子句中的连接顺序ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。6选择最有效率的表名顺序 (只在基于规则的优化器(RBO)中有效)ORACLE 的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表也称为驱动表,driving table)将被最先处理，在FROM子句中包含多个表的情况下,必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表。7删除重复记录 DELETE FROM TEMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM TEMP1 X WHERE X.TEMP_NO = E.TEMP_NO);8在含有子查询的SQL语句中,要特别注意减少对表的查询 SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = (SELECT TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604);9若不考虑删除重复行，使用Union all替代UnionUnion：对两个结果集进行并集操作，不包括重复行并进行默认规则的排序(所以效率低) Union All：对两个结果集进行并集操作，包括重复行，不进行排序(所以效率高)10当SQL包含一对多查询时,使用EXIST替换DISTINCT因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果低效：SELECT DISTINCT USER_ID,BILL_ID FROM USER_TAB1 D,USER_TAB2 E WHERE D.USER_ID= E.USER_ID;高效：SELECT USER_ID,BILL_ID FROM USER_TAB1 D WHERE EXISTS(SELECT 1 FROM USER_TAB2 E WHERE E.USER_ID= D.USER_ID); 11尽量用Where子句替换HAVING子句HAVING子句只会在检索出所有记录之后才对结果集进行过滤，所以最好能通过WHERE子句限制记录的数目。顺序 Where >Group >Having12提高GROUP BY 语句的效率,可以通过将不需要的记录在GROUP BY之前过滤掉低效：SELECT JOB,AVG(AGE) FROM TEMP GROUP BY JOB HAVING JOB = 'STUDENT' OR JOB = 'MANAGER';高效：SELECT JOB,AVG(AGE) FROM EMP WHERE JOB = 'STUDENT' OR JOB = 'MANAGER' GROUP BY JOB;13删除全表时，用TRUNCATE替代DELETE当删除表中的记录时,通常 回滚段(rollback segments)用来存放可以被恢复的信息。如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)。而当运用TRUNCATE时,回滚段不再存放任何可被恢复的信息。当命令运行后,数据不能被恢复。因此很少的资源被调用,执行时间也会很短。14用EXISTS替代IN、用NOT EXISTS替代 NOT IN在子查询中,NOT IN子句将执行一个内部的排序和合并(全表遍历)，比较低效低效：SELECT A.* FROM TEMP(基础表) A WHERE A.ID IN(SELECT ID FROM TEMP1); 高效：SELECT A.* FROM TEMP(基础表) A WHERE EXISTS(SELECT 1 FROM TEMP1 WHERE A.ID=TEMP1.ID);15用>=替代>低效：SELECT * FROM TEMP WHERE ID >3;高效：SELECT * FROM TEMP WHERE ID >=4;16用UNION替换OR(只适用于索引列)用UNION替换WHERE子句中的OR将会起到较好的效果。对索引列使用OR将造成全表扫描。注意,以上规则只针对多个索引列有效。如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低。低效：SELECT * FROM USER_TAB1 WHERE USER_ID = 10 OR AGE = 20; 高效：SELECT * FROM USER_TAB1 WHERE USER_ID = 10 UNION SELECT * FROM USER_TAB1 WHERE AGE = 20;17使用DECODE减少处理事件低效：select count(*) from 表 where 性别 ＝ 男； select count(*) from 表 where 性别 ＝ 女；高效：select sum(decode(性别，男，1，0))，sum(decode(性别，女，1，0)) from 表18利用外部连接"+"替代效率低下的not in运算低效：select a.empno from emp a where a.empno not in (select empno from emp1 where job='SALE')高效：select a.empno from emp a,emp1 b where a.empno=b.empno(+) and b.empno is null and b.job='SALE'19避免在索引列上使用NOT NOT会产生在和在索引列上使用函数相同的影响。当ORACLE遇到NOT,就会停止使用索引转而执行全表扫描。20避免在索引列上使用IS NULL和IS NOT NULL 避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引。21避免在索引列上使用函数计算WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描。 低效：SELECT … FROM TEMP WHERE SAL * 12 > 25000;高效：SELECT … FROM TEMP WHERE SAL > 25000/12;22总是使用索引的第一个列如果索引是建立在多个列上,只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引。当引用索引的第二个列时,优化器使用了全表扫描而忽略了索引。23避免改变索引列的类型 (明确根据字段类型指定数据值)自动转换：SELECT … FROM EMP WHERE EMP_TYPE = 123显示转换：SELECT … FROM EMP WHERE TO_NUMBER(EMP_TYPE)=12324尽量多使用COMMIT尽量多使用COMMIT, 这样程序的性能得到提高,COMMIT及时释放资源、解锁等等25避免在order by子句中使用表达式order by 语句都会降低查询速度,可以考虑使用的列建立一个索引
转载请注明原文地址: https://www.6miu.com/read-5036268.html
