oracle SQL性能优化〔转贴〕

（1）     选择最有效率的表名顺序(只在基于规则的优化器中有效)：

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询,那就需要选择交叉表(intersection table)作为基础表,交叉表是指那个被其他表所引用的表.

（2）     WHERE子句中的连接顺序．：51Testing软件测试网*@dY(B%Zb|0i

ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.51Testing软件测试网0t,Q*n)MU@3Ht3mY

（3）     SELECT子句中避免使用‘ * ‘：51Testing软件测试网1P#EoI?)gg)L`

ORACLE在解析的过程中,会将'*'依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间

（4）     减少访问数据库的次数：

ORACLE在内部执行了许多工作:解析SQL语句,估算索引的利用率,绑定变量,读数据块等；

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

（6）     使用DECODE函数来减少处理时间：

使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.51Testing软件测试网t%j@9rh b

（7）     整合简单,无关联的数据库访问：

如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)

（8）     删除重复记录：51Testing软件测试网s2Y&A_AGx

最高效的删除重复记录方法(因为使用了ROWID)例子：

DELETE  FROM  EMP E  WHERE  E.ROWID > (SELECT MIN(X.ROWID)
6@TTt:j)[q!f"]0FROM  EMP X  WHERE  X.EMP_NO = E.EMP_NO);

（9）     用TRUNCATE替代DELETE：51Testing软件测试网
n@F,|0\U

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

（10） 尽量多使用COMMIT：51Testing软件测试网1WX#RO5g Tb-j6X+T&m @

只要有可能,在程序中尽量多使用COMMIT,这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:51Testing软件测试网W:_q!X!rjT
COMMIT所释放的资源:
'Y2D K7O5y~)F(]0a.回滚段上用于恢复数据的信息.
2D,L YZ"m.{6v0b.被程序语句获得的锁51Testing软件测试网h.D7nb$[?z
c. redo log buffer中的空间
{v/U.mKK2^ K0d. ORACLE为管理上述3种资源中的内部花费

（11） 用Where子句替换HAVING子句：51Testing软件测试网8g1}'dut

避免使用HAVING子句, HAVING只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序,总计等操作.如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.(非oracle中)on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后，因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的，where也应该比having快点的，因为它过滤数据后才进行sum，在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是where可以使用rushmore技术，而having就不能，在速度上后者要慢如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，根据上篇写的工作流程，where的作用时间是在计算之前就完成的，而having就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里

（12） 减少对表的查询：

在含有子查询的SQL语句中,要特别注意减少对表的查询.例子：51Testing软件测试网F+fg R[1I

    SELECT TAB_NAMEFROMTABLESWHERE(TAB_NAME,DB_VER) = (SELECT

TAB_NAME,DB_VER FROM TAB_COLUMNS  WHERE VERSION = 604)

（13） 通过内部函数提高SQL效率.：

复杂的SQL往往牺牲了执行效率.能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的51Testing软件测试网lOI Vx6KvY9J0C9|

（14） 使用表的别名(Alias)：

当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.

（15） 用EXISTS替代IN、用NOT EXISTS替代NOT IN：51Testing软件测试网[!XC1w c(P

在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下,使用EXISTS(或NOT EXISTS)通常将提高查询的效率.在子查询中,NOT IN子句将执行一个内部的排序和合并.无论在哪种情况下,NOT IN都是最低效的(因为它对子查询中的表执行了一个全表遍历).为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.51Testing软件测试网-jh-t$mG VW

例子：

（高效）SELECT*FROM EMP (基础表)  WHERE EMPNO > 0  AND EXISTS(SELECT‘X'  FROMDEPT  WHERE DEPT.DEPTNO = EMP.DEPTNO  AND LOC = ‘MELB')

(低效)SELECT *FROM EMP (基础表)  WHERE EMPNO > 0  AND DEPTNOIN(SELECTDEPTNO  FROM DEPT  WHERE LOC = ‘MELB')

（16） 识别'低效执行'的SQL语句：51Testing软件测试网:b Azp5[5r9||{

虽然目前各种关于SQL优化的图形化工具层出不穷,但是写出自己的SQL工具来解决问题始终是一个最好的方法：

SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
%{tqf(l0xl0p0ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2)Hit_radio,51Testing软件测试网'}
gzw4V3?Q9n
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
pY(GNZkR d(g\-n0SQL_TEXT51Testing软件测试网sm*]5Wk,ap"Nv
FROM V$SQLAREA51Testing软件测试网!E+v7}F PH'o[!BZq
WHERE EXECUTIONS>051Testing软件测试网{M:M"Z[E.o
AND BUFFER_GETS > 051Testing软件测试网lQe0R%D%F
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.851Testing软件测试网5P,j2zy z(?Bx
J
ORDER BY  4DESC;

（17） 用索引提高效率：

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

ALTER INDEX<indexname></indexname>REBUILD<tablespacename> </tablespacename>

（18） 用EXISTS替换DISTINCT：

当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT.一般可以考虑用EXIST替换, EXISTS使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.例子：51Testing软件测试网't hzTb,i?

      (低效):51Testing软件测试网/_,JBu o$W*m$s(ET
SELECT DISTINCT DEPT_NO,DEPT_NAME  FROM DEPT D , EMP E51Testing软件测试网 {!hB"y7[rSBh

WHERE D.DEPT_NO = E.DEPT_NO51Testing软件测试网Lu Src9yA7r
(高效):51Testing软件测试网.B,t5xd-R-@fK O2W0q
SELECT DEPT_NO,DEPT_NAME  FROM DEPT D  WHERE EXISTS ( SELECT‘X'51Testing软件测试网6^Pl*v]6pD |
FROM EMP E  WHERE E.DEPT_NO = D.DEPT_NO);51Testing软件测试网%{G| D3Ps

（19） sql语句用大写的；因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行51Testing软件测试网Nv9{;L1D,E'B3j

（20） 在java代码中尽量少用连接符“＋”连接字符串！51Testing软件测试网3u9T-T j\C!E6M Z

（21） 避免在索引列上使用NOT通常，　51Testing软件测试网"N w,VCM {8t?

我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响.当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描.51Testing软件测试网:x9rM5BQqBx.Ey7\

（22） 避免在索引列上使用计算．51Testing软件测试网8aF H/V%r#R
WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．51Testing软件测试网8M q$Mit:U
举例:51Testing软件测试网8l BT1TL'i'CE?{5o'M
低效：
^k$SIz_w0SELECT … FROM  DEPT  WHERE SAL * 12 > 25000;51Testing软件测试网.Y$?IG;Pz
高效:
d tn!L:R$E'^G6He o0SELECT … FROM DEPT WHERE SAL > 25000/12;

（23） 用>=替代>51Testing软件测试网x&}x^9]G

高效:51Testing软件测试网L S.V5db;R
SELECT * FROM  EMP  WHERE  DEPTNO >=451Testing软件测试网%pFj!m Sv$G r
低效:
?$`$iep'uJ6q)_y0SELECT * FROM EMP WHERE DEPTNO >351Testing软件测试网`3e4a;zN+wXD7af$i.z
两者的区别在于,前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.51Testing软件测试网;PjGkr fM6v7?w

（24） 用UNION替换OR (适用于索引列)51Testing软件测试网'N N0_8V$E6C

通常情况下,用UNION替换WHERE子句中的OR将会起到较好的效果.对索引列使用OR将造成全表扫描.注意,以上规则只针对多个索引列有效.如果有column没有被索引,查询效率可能会因为你没有选择OR而降低.在下面的例子中, LOC_ID和REGION上都建有索引.51Testing软件测试网#rv{b/[ V%Of9??,C@l
高效:
/]rW(E2Q0SELECTLOC_ID , LOC_DESC , REGION
[n7M,}8Vud0FROMLOCATION
0L&p2]p{f0U,E0WHERELOC_ID = 10
iZ4b[ O[t;Pw3u0UNION51Testing软件测试网Z n
Z'yEMk:P
SELECTLOC_ID , LOC_DESC , REGION51Testing软件测试网f| \Tq)K#{
FROMLOCATION
$~] P(l9}8^(H^O0WHEREREGION = “MELBOURNE”51Testing软件测试网Ao
N4Pr/p
低效:
9p(h^ n}0O@0SELECTLOC_ID , LOC_DESC , REGION51Testing软件测试网+p@&t ~;E6a,K u
FROMLOCATION
z\9e])[
[0WHERELOC_ID = 10ORREGION = “MELBOURNE”
p f ~B-j0如果你坚持要用OR,那就需要返回记录最少的索引列写在最前面.

（25） 用IN来替换OR  51Testing软件测试网;` v \$QP

这是一条简单易记的规则，但是实际的执行效果还须检验，在ORACLE8i下，两者的执行路径似乎是相同的．　51Testing软件测试网 H/e0Mh]5Ov&WJj

低效:
4^Ma`b4NN0SELECT….FROMLOCATIONWHERELOC_ID = 10ORLOC_ID = 20ORLOC_ID = 3051Testing软件测试网1RK+z,A t9J#` B
高效51Testing软件测试网6F `n+a1j8wU/a
SELECT…FROMLOCATIONWHERELOC_IN  IN(10,20,30);

（26） 避免在索引列上使用IS NULL和IS NOT NULL51Testing软件测试网r@-Ju @

避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引．对于单列索引，如果列包含空值，索引中将不存在此记录.对于复合索引，如果每个列都为空，索引中同样不存在此记录.　如果至少有一个列不为空，则记录存在于索引中．举例:如果唯一性索引建立在表的A列和B列上,并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入).然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空.因此你可以插入1000条具有相同键值的记录,当然它们都是空!因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引.