oracle联接查询SQL性能测试

--以下测试均在删除emp.deptno外键情况下执行--附件udump.rar为测试报告 每份报告中包含SQL解析过程及执行计划--(每种SQL分为两条 下一条为oracle最后解析执行的SQL)--【假设要查询的部门必须在部门表中存在】--【例如：如果员工表中存在部门编号为10的员工，但是部门表中却不存在编号为10的部门，则结果集中不应该显示部门编号为10的员工相关信息】--第一类要查出必须存在一个部门编号为10的员工的所在部门名称以及员工编码及姓名--五种写法--第一种select d.dname, e.empno, e.ename  from emp e, dept d where e.deptno = d.deptno   and d.deptno = 10;SELECT "D"."DNAME" "DNAME", "E"."EMPNO" "EMPNO", "E"."ENAME" "ENAME"  FROM "SCOTT"."EMP" "E", "SCOTT"."DEPT" "D" WHERE "E"."DEPTNO" = 10   AND "D"."DEPTNO" = 10;--第二种select d.dname, e.empno, e.ename  from emp e, (select * from dept t where t.deptno = 10) d where e.deptno = d.deptno;SELECT "T"."DNAME" "DNAME", "E"."EMPNO" "EMPNO", "E"."ENAME" "ENAME"  FROM "SCOTT"."EMP" "E", "SCOTT"."DEPT" "T" WHERE "E"."DEPTNO" = 10   AND "T"."DEPTNO" = 10;--第三种select d.dname, e.empno, e.ename  from emp e, (select t.deptno, t.dname from dept t where t.deptno = 10) d where e.deptno = d.deptno;SELECT "T"."DNAME" "DNAME", "E"."EMPNO" "EMPNO", "E"."ENAME" "ENAME"  FROM "SCOTT"."EMP" "E", "SCOTT"."DEPT" "T" WHERE "E"."DEPTNO" = 10   AND "T"."DEPTNO" = 10;--第四种select d.dname, e.empno, e.ename  from emp e, dept d where e.deptno = d.deptno   and exists (select 1 from dual where e.deptno = 10);SELECT /*+ */ "D"."DNAME"           "DNAME", "SYS_ALIAS_1"."EMPNO" "EMPNO", "SYS_ALIAS_1"."ENAME" "ENAME"  FROM "SCOTT"."EMP" "SYS_ALIAS_1", "SCOTT"."DEPT" "D" WHERE EXISTS (SELECT /*+ */         0          FROM "SYS"."DUAL" "DUAL"         WHERE "SYS_ALIAS_1"."DEPTNO" = 10)   AND "SYS_ALIAS_1"."DEPTNO" = "D"."DEPTNO"--第五种  select d.dname, e.empno, e.ename          from emp e, dept d         where e.deptno = d.deptno           and exists (select 1                  from dept t                 where d.deptno = t.deptno                   and t.deptno = 10);SELECT "D"."DNAME" "DNAME", "E"."EMPNO" "EMPNO", "E"."ENAME" "ENAME"  FROM "SCOTT"."DEPT" "T", "SCOTT"."EMP" "E", "SCOTT"."DEPT" "D" WHERE "T"."DEPTNO" = 10   AND "D"."DEPTNO" = "T"."DEPTNO"   AND "E"."DEPTNO" = 10   AND "D"."DEPTNO" = 10;--第二类要查出必须存在一个部门编号为10的员工的员工编码及姓名--四种写法--第一种select e.empno, e.ename  from emp e, dept d where e.deptno = d.deptno   and d.deptno = 10;SELECT "E"."EMPNO" "EMPNO", "E"."ENAME" "ENAME"  FROM "SCOTT"."EMP" "E", "SCOTT"."DEPT" "D" WHERE "E"."DEPTNO" = 10   AND "D"."DEPTNO" = 10;--第二种select e.empno, e.ename  from emp e, (select * from dept t where t.deptno = 10) d where e.deptno = d.deptno;SELECT "E"."EMPNO" "EMPNO", "E"."ENAME" "ENAME"  FROM "SCOTT"."EMP" "E", "SCOTT"."DEPT" "T" WHERE "E"."DEPTNO" = 10   AND "T"."DEPTNO" = 10;--第三种select e.empno, e.ename  from emp e, (select t.deptno from dept t where t.deptno = 10) d where e.deptno = d.deptno;SELECT "E"."EMPNO" "EMPNO", "E"."ENAME" "ENAME"  FROM "SCOTT"."EMP" "E", "SCOTT"."DEPT" "T" WHERE "E"."DEPTNO" = 10   AND "T"."DEPTNO" = 10;--第四种select e.empno, e.ename  from emp e where e.deptno in (select d.deptno from dept d where d.deptno = 10);SELECT "E"."EMPNO" "EMPNO", "E"."ENAME" "ENAME"  FROM "SCOTT"."DEPT" "D", "SCOTT"."EMP" "E" WHERE "D"."DEPTNO" = 10   AND "E"."DEPTNO" = 10;--第五种select e.empno, e.ename  from emp e where exists (select 1          from dept d         where e.deptno = d.deptno           and d.deptno = 10);SELECT "E"."EMPNO" "EMPNO", "E"."ENAME" "ENAME"  FROM "SCOTT"."DEPT" "D", "SCOTT"."EMP" "E" WHERE "D"."DEPTNO" = 10   AND "E"."DEPTNO" = 10   AND "E"."DEPTNO" = "D"."DEPTNO";--第一类SQL结论：1，2，3三条SQL效率一样、4，5两条效率相对较低，4最低。--第二类SQL结论：1，2，3，4四条SQL效率一样、5效率最高。