oracle连接查询SQL性能测试

hzyblroirr

--以下测试均在删除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效率最高。