--標量子查詢

select e.empno, e.ename, e.sal, e.deptno,

(select d.dname from dept d where e.deptno = d.deptno)as dname

from emp e

--插入一條數據

insert into emp(empno,deptno) values(9999,null)--返回結果15條記錄

--改成left join(hash outer)

select e.empno, e.ename, e.sal, e.deptno,d.dname

from emp e

left join dept d

on (e.deptno = d.deptno)

--NL outer

select /*+ use_nl(e,d) */e.empno, e.ename, e.sal, e.deptno,d.dname

from emp e

left join dept d

on (e.deptno = d.deptno)

/*Note:修改后plan一般有outer字樣，如果沒有，注意是否改錯。*/

--用left join 優化標量子查詢之聚合改寫

select dp.department_id, dp.department_name, dp.location_id,

nvl((select sum(em.salary)

from hr.employees em

where em.department_id = dp.department_id),

0) as sum_dept_salary

from hr.departments dp

--錯誤寫法

select dp.department_id, dp.department_name, dp.location_id,

nvl(sum(em.salary), 0) as sum_sal

from hr.departments dp

left join hr.employees em

on dp.department_id = em.department_id

--原標量子查詢改寫為：

select em.department_id, sum(em.salary) as sum_sal

from hr.employees em

group by em.department_id

--左聯改寫后的內聯視圖

select dp.department_id, dp.department_name, dp.location_id,

nvl(sum(e.sum_sal), 0) as sum_sal

from hr.departments dp

left join (select e.department_id, sum(e.salary) as sum_sal

from hr.employees e

group by e.department_id) e

on (dp.department_id = e.department_id)

group by dp.department_id, dp.department_name, dp.location_id

--

create table dept2 as select * from scott.dept;

insert into dept2? select * from scott.dept where deptno=10

select t1.job, t1.deptno,

(select distinct dname from dept2 b where b.deptno = t1.deptno) as dname

from scott.emp t1

order by 1, 2, 3

--以下改寫結果變了

select distinct t1.job, b.deptno, b.dname

from scott.emp t1

left join dept2 b

on t1.deptno = b.deptno

--正確改寫

select t1.job, t1.deptno, f.dname

from scott.emp t1

left join (select b.deptno, b.dname

from dept2 b

group by b.deptno, b.dname) f

on (f.deptno = t1.deptno)