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

- 日理万妓 2023-02-19 11:26 9阅读 0赞

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 ;