1、表联结与子查询,一.联结类型1.相等联结 最常见联结,使用相等条件匹配一张表和另一张表的记录.2.不等联结联结指定一个不等联结条件,如between 等.,3.交叉联结联结中不指定任何条件,获得笛卡尔积.4.外联结一张表与另外一张表中的记录不相匹配的记录也包括其中.包括LEFT,RIGHT,FULL三种.5.自连接联结左右两端的表为同一张表6.层级连接一种特殊的自连接,为了获得表中的父子关系,或生成一个树.7.反联结,一张表的记录并未与其他表的记录相匹配,则返回这些记录.8.半联结一张表中的和其他表中的记录相匹配的记录被返回,然而外表只返回一行记录.,二.联结方法1.嵌套循环在嵌套循环联结中,
2、oralce会对第一张表(外表)中发现的每一行记录对第二个表(内表)执行一次搜索.,2.散列联结当执行散列联结时,oracle会对两张表中的一张表构建散列表.通常,在散列联结和排序合并联结都可以使用的条件下,散列联结比排序合并联结更加高效,而且在一张表的大部分数据被访问的情况下,散列联结也比嵌套循环联结更高效.,3.排序合并联结排序合并联结适用于需要包含访问表中的大部分数据或内表没有索引访问可用的联结.在考虑排序合并联结的条件下,散列联结通常更高效.然而,排序合并联结可以被用于无法使用散列联结的地方(不等联结).,4.联结算法的比较和选择1).当联结涉及的记录只占表中相对小的比例并且索引支持的
3、时候,嵌套循环联结方法较为适合.而当表的大部分数据参与联结或没有合适的索引时,排序合并和散列联结则更合适.2).散列联结在结果集不需要排序的情况下通常是比排序合并更优的选择,但是散列联结不能用于不等联结.,避免联结1.反范式化Everything is key;在一张表中维护另一张表的反范式化的数据.select e.empno,e.ename,d.dname from emp e ,dept d where e.deptno=d.deptno;可以在员工表上维护dname字段来避免联结Alter table emp add (dname varchar(14);-添加新列Update emp
4、 set dname=(select dname from dept where emp.depno=dept.depno);-将数据更新,建立对应的触发器,以同步更新以后的数据(触发器会带来dml的额外开销,11g之后引入虚拟列,可以减少这部分开销,但是虚拟列主要是使用当前表字段的处理) create or replace trigger trg_emp_dname before insert or update of deptno on emp for each rowbegin if :new.deptno is not null then select dname into :new.
5、dname from dept where deptno = :new.deptno; end if;end; /,2.物化视图将物化视图和查询重写联合起来可以用来避免联结For example:create materialized view mv_emp_dept refresh complete enable query rewrite as select e.empno,e.ename,d.dname from dept d ,emp e where e.deptno=d.deptno;查询新的执行计划.,Execution Plan-Plan hash value: 63258075
6、7-| Id | Operation | Name | Rows | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 | 1 | SORT AGGREGATE | | 1 | | | 2 | MAT_VIEW REWRITE ACCESS FULL| MV_EMP_DEPT | 15 | 3 (0)| 00:00:01 |-,位图联结索引和索引聚簇以上两种方法,使用场景局限性较大,不做过多讨论.,三.联结顺序1.多表联结当联结3张表以上的表时,就会发生多次联结操作.每次联结都会产生一个临时结果集,然后这个结果
7、集再与后续的表进行联结.减少这些中间临时结果集的大小是降低总的开销的一个重要步骤.,2.如何手动确定最优的联结(顺序)1).驱动表应该是一张具有高度选择性并且具有高效的where子句条件的表.2).尽可能早地在联结顺序中将最终结果集中不需要的记录排除出去.3).要联结涉及表的较小的结果集,如果有索引支持的话,尝试对每个后续的联结都使用嵌套循环联结.否则,优先使用散列联结而不是排序合并联结.4)确保嵌套循环联结使用的索引包含where子句的所有字段与联结字段.,四.子查询1.简单子查询Select * from a whereVal = (select max(val) froma);因为每个子
8、查询都是单独执行的,所以可以对它们进行单独优化.,2.相关子查询Select empno,sal from emp a where sal = (select min(sal) from emp b where b.deptno=a.deptno);优化方式1:转换为联结select a.empno,a.sal from emp a ,(select deptno,min(sal) min_sal from emp group by deptno) b where a.sal = b.min_sal and a.deptno=b.deptno;优化方式2:使用分析函数with tmp_emp
9、as (select e.*,min(sal) over(partition by deptno)as dept_min_sal from emp e)select empno,sal from tmp_emp where sal=dept_min_sal;,层级查询查询部门id为10的员工号为7369的上级.select empno,mgr,deptno from emp (where deptno=10) connect by prior mgr=empno start with empno=7369 ;-票据的上级机构查询-以及bln_brh_info表应该包含brh_class字段.层
10、级查询的优化思路,为考虑在prior列建立索引.,4.反/半联结子查询反联结Select * from xxxx where xxxx.xxx not in (select xxx from yyyy where .)Select * from xxxx where not exists (select xxx from yyyy where .)半联结Select * from xxxx where xxxx.xxx in (select xxx from yyyy where .)Select * from xxxx where exists (select xxx from yyyy w
11、here .),半联结的优化参考普通子查询即可.而反联结子查询则注意not in 后的子查询列要定义为非空,否则使用not exists;,五.扩展1.代码重构消除表联结Select* from t1,t2 where t1.id=t2.id;伪代码如下:(嵌套循环算法)Open cursor for t1:Foreach row in t1;(fetch one)DoSelect * from t2 where t2.id = T1.row_n.id;Do something more;Done;,同样可以用散列和排序算法来消除消除合并,但是我们多数只是使用到嵌套循环即可.2.中间表/结果集转存1),分析现有的程序(核心为日终,其他非实时类交易亦可,虽然部分实时交易亦可在考虑范围,但是考虑到生产的稳定性,不建议作为实际操作的选择范围).针对那些日常报表/交易需要的联结查询的中间结果集新增中间表保存.2),新增批量,将一些联结报表转换为日终报表存储到本地文件系统,或日结型报表作为数据库记录存放在数据库中.其中可以对多个批量/报表使用的中间结果集进行额外的分类及分层级处理,
