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');--exsistselect 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  10Time differences is  9Time differences is  10

9.4

效率由高到低：exists> 外串連=子查詢

重點關注10:用NOT EXISTS替代NOT IN .

案例 ：尋找 非DEPT_1部門的 人員姓名

10.1 SQL語句

 --子查詢 SELECT  p.pnameFROM T_PERSONWHERE DEPTID NOT IN (SELECT DEPTIDFROM T_DEPARTMENTWHERE 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 >子查詢