关于sql调优之oracle数据库提高sql的执行效率

以Oracle为实验用数据库,使用著名的emp表。安装oracle数据库之后,scott用户的密码是tiger. 在他的schema下有几张sample表,很多sql教程都以这几张表为基础. 据说,熟悉oracle数据库的人提到smith这个人名,就能联想起sample表中他的工作是clerk. emp表就是sample表之一。这张表原始数据只有14行,为了体现不同sql性能上的差异,我们需要多填充一些数据进去。作为填充数据的预备知识,我 们可以看一下如何生成一系列从小到大的id:

SELECT ROWNUM
FROM DUAL
CONNECT BY LEVEL < 10000;

填充随机数据可以借助于dbms_random包,不想覆盖已有的表,所以新创建一个表结构基本一样的: 

create table emp_new
as
select level                                                          empno,
       SYS.dbms_random.String('u', SYS.dbms_random.value(3,10))       ename,
       SYS.dbms_random.String('u', SYS.dbms_random.value(3,9))        job,
       round(SYS.dbms_random.value(1000,9999))                        mgr,
       TO_DATE (   ROUND (DBMS_RANDOM.VALUE (128))
                    || '-'
                    || ROUND (DBMS_RANDOM.VALUE (112))
                    || '-'
                    || ROUND (DBMS_RANDOM.VALUE (19802012)),
                    'DD-MM-YYYY'
                   )                                                   hiredate,
        round(SYS.dbms_random.value(300,9999))                         sal,
        round(SYS.dbms_random.value(1,6)) * 100                        comm,
        round(SYS.dbms_random.value(1,4)) * 10                         deptno
 FROM DUAL
CONNECT BY LEVEL < 1000000;
 
这里有一个局限,原本的emp表mgr列reference empno列。上面新创建的emp_new中失去了这个constraint. 这点可以从Oracle SQL Developer中看到。

emp表:
with_constrant.png
emp_new表:
no_constrant.png

下面就开始测试sql了:
1. 先比较一下加primary key前后的结果:
select * from emp_new where empno=1;
加primary key constraint之前运行0.023秒。 加了primary key constraint之后0.001秒。加primary key constraint在100万条数据上大约花费4秒钟。
2. where子句 vs. having子句
select deptno, avg(sal) from emp_new group by deptno having deptno != 10 and deptno != 20;
0.24秒
select deptno, avg(sal) from emp_new where deptno != 10 and deptno != 20 group by deptno ;
0.16秒
所以having中的条件一般用于对一些集合函数的比较,如count()等,除此之外,一般条件应该写在where子句中。

3. 减少对表的查询
update emp_new set sal=(select max(sal) from emp_new), comm=(select max(comm) from emp_new) where empno=1237;
0.11秒左右
update emp_new set (sal, comm) =(select max(sal), max(comm) from emp_new) where empno=1224;
0.07秒到0.08秒之间

注意:以上三个测试都只fetch前50条数据。

4. 传说中用exists替代in通常可提高查询效率, not exists 也比not in 快。
先生成dept_new表:
create table dept_new
as
select level                                                          deptno,
       SYS.dbms_random.String(
'u', SYS.dbms_random.value(3,10))       dname,
       SYS.dbms_random.String(
'u', SYS.dbms_random.value(3,9))        loc
FROM DUAL
CONNECT 
BY LEVEL < 10000;
实际测试中,无论是执行计划还是实际测试的速度都是基本一致的。
第一组
select * from emp_new e where e.empno > 986000 and e.deptno in (select d.deptno from dept_new d where d.loc='AYDN') select * from emp_new e where empno > 986000 and exists (select * from dept_new d where d.deptno = e.deptno and d.loc='AYDN') 第二组 select e.empno from emp_new e where e.empno > 996000 and not exists (select 1 from dept_new d where d.deptno = e.deptno and loc like 'A%') select e.empno from emp_new e where e.empno > 996000 and e.deptno not in (select d.deptno from dept_new d where loc like 'A%')

来源://作者:/更新时间:2013-04-03
相关文章

最新文章

新热推荐

文章排行