重点关注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 >子查询