Shulou(Shulou.com)06/01 Report--

-- scalar quantum query

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 a piece of data

Insert into emp (empno,deptno) values (999999)-return 15 records of the result

-- changed to 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 e.empno d) * / e.empno, e.ename, e.sal, e.deptnored.dname

From emp e

Left join dept d

On (e.deptno = d.deptno)

/ * Note: the modified plan usually has the word "outer". If not, pay attention to whether to correct the error. , /

-- optimizing aggregate rewriting of scalar quantum queries with 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

-- incorrect writing

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

-- the original quantum query is rewritten as:

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

From hr.employees em

Group by em.department_id

-- the inline view of the rewritten Leftist League

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

The result of the following rewriting has changed

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

From scott.emp t1

Left join dept2 b

On t1.deptno = b.deptno

-- correct rewriting

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)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.