(3/14) oracle查询优化的改写 笔记 第一章 操作多个表

select * from table(dbms_xplan.display);

--union all与空字符串
select empno as 编码, ename as 名称,nvl(mgr, deptno) as 上级编码
from emp 
where empno=7788
union all
select deptno as 编码, dname as 名称, null as 上级编码
from dept 
where deptno =10;

select sysdate from dual where '' is null;

-- union 去重 union all不去重
select empno ,ename from emp where empno =7788
union 
select empno,ename from emp where ename='SCOTT';

select empno ,ename from emp where empno =7788
union all
select empno,ename from emp where ename='SCOTT';

create index idx_emp_empno on emp(empno);
create index idx_emp_ename on emp(ename);

alter session set "_b_tree_bitmap_plans"=false;

--执行计划
explain plan for 
select empno, ename from emp where empno=7788 or ename='SCOTT';

select * from table(dbms_xplan.display);

--执行计划
explain plan for 
select empno,ename from emp where empno=7788
union
select empno,ename from emp where ename='SCOTT';

select * from table(dbms_xplan.display);

-- 看是建主键还是建索引  如果两个都是索引 or走全表扫描  union 走range扫描
select * from all_indexes where owner='SCOTT';
select * from all_tables where owner='SCOTT';

-- union去重  的执行计划
explain plan for 
select deptno from emp where mgr=7698
union
select deptno from emp where job='SALESMAN';

select * from table(dbms_xplan.display);

-- union all 的执行计划
explain plan for 
select deptno from emp where mgr=7698
union all
select deptno from emp where job='SALESMAN';

select * from table(dbms_xplan.display);

-- union如果去重 不是想要的结果，加唯一标识
select empno,deptno from emp where mgr=7698
union
select empno,deptno from emp where job='SALESMAN';

-- 3.4 组合相关的行
select e.empno, e.ename, d.dname, d.loc 
from emp e
inner join dept d on (e.deptno=d.deptno)
where e.deptno=10;

drop table emp2 purge;
create table emp2 as
select ename, job, sal, comm from emp where job='CLERK';

-- has join semi
explain plan for select empno, ename, job, sal, deptno from emp 
where (ename, job, sal) in (select ename, job, sal from emp2);

select * from table(dbms_xplan.display());

-- has join semi
explain plan for select empno, ename, job, sal ,deptno 
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);

-- hash join
explain plan for select a.empno, a.ename, a.job, a.sal ,a.deptno 
from emp a
inner join emp2 b 
on(
b.ename=a.ename
and b.job=a.job
and b.sal=a.sal);

-- 3.6 inner join \ left join \ right join \ full join 
drop table L purge;
drop table R purge;
create table L as 
select 'left_1' as str, '1' as val from dual union all 
select 'left_2' as str, '2' as val from dual union all 
select 'left_3' as str, '3' as val from dual union all 
select 'left_4' as str, '4' as val from dual ;

create table R as 
select 'left_3' as str, '3' as val,1 as status from dual union all 
select 'left_4' as str, '4' as val,0 as status from dual union all 
select 'left_5' as str, '5' as val,0 as status from dual union all 
select 'left_6' as str, '6' as val,0 as status from dual ;

-- join的写法
select l.str as left_str, r.str as right_str from l 
inner join r on l.val=r.val 
order by 1,2;

select l.str as left_str, r.str as right_str from l 
left join r on l.val=r.val 
order by 1,2;

select l.str as left_str, r.str as right_str from l 
right join r on l.val=r.val 
order by 1,2;

select l.str as left_str, r.str as right_str from l 
full join r on l.val=r.val 
order by 1,2;

-- 加 +的写法
select l.str as left_str, r.str as right_str from l , r 
where  l.val=r.val 
order by 1,2;

select l.str as left_str, r.str as right_str from l , r 
where  l.val=r.val(+)
order by 1,2;

explain plan for 
select l.str as left_str, r.str as right_str from l , r 
where  l.val(+)=r.val 
and r.status(+)=1
order by 1,2;

-- 3.8 自关联  可以理解从不同的数据集取数据
select e.empno , e.ename, e.job, e.mgr, e2.empno, e2.ename 
from emp e 
left join emp e2
on (e.mgr=e2.empno)
order by 1;

select count(*) from emp where deptno=40;

explain plan for
select * 
from dept 
where deptno not in (select e.deptno from emp e where e.deptno is not null );

explain plan for select dept.* from dept left join emp on emp.deptno=dept.deptno where emp.deptno  is null;

select * from table(dbms_xplan.display);


-- 3.10 检测两个表中的数据及对应数据的条数是否相同，这个表多加了scott这个用户
create or replace view v3_10 as 
select * from emp where deptno!=10
union all
select * from emp wHere ename='SCOTT';

select rownum, empno, ename from v3_10 where ename ='SCOTT';

select rownum, empno, ename from emp where ename='SCOTT';

--比较两个数据集不同的时候用full join
select v.empno, v.ename, b.empno, b.ename from V3_10 v full join emp b 
on (b.empno =v.empno)
where (v.empno is null or b.empno is null);

select v.empno, v.ename, v.cnt, emp.empno, emp.ename, emp.cnt from 
(select empno, ename, count(*) as cnt from v3_10 group by empno, ename ) v 
full join (select empno, ename, count(*) as cnt from emp group by empno, ename) emp 
on (emp.empno=v.empno and emp.cnt=v.cnt)
where (v.empno is null or emp.empno is null);

--  3.11 聚集与内连接  先求出奖金的金额 （聚集），然后让奖金和工资关联，奖金有两条，先关联，工资就会求出两次
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-2-15',3);
insert into emp_bonus values(7782,Date '2005-2-15',1);

select e.deptno, e.empno, e.ename, e.sal,
(e.sal * case 
	when eb.type=1 then 0.1
	when eb.type=2 then 0.2
	when eb.type=3 then 0.3
	end) as bonus 
from emp e 
inner join emp_bonus eb on (e.empno = eb.empno)
where e.deptno=10 
order by 1,2;

select e.deptno,
	sum(e.sal) as total_sal,
sum(e.sal * case 
	when eb.type=1 then 0.1
	when eb.type=2 then 0.2
	when eb.type=3 then 0.3
	end) as bonus 
from emp e 
inner join emp_bonus eb on (e.empno = eb.empno)
where e.deptno=10
group by e.deptno;

-- 先将奖金率算出来
select eb.empno,
	sum( case 
			 when eb.type=1 then  0.1
			 when eb.type=2 then  0.2
			 when eb.type=3 then  0.3
			 end) as rate 
  from emp_bonus eb 
	group by empno 
	order by 1,2;
	
-- 然后将奖金和奖金率关联起来
select e.deptno, sum(e.sal) as total_sal,
	sum(e.sal * eb2.rate) as total_bonus
from emp e
inner join (select eb.empno,
		sum( case 
				 when eb.type=1 then  0.1
				 when eb.type=2 then  0.2
				 when eb.type=3 then  0.3
				 end) as rate 
		from emp_bonus eb 
		group by empno 
		order by 1,2
	) eb2 on eb2.empno = e.empno 
where e.deptno=10
group by e.deptno ;