ORACLE SQL效率 实践

重点关注9:用EXISTS替代IN.重点关注

案例 ：查找DEPT_1部门的人员姓名

9.1 SQL语句

--子查询
select pname from t_person where deptid in (
       select deptid from t_department where deptname like 'DEPT_1'
);
--exsist
select pname from t_person where exists (
       select * from t_department where t_department.deptid = t_person.deptid
       and deptname like 'DEPT_1'
);
--外连接
select pname
  from t_person p LEFT join t_department d ON p.deptid = d.deptid
 WHERE d.deptname like 'DEPT_1';

9.2 测试代码块

--子查询代码块测试
  declare
        v_sql  varchar2(300);
        v_diff number;
      begin
        v_sql := 'select pname from t_person where deptid in (
         select deptid from t_department where deptname like ''DEPT_1'') ';
        select F_TEST_TIME_efficiency(v_sql) into v_diff from dual;
      end;
  
--exists代码块测试
    declare
      v_sql  varchar2(300);
      v_diff number;
    begin
      v_sql := 'select pname from t_person where exists (
       select * from t_department where t_department.deptid = t_person.deptid
       and deptname like ''DEPT_1'')';
      select F_TEST_TIME_efficiency(v_sql) into v_diff from dual;
    end;
 
--外连接代码块测试
    declare
      v_sql  varchar2(300);
      v_diff number;
    begin
      v_sql := 'select pname
  from t_person p LEFT join t_department d ON p.deptid = d.deptid
 WHERE d.deptname like ''DEPT_1''';
      select F_TEST_TIME_efficiency(v_sql) into v_diff from dual;
    end;

9.3 输出结果

Time differences is  10

Time differences is  9

Time differences is  10

9.4

效率由高到低：exists> 外连接=子查询

重点关注10:用NOT EXISTS替代NOT IN .

案例 ：查找 非DEPT_1部门的 人员姓名

10.1 SQL语句

 --子查询
 SELECT  p.pname
FROM T_PERSON
WHERE DEPTID NOT IN (SELECT DEPTID
FROM T_DEPARTMENT
WHERE DEPTNAME LIKE 'DEPT_1');
 --外连接 查询结果包含没有部门的人员
 select p.pname
  from t_person p left join t_department d
 on p.deptid = d.deptid 
   where d.DEPTNAME  not like 'DEPT_1' or d.DEPTNAME is null;
--exists法  查询结果不包含没有部门的人员
select p.pname
  from t_person p
 where exists (
 select * from t_department  d where  p.deptid = d.deptid and d.DEPTNAME  not like 'DEPT_1'  );

10.2 执行代码块(在每个测试之前都要清空数据库缓存)

 

--子查询代码块测试
declare
      v_sql  varchar2(300);
      v_diff number;
    begin
      v_sql := 'SELECT T_PERSON.pname
    FROM T_PERSON
    WHERE DEPTID NOT IN (SELECT DEPTID
    FROM T_DEPARTMENT
    WHERE DEPTNAME LIKE ''DEPT_1'')';
      select F_TEST_TIME_efficiency(v_sql) into v_diff from dual;
    end;
  
--外连接代码块测试
    declare
      v_sql  varchar2(300);
      v_diff number;
    begin
      v_sql := 'select p.pname
      from t_person p left join t_department d
     on p.deptid = d.deptid 
       where d.DEPTNAME  not like ''DEPT_1'' or d.DEPTNAME is null';
      select F_TEST_TIME_efficiency(v_sql) into v_diff from dual;
    end;
 
--exists代码块测试
    declare
      v_sql  varchar2(300);
      v_diff number;
    begin
      v_sql := 'select p.pname
      from t_person p
     where exists (
     select * from t_department  d where  p.deptid = d.deptid and d.DEPTNAME  not like ''DEPT_1'')';
      select F_TEST_TIME_efficiency(v_sql) into v_diff from dual;
    end;
 

10.3 清空oracle缓存的语句

ALTER SYSTEM FLUSH BUFFER_CACHE;  
ALTER SYSTEM FLUSH SHARED_POOL ;
ALTER SYSTEM FLUSH GLOBAL CONTEXT;
ALTER SYSTEM SET EVENTS='IMMEDIATE TRACE NAME FLUSH_CACHE';
 

10.4 输出结果

Time differences is  9

Time differences is  8

Time differences is  8

10.5 结论

效率由高到低：外连接=exists >子查询