Oracle查询优化-多表查询
来源：互联网 发布：剑三捏脸数据成女网盘 编辑：程序博客网 时间：2024/06/05 20:00
--合并结果集--1.union allSELECT * FROM emp WHERE empno=7369UNION ALL--单纯合并SELECT * FROM emp WHERE empno=7369;--2.union SELECT * FROM emp WHERE empno=7369UNION --将重复结果集合并SELECT * FROM emp WHERE empno=7369;--------------使用命令窗口执行，查看union与or的执行计划SQL> alter session set "_b_tree_bitmap_plans" = FALSE;SQL> explain plan for select empno,ename from emp where empno =7788 or ename='SCOTT'; SQL> select * from table(dbms_xplan.display); SQL> explain plan for select empno,ename from emp where empno=7788 UNION select empno,ename from emp where ename = 'SCOTT'; SQL> select * from table(dbms_xplan.display);--可见union会使用索引 --union会强制将结果集进行合并，从而使数据出现错误SQL> select empno,deptno from emp where mgr=7698 order by 1;EMPNO DEPTNO----- ------ 7499 30 7521 30 7654 30 7844 30 7900 30 SQL> select empno,deptno from emp where job='SALESMAN' order by 1; EMPNO DEPTNO----- ------ 7499 30 7521 30 7654 30 7844 30--从上面两条语句看出有4条结果重复，看看union和or的区别 SQL> select deptno from emp where mgr=7698 or job='SALESMAN'; DEPTNO------ 30 30 30 30 30 SQL> select deptno from emp where mgr=7698 2 union 3 select deptno from emp where job='SALESMAN'; DEPTNO------ 30/************************************************************************************************************************************** * 结论： * 1.不仅两个数据集间重复的数据会被去重，而且单个数据集里重复的数据也会被去重； * 2.有重复数据的数据集用union后得到的数据与预期会不一致； * 3.可以这样，select deptno from (select empno,deptno from emp where mgr=7698 union select empno,deptno from emp where job='SALESMAN') * 或者将empno改为rowid也行。 **************************************************************************************************************************************/--4.[inner] join与=SELECT e.empno,e.ename,d.dname,d.locFROM emp eINNER JOIN dept d ON (e.deptno=d.deptno)WHERE e.deptno=10;SELECT e.empno,e.ename,d.dname,d.locFROM emp e,dept dWHERE e.deptno=d.deptnoAND e.deptno=10;--以上两句结果相同，但join是SQL-92标准，可以清晰反应表与表之间的关联关系，推荐使用join--5.in、exists和inner joinCREATE TABLE emp2 AS--创建相关tableSELECT ename,job,sal,comm FROM emp WHERE job = 'CLERK';--6.要求查询与emp2相匹配的emp中的数据，可以使用in、exists或inner joinSELECT * FROM emp WHERE (ename,job,sal) IN (SELECT ename,job,sal FROM emp2);SELECT * FROM emp a WHERE EXISTS (SELECT NULL FROM emp2 b WHERE b.ename=a.ename AND b.job=a.job AND b.sal=a.sal);SELECT * FROM emp aINNER JOIN emp2 b ON (b.ename=a.ename AND b.job=a.job AND b.sal=a.sal);/******************************************************************************************************** * 结论： * 1.以上三条语句可以返回相同条数的结果集； * 2.通过查看plan可以看出，exists和in都使用了HASH JOIN SEMI（哈希半连接）而inner join使用了哈希连接； * 3.exists和in的执行效率是一样的，如果不确定，可以通过查看plan来判断，不要死记硬背。 ********************************************************************************************************/--7.内、左、右、外连接--建立测试表--左表DROP TABLE L;CREATE TABLE L AS SELECT 'L_1' AS str,'1' AS v FROM dual UNION ALLSELECT 'L_2','2' AS v FROM dual UNION ALLSELECT 'L_3','3' AS v FROM dual UNION ALLSELECT 'L_4','4' AS v FROM dual;--右表DROP TABLE R;CREATE TABLE R AS SELECT 'R_3' AS str,'3' AS v,1 AS STATUS FROM dual UNION ALLSELECT 'R_4','4' AS v,0 AS STATUS FROM dual UNION ALLSELECT 'R_5','5' AS v,0 AS STATUS FROM dual UNION ALLSELECT 'R_6','6' AS v,0 AS STATUS FROM dual;--INNER JOINSELECT l.str AS l_str,r.str AS r_str FROM l INNER JOIN r ON l.v=r.vORDER BY 1,2;SELECT l.str AS l_str,r.str AS r_str FROM l,rWHERE l.v=r.vORDER BY 1,2;--LEFT JOINSELECT l.str AS l_str,r.str AS r_str FROM l LEFT JOIN r ON l.v=r.vORDER BY 1,2;SELECT l.str AS l_str,r.str AS r_str FROM l,rWHERE l.v=r.v(+)ORDER BY 1,2;--RIGHT JOINSELECT l.str AS l_str,r.str AS r_str FROM l RIGHT JOIN r ON l.v=r.vORDER BY 1,2;SELECT l.str AS l_str,r.str AS r_str FROM l,rWHERE l.v(+)=r.vORDER BY 1,2;--FULL JOINSELECT l.str AS l_str,r.str AS r_str FROM l FULL JOIN r ON l.v=r.vORDER BY 1,2;--8.自连接SELECT a.empno,a.ename,b.ename mgr,a.deptno FROM emp a,emp b WHERE a.mgr=b.empno(+) ORDER BY a.empno; --9.NOT IN,NOT EXISTS和LEFT JOINSELECT * FROM DEPT WHERE DEPTNO NOT IN(SELECT DEPTNO FROM EMP WHERE EMP.DEPTNO IS NOT NULL);SELECT * FROM DEPT WHERE NOT EXISTS (SELECT NULL FROM EMP WHERE EMP.DEPTNO = DEPT.DEPTNO);SELECT DEPT.* FROM DEPT LEFT JOIN EMP ON EMP.DEPTNO=DEPT.DEPTNO WHERE EMP.DEPTNO IS NULL;--10.外连接中的条件 --沿用7的测试表，查询左表所有内容，使用V关联右表，但只显示右表中STATUS为1的值，期望结果如下：STR STR--- ---L_1 L_2 L_3 R_3L_4 --错误写法：SELECT L.STR,R.STR FROM L LEFT JOIN R ON(L.V=R.V) WHERE R.STATUS=1 ORDER BY 1;PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 688663707----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 27 | 8 (25)| 00:00:01 || 1 | SORT ORDER BY | | 1 | 27 | 8 (25)| 00:00:01 ||* 2 | HASH JOIN | | 1 | 27 | 7 (15)| 00:00:01 ||* 3 | TABLE ACCESS FULL| R | 1 | 21 | 3 (0)| 00:00:01 || 4 | TABLE ACCESS FULL| L | 4 | 24 | 3 (0)| 00:00:01 |----------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("L"."V"="R"."V") 3 - filter("R"."STATUS"=1)Note----- PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------- - dynamic sampling used for this statement 21 rows selected--正确写法：SELECT L.STR,R.STR FROM L LEFT JOIN R ON (L.V=R.V AND R.STATUS=1) ORDER BY 1;PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 2310059642----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 4 | 108 | 8 (25)| 00:00:01 || 1 | SORT ORDER BY | | 4 | 108 | 8 (25)| 00:00:01 ||* 2 | HASH JOIN OUTER | | 4 | 108 | 7 (15)| 00:00:01 || 3 | TABLE ACCESS FULL| L | 4 | 24 | 3 (0)| 00:00:01 ||* 4 | TABLE ACCESS FULL| R | 1 | 21 | 3 (0)| 00:00:01 |----------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("L"."V"="R"."V"(+)) 4 - filter("R"."STATUS"(+)=1)Note----- PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------- - dynamic sampling used for this statement 21 rows selected/*外层条件不要随便加，会影响结果集*/--11.检测两个表中的数据及对应数据的条数是否相同CREATE OR REPLACE VIEW V ASSELECT * FROM EMP WHERE DEPTNO != 10UNION ALLSELECT * FROM EMP WHERE ENAME='SCOTT';--要求查处视图V与表EMP中不同的数据（注意：视图中'SCOTT'有两行数据，而EMP表中只有一条数据）--分析：除了EMP表中DEPTNO为10的数据，还有ENAME为SCOTT的数据与V中不一致，因为EMP表中只有一条而V中有两条SELECT *FROM (SELECT EMPNO,ENAME,COUNT(*) AS CNT FROM V GROUP BY EMPNO,ENAME) V --加一列统计条数用以区分不同FULL JOIN (SELECT EMPNO,ENAME,COUNT(*) AS CNT FROM EMP GROUP BY EMPNO,ENAME) EON V.EMPNO=E.EMPNO AND V.CNT=E.CNTWHERE V.EMPNO IS NULL OR E.EMPNO IS NULL; EMPNO ENAME CNT EMPNO ENAME CNT---------- ---------- ---------- ----- ---------- ---------- 7788 SCOTT 2 7782 CLARK 1 7839 KING 1 7788 SCOTT 1 7934 MILLER 1--12.聚集与内连接CREATE TABLE EMP_BONUS (EMPNO INT,RECEIVED DATE,TYPE INT);INSERT INTO EMP_BONUS VALUES (7934,DATE '2005-5-17',1);INSERT INTO EMP_BONUS VALUES (7934,DATE '2005-2-15',2);INSERT INTO EMP_BONUS VALUES (7839,DATE '2005-5-17',3);INSERT INTO EMP_BONUS VALUES (7782,DATE '2005-5-17',1);--要求返回上述员工的工资及奖金，奖金根据TYPE来定，TYPE为1，奖金为10%...SELECT E.DEPTNO,E.EMPNO,E.ENAME,E.SAL,B.TYPE*0.1*E.SAL AS BONUSFROM EMP E,EMP_BONUS BWHERE E.EMPNO(+)=B.EMPNO;DEPTNO EMPNO ENAME SAL BONUS------ ----- ---------- --------- ---------- 10 7934 MILLER 1300.00 130 10 7934 MILLER 1300.00 260 10 7839 KING 5000.00 1500 10 7782 CLARK 2450.00 245--若此时想要计算6月份部门为10的总工资 --错误做法： SELECT E.DEPTNO,SUM(E.SAL) TOTAL_SAL,SUM(B.TYPE*0.1*E.SAL) AS TOTAL_BONUS FROM EMP E,EMP_BONUS B WHERE E.EMPNO(+)=B.EMPNO GROUP BY E.DEPTNO; DEPTNO TOTAL_SAL TOTAL_BONUS ------ ---------- ----------- 10 10050 2135 --原因，MILLER的工资和奖金都重复计算了，因为他5月份已经降职了，所以有两条不同的TYPE对应他的相关信息！ --正确做法： SELECT E.DEPTNO,SUM(E.SAL) TOTAL_SAL,SUM(B.TYPE*0.1*E.SAL) AS TOTAL_BONUS FROM EMP E INNER JOIN (SELECT EB.EMPNO,EB.TYPE,EB.RECEIVED FROM EMP_BONUS EB INNER JOIN (SELECT MAX(C.EMPNO) EMPNO,MAX(C.RECEIVED) RECEIVED FROM EMP_BONUS C GROUP BY C.EMPNO) EBG --先统计出员工最新的职位变更时间 ON EB.EMPNO=EBG.EMPNO AND EB.RECEIVED=EBG.RECEIVED) B ON E.EMPNO=B.EMPNO GROUP BY E.DEPTNO; DEPTNO TOTAL_SAL TOTAL_BONUS------ ---------- ----------- 10 8750 1875 --13.聚集与外连接 SELECT E.DEPTNO,SUM(E.SAL) TOTAL_SAL,SUM(B.TYPE*0.1*E.SAL) AS TOTAL_BONUS FROM EMP E LEFT JOIN --只改此处为LEFT JOIN即可 (SELECT EB.EMPNO,EB.TYPE,EB.RECEIVED FROM EMP_BONUS EB INNER JOIN (SELECT MAX(C.EMPNO) EMPNO,MAX(C.RECEIVED) RECEIVED FROM EMP_BONUS C GROUP BY C.EMPNO) EBG --先统计出员工最新的职位变更时间 ON EB.EMPNO=EBG.EMPNO AND EB.RECEIVED=EBG.RECEIVED) B ON E.EMPNO=B.EMPNO GROUP BY E.DEPTNO ORDER BY 1; DEPTNO TOTAL_SAL TOTAL_BONUS------ ---------- ----------- 10 8750 1875 20 10875 30 9400 --14.多表查询时的空值处理 --要求返回比ALLEN提成低的员工 --错误做法：SELECT E.ENAME,E.COMMFROM EMP EWHERE E.COMM<(SELECT COMM FROM EMP WHERE ENAME='ALLEN');ENAME COMM---------- ---------TURNER 0.00 --错误原因：有些员工的COMM一项为NULL，但并未返回 --正确做法：SELECT E.ENAME,E.COMMFROM EMP EWHERE COALESCE(E.COMM,0)<(SELECT COMM FROM EMP WHERE ENAME='ALLEN'); ENAME COMM---------- ---------SMITH JONES BLAKE CLARK SCOTT KING TURNER 0.00ADAMS JAMES FORD MILLER
0 0
- Oracle查询优化-多表查询
- ORACLE多表查询优化
- ORACLE多表查询优化
- ORACLE多表查询优化
- ORACLE多表查询优化
- ORACLE多表查询优化
- ORACLE多表查询优化
- ORACLE多表查询优化
- ORACLE多表查询优化
- ORACLE多表查询优化
- ORACLE多表查询优化
- ORACLE多表查询优化
- oracle多表查询优化
- ORACLE多表查询优化
- ORACLE多表查询优化
- ORACLE多表查询优化
- ORACLE多表查询优化
- Oracle 多表查询优化
- Oracle查询优化--排序
- Maven-使用及常见问题
- 蚯蚓
- Git-常用命令
- Js_闭包详解
- Oracle查询优化-多表查询
- Spring-MVC案例：Spitter的笔记
- Android事件分发之View（一）
- WPF 通过行为将事件绑定到命令
- Oracle-Merge用法
- 【NOIP2016提高组】蚯蚓
- java之socket的OOBInline和UrgentData和发送心跳包研究
- SpringMVC配置
- ubuntu下如何修改端口号
