PL/SQL优化规则小结

 

PL/SQL优化规则小结

 

注：O代表比X 更优化的写法。

以下只是本人知道的一些基本的SQL优化，我更希望能从网友你们那里学习到更多的、更好的优化办法，提高自己的SQL编写能力。

另外，有什么oracle管理、oracle编程方面的好书能够给予推荐一下吗？

 

1、尽量避免对索引列进行计算

例：

X    WHERE sa*1.1>950

O    WHERE sa>950/1.1

 

X    WHERE SUBSTR(name,1,7)=’CAP’

O    WHERE name LIKE ‘CAP%

 

2、比较值与索引列的数据类型一致

例：

emp： NUMBER型

O   WHERE emp=123

X   WHERE emp=’123’

 

    emp_char：CHAR型

X   WHERE emp_char=123 （此时，查询时，不利用索引列）

O   WHERE emp_char=’123’

 

3、避免使用NULL

例：

X  WHERE comm IS NOT NULL

X  WHERE comm IS NULL

O  WHERE comm>=0

  

4、对于复合索引，SQL语句必须使用主索引列

例：复合索引(deptno,job)

O  WHERE deptno=20 AND job=’MANAGER’

O  WHERE deptno=20

O  WHERE job=’MANAGER’ AND deptno=20

X  WHERE job=’MANAGER’  

 

5、ORDER BY子句

O  子句中，列的顺序与索引列的顺序一致。

O  子句中，列应为非空列。

 

6、查询列与索引列次序（WHERE）一致

O SELECT empno,job FROM emp WHERE empno<100 AND job=’MANAGER’;

X SELECT empno,job FROM emp WHERE job=’MANAGER’ AND empno<100;

 

7、尽量少用嵌套查询

X SELECT * FROM emp WHERE empno IN (SELECT empno FROM OnWork);

O SELECT emp.* FROM emp t1,OnWork t2 WHERE t1.empno = t2.empno;

 

8、多表连接时，使用表的别名来引用列。

例：

X  SELECT ab02.aab001,ab01.aab004

   FROM ab02 ,ab01

  WHERE ab02.aab001 = ab01.aab001

......

O  SELECT t1.aab004,t2.aab001

   FROM ab02 t1,ab01 t2

  WHERE t1.aab001=t2.aab001

 

9、用NOT EXISTS代替NOT IN

例：

X  SELECT * FROM ab01

    WHERE aab001 NOT IN

      (SELECT aab001 FROM ab02 WHERE aae140='3'); 

O  SELECT * FROM ab01 t

    WHERE NOT EXISTS

      (SELECT 1 FROM ab02 WHERE aab001=t.aab001 AND aae140='3');

 

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

例：

X  SELECT * FROM ab01 t

   WHERE EXISTS

     (SELECT 1 FROM ab02 WHERE aab001=t.aab001 AND aae140=‘3’);

O  SELECT t1.* FROM ab01 t1,ab02 t2

   WHERE t1.aab001 = t2.aab001 AND t2.aae140=‘3’;

 

11、少用DISTINCT，用EXISTS代替

X  SELECT DISTINCT ac01.aac016 aac016 FROM ac01,ac02

    WHERE ac01.aac001=ac02.aac001 

      AND ac01.aab001 = '100659‘

      AND NVL(ac01.aac016,'0') <> '107'

      AND NVL(ac01.aac008,'0') = '1'

      AND ac02.aae140 = '3' AND ac02.aac031 = '1'; 

O  SELECT aac016 FROM ac01 t

    WHERE aab001 = '100659'

      AND NVL(aac016,'0') <> '107'

      AND NVL(aac008,'0') = '1'

      AND EXISTS (SELECT 1 FROM ac02 WHERE aac001=t.aac001

             AND ac02.aae140 = '3' AND ac02.aac031 = '1');

 

12、使用ROWID提高检索速度

对SELECT得到的单行记录，需进行DELETE、UPDATE操作时，使用ROWID将会使效率大大提高。

例：SELECT rowid INTO v_rowid FROM t1

     WHERE con1 FOR UPDATE OF col2;

……

……

   UPDATE t1 SET col2=......

WHERE rowid=v_rowid;

 

13、查询的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列，依次类推。