oracle语句优化方式

1、SQL语句尽量用大写： 因为oracle总是先解析SQL语句，把小写的字母转换成大写的再执行。

2、使用表的别名： 当在SQL语句中连接多个表时，尽量使用表的别名并把别名前缀于每个列上。 这样一来，就可以减少解析的时间并减少那些由列歧义引起的语法错误。

3、选择最有效率的表名顺序(只在基于规则的优化器(RBO)中有效)： ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句 中写在最后的表(基础表也称为驱动表，driving table)将被最先处理，在FROM 子句中包含多个表的情况下，必须选择记录条数最少的表作为基础表。如果有3 个以上的表连接查询，那就需要选择交叉表(intesection table)作为基础表，交 叉表是指那个被其他表所引用的表。

4、WHERE子句中的连接顺序： ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接 必须写在WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE 子句的末尾。

5、SELECT子句中避免使用*： ORACLE在解析的过程中，会将'*'依次转换成所有的列名，这个工作是通过查询数 据字典完成的，这意味着将耗费更多的时间。

6、减少访问数据库的次数： ORACLE在内部执行了许多工作：解析SQL语句，估算索引的利用率，绑定变量，读数据块等。

7、整合简单、无关联的数据库访问： 如果有几个简单的数据库查询语句，可以整合到一个查询中(即使它们之间没有关系)。

8、在SQL*Plus，SQL*Forms和Pro*C中重新设置ARRAYSIZE参数，可以增加每次数据库访问的 检索数据量，建议值为200。

9、删除重复记录： 最高效的删除重复记录方法： DELETE FROM TEMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM TEMP1 X WHERE X.TEMP_NO = E.TEMP_NO)

10、减少对表的查询： 在含有子查询的SQL语句中，要特别注意减少对表的查询。 SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER)= (SELECT TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION=604);

11、避免使用耗费资源的操作： 带有DISTINCT、UNION、MINUS、INTERSECT、ORDER BY的SQL语句会启动SQL引擎执 行耗费资源的排序(SORT)功能。DISTINCT需要一次排序操作，而其他的至少需要执行两次排 序。通常，带有UNION、MINUS、INTERSECT的SQL语句都可以用其他方式重写。如果数 据库的SORT_AREA_SIZE调配得好，使用UNION，MINUS，INTERSECT也是可以考虑的。

12、优化GROUP BY： 提高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、根据需要用UNION ALL替换UNION： 当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以UNION-ALL的方式的方式 被合并，然后在输出最终结果前进行排序。如果用UNION ALL替代UNION，这样排序就不是 必要了。效率会因此得到提高。需要注意UNION ALL将重复输出两个结果集合中相同的记录。 因此要从业务需求使用UNION ALL的可行性。UNION将对结果集合排序，这个操作会使用到 SORT_AREA_SIZE这块内存。对于这块内存的优化也很重要。 低效： SELECT USER_ID,BILL_ID FROM USER_TAB1 WHERE AGE = '20' UNION SELECT USER_ID,BILL_ID FROM USER_TAB2 WHERE AGE = '20'; 高效： SELECT USER_ID,BILL_ID FROM USER_TAB1 WHERE AGE = '20' UNION ALL SELECT USER_ID,BILL_ID FROM USER_TAB2 WHERE AGE = '20';

14、用EXISTS替换DISTINCT： 当SQL包含一对多表查询时，避免在SELECT子句中使用DISTINCT，一般用EXISTS替换，EXISTS 查询更为迅速，因为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);

15、尽量多使用COMMIT： 只要有可能，在程序中尽量多使用COMMIT，这样程序的性能得到提高，需求也会因为COMMIT 所释放的资源而减少。 COMMIT所释放的资源： a、回滚段上用于恢复数据的信息。 b、被程序语句获得的锁。 c、redo log buffer中的空间。 d、ORDER为管理上述3种资源中的内部花销。

16、用WHERE子句替换HAVING子句： 避免使用HAVING子句，HAVING只会在检索出所有记录之后才对结果集进行过滤。这个处理要排 序，总计等操作。最好能通过WHERE子句限制限制记录的数目。 (非ORACLE中)on、where、having这三个都可以加条件的子句中，on是最先执行，where次之， having最后。 a、on是先把不符合条件的记录过滤后才进行统计，可以减少中间运算要处理的数据，速度是最快的。 b、where比having快点，因为它过滤数据后才进行sum，在两个表联接时采用on，所以在一个表的 时候，就剩下where跟having比较了。在单表查询统计的情况下，如果要过滤的条件没有涉及到 要计算字段，那它们的结果是一样的，只是where可以使用rushmore技术，而having就不能，在 速度上后者要慢。 如果涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，where的作用时间是在计 算之前就完成的，而having就是在计算之后才起作用的，所以在这种情况下，两者的结果会不同。在 多表联接查询时，on比where更早起作用。系统首先根据各个表之间的关联条件，把多个表合成一个 临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。

17、用TRUNCATE替代DELETE： 当删除表中的记录时，在通常情况下，回滚段(rollback segments)用来存放可以被恢复的信息。如果 你没有COMMIT事务，ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前 的状况)。而当运用TRUNCATE时，回滚段不再存放任何可被恢复的信息。当命令运行后，数据不能被 恢复。因此很少的资源被调用，执行时间也会很短。

18、使用DECODE函数来减少处理时间： 使用DECODE函数可以避免重复扫描相同记录或重复连接相同表。

19、用EXISTS替代IN、用NOT EXISTS替代NOT IN： 在基于基础表的查询中经常需要对另一个表进行联接。在这种情况下，使用EXISTS(或NOT EXISTS)通 常将提高查询的效率。在子查询中，NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下， NOT IN都是最低效的(要对子查询中的表执行了一个全表遍历)。所以尽量将NOT IN改写成外连接 (Outer Joins)或NOT EXISTS。 高效： SELECT A.* FROM TEMP(基础表) A WHERE AGE > 0 AND EXISTS(SELECT 1 FROM TEMP1 WHERE A.ID = ID AND NAME = 'TOM'); 低效： SELECT A.* FROM TEMP(基础表) A WHERE AGE > 0 AND A.ID IN (SELECT ID FROM TEMP1 WHERE NAME = 'TOM');

20、用索引提高效率： 索引是表的一个概念部分，用来提高检索数据的效率，ORACLE使用了复杂的自平衡B-tree结构。通常 通过索引查询数据比全表扫描要快。当ORACLE找出执行查询和Update语句的最佳路径时，ORACLE优 化器将使用索引。同样在联结多个表时使用索引也可以提高效率。 另外一个使用索引的好处是，它提供了主键(primary key)的唯一性验证。那些LONG或LONG RAW数据 类型，你可以索引几乎所有的列。通常在大型表中使用索引特别有效。在扫描小表时，使用索引同样能提 高效率。 使用索引虽能得到查询效率，但也必须注意到它的代价。索引需要空间来存储，也需要定期维护，每当有 记录在表中增减或索引列被修改时，索引本身也会被修改。这意味着每条记录的INSERT、DELETE、UPDATE 将会将为此多付出4、5次的磁盘I/O。因为索引需要额外的存储空间和处理，一些不必要的索引反而会使查询反 应时间变慢。

21、定期的重构索引： ALTER INDEX <INDEXNAME> REBUILD<TABLESPACENAME>22、用>=替代>： 高效： SELECT * FROM TEMP ID >=4; 低效： SELECT * FROM TEMP WHERE ID >3;23、避免在索引上使用NOT： NOT会产生和在索引上使用函数相同的影响。当ORACLE遇到NOT，就会停止使用索引转而执行全表扫描。