05.09 oracle语句优化

1、ORDER BY子句

列的顺序与索引列的顺序一致 ,列应为非空列。

2、使用表的别名

多表连接时，使用表的别名来引用列。　例：

SELECT abc002,abd003

FROM ab001 ,ab020

WHERE ab001.col2=ab020.col3×

SELECT t1.abc002,t2.abd003

FROM ab001 t1,ab020 t2

WHERE t1.col2=t2.col3

3、用NOT EXISTS代替NOT IN

例：

SELECT ......

FROM emp

WHERE dept_no NOT IN ( SELECT dept_no

FROM dept

WHERE dept_cat=’A’);　 　　×

SELECT ......

FROM emp e

WHERE NOT EXISTS ( SELECT ‘X’

FROM dept

WHERE dept_no=e.dept_no

AND dept_cat=’A’);　　√

4、用多表连接代替EXISTS子句

例：

SELECT ......

FROM emp

WHERE EXISTS ( SELECT ‘X’

FROM dept

WHERE dept_no=e.dept_no

AND dept_cat=’A’); ×

SELECT ......

FROM emp e,dept d

WHERE e.dept_no=d.dept_no

AND dept_cat=’A’; √

5、用UNION ALL替换UNION(如果有可能的话)

当SQL语句需要UNION两个查询结果集合时,这两个结果

集合会以UNION-ALL的方式被合并, 然后在输出最终结果

前进行排序。

如果用UNION ALL替代UNION, 这样排序就不是必要了。

效率就会因此得到提高。

6、使用ROWID提高检索速度

ROWID包含了表中记录的物理位置信息。ORACLE采用索引(INDEX)实现了

数据和存放数据的物理位置(ROWID)之间的联系。因此那些基于索引列

的查询就可以得到性能上的提高.

对SELECT得到的单行记录，需进行DELETE、UPDATE操作时，使用ROWID

将会使效率大大提高。

例：SELECT rowid

INTO v_rowid

FROM t1

WHERE con1 = ...;

UPDATE t1

SET col2= ......

WHERE rowid=v_rowid;

7、WHERE过滤原则

过滤记录数最多的条件放在最前面

例：SELECT info

FROM taba a,tabb b,tabc c

WHERE a.acol between :alow and :ahigh

AND b.bcol between :blow and :bhigh

AND c.ccol between :clow and :chigh

AND a.key1 = b.key1

AND a.key2 = c.key2;

其中，A表的acol列可以最多减少查询的记录数目，其次为B表的bcol

列，依次类推。

8、共享SQL语句

为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语

句存放在内存中.这块位于系统全局区域SGA(system global area)的共

享池(shared buffer pool)中的内存可以被所有的数据库用户共享. 因

此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执

行过的语句完全相同，ORACLE就能很快获得已经被解析的语句以及最好

的执行路径。ORACLE的这个功能大大地提高了SQL的执行性能并节省了内

存的使用。

如经常使用select * from dept where deptno=值;　值改为变量

select * from dept where deptno=:d; 则可以使用共享。

　　可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering) ,这

个功能并不适用于多表连接查询。

9、清除大量数据

truncate替代delete， truncate不用commit，delete需要commit;

10、尽量避免使用select *

ORACLE在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作

是通过查询数据字典完成的, 这意味着将耗费更多的时间。

11、强制索引

对ORACLE优化器缺省指定的索引不满意,需要手工修改，则采用强制

索引：

SELECT /*+INDEX(表名 索引名)*/ 列名

FROM ……