plsql语句优化方法

经验分享

ORACLESQL语句的优化：

1. 选择最有效率的表名顺序：应该选择记录条数最少的表作为基表！

因为oracle解析器的处理顺序是从右至左的.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.应该选择记录条数最少的表作为基表，排序连接后面的表中之后可以最大限度的过滤掉后面的表的不必要的的记录，加快执行时间，提高执行效率.

1. WHERE子句中的连接顺序。Oracle Parser采用至下而上的WHERE子句，因此，表之间的连接应该写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句末尾.

1. 使用表别名(Alias): 当关联的表比较多时，通过使用表别名并且Column名前面加上别名前缀时，可以很有效的节省Oracle解析器解析表名的时间.

1. 要尽量避免使用HAVING子句，因为HAVING需要在检索出所有的记录之后才对结果集进行过滤，而这个处理还需要进行排序和总计的操作。可以通过使用WHERE子句来代替HAVING关键字。通过WHERE子句过滤现在查询记录的条数可以节约ORACLE很多的工作量和开销.

1. > 及 < 操作符

一般不用调整，但是如果可以优先写成 >= 和 <=.

如：一个表有100万记录，一个数值型字段A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了，因为A>2时ORACLE会先找出为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引。

6.       行数总计时，COUNT(*),COUNT(1)的速率基本一样,但通过索引检索对索引的计数是最快的COUNT(EMPNO).

1. 减少访问数据库的次数。当执行每条sql语句时，ORACLE内部还会执行很多的工作：解析SQL语句，绑定变量，读取数据等，因此尽量较少对数据库的访问次数就能减少ORACLE的工作量.

例:

低效，需要访问两次数据库：

SELECT*FROM oe_order_lines_all ool

WHERE ool.header_id=35718;

SELECT *FROM oe_order_lines_all ool

WHERE ool.header_id=38516;

高效！只访问了一次数据库：

SELECT *FROM oe_order_lines_all ool1,oe_order_lines_all ool2

WHERE ool1.header_id=38516

ANDool2.header_id=35718;

1. 使用DECODE函数可以避免重发扫描相同记录或重复连接相同的表.

例:

低效:

SELECTCOUNT(*),SUM(emp.SAL)

FROMEMP

WHERE emp.deptno = 20

AND emp.ENAME LIKE'SMITH%';

SELECTCOUNT(*),SUM(emp.SAL)

FROMEMP

WHERE emp.deptno = 30

AND emp.ENAME LIKE'SMITH%';

--你可以用DECODE函数高效地得到相同结果

SELECTCOUNT(DECODE(emp.deptno,20,’X’,NULL)) D20_COUNT,

COUNT(DECODE(emp.deptno,30,’X’,NULL)) D30_COUNT,

SUM(DECODE(emp.deptno,20,SAL,NULL)) D20_SAL,

SUM(DECODE(emp.deptno,30,SAL,NULL)) D30_SAL

FROM EMP WHERE emp.ENAME LIKE'SMITH%';

1. 注意：要尽量减少访问表的次数，在子查询语句中比较常见.

例:

              低效:

           UPDATE EMP_C1 X

SET X.SAL= (SELECTMAX(Y.SAL) FROM EMP Y),

X.HIREDATE = (SELECTMAX(Y.HIREDATE) FROM EMP Y)

WHERE X.DEPTNO = 20;

高效:

UPDATE EMP_C1 X

SET(X.SAL, X.HIREDATE) = (SELECTMAX(Y.SAL) , MAX(Y.HIREDATE)FROM EMP Y) WHERE X.DEPTNO = 20;

1.  ‘EXISTS’ 和’NOTEXISTS’用法:在处理大量的数据时，SQL语句中使用‘EXISTS’来代替’IN’和’NOT EXISTS’来代替’NOT IN’ 处理的效率要大很多;

例:

低效:

SELECT*FROM Oe_Order_Lines_All ool

WHERE ool.ship_from_org_id=86

AND ool.header_id IN (SELECT ooh.header_id FROMoe_order_headers_all ooh);

高效:

SELECT*FROM Oe_Order_Lines_All ool

WHERE ool.ship_from_org_id=86

ANDEXISTS (SELECT1FROMoe_order_headers_all ooh WHERE ool.org_id = ooh.org_id AND ool.header_id = ooh.header_id);

注：当所用的表之间相互可以连接时尽量使用表连接而不是’EXISTS’;因为表连接速度更快更高效;

1. 用索引来提高效率

 索引是表的一个概念部分,用来提高检索数据的效率. 实际上,ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证.

除了那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列. 通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率.

虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4, 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢

注: 定期的重构索引是有必要的.

1. 在’WHERE’子句中避免在索引列上使用计算。

同样道理，避免在索引列上使用’IS NULL’或’IS NOT NULL’，NOT或者’<>’ OR ’!=’号：因为索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中，即索引不能索引空值.当ORACLE遇到’NOT’就会停止执行索引搜索扫描而进行全表扫描.

1. 带通配符(%)的like语句.

因为通配符(%)在搜索次首出现，Orcale解析器无法跳过’%’使用索引，只能进行全表搜索，这样搜索的效率就会低很多。’%’在搜索词后，ORACLE就能使用搜索词的索引，使用索引扫描数据，效率将会得到很大的提高。

 ‘%’在搜索词后面进行的是索引搜索扫描：

 ‘%’在搜索词前面进行的是索引搜索全表扫描：