LEADING ヒントの練習

表を作成して、leading ヒントを付与した場合の挙動を確認。

create table dept (deptno number(2), dname varchar2(14), loc varchar2(13));
create table emp (empno number(4) primary key, ename varchar2(10), job varchar2(9), mgr number(4), hiredate date, sal number(7,2), comm number(7,2), deptno number(2));

insert into dept values (10,'ACCOUNTING','NEW YORK');
insert into dept values (20,'RESEARCH','DALLAS');
insert into dept values (30,'SALES','CHICAGO');
insert into dept values (40,'OPERATIONS','BOSTON');

insert into emp values( 7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'), 5000, null, 10 ):
insert into emp values( 7698, 'BLAKE', 'MANAGER', 7839, to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 30 );
insert into emp values( 7782, 'CLARK', 'MANAGER', 7839, to_date('9-6-1981','dd-mm-yyyy'), 2450, null, 10 );
insert into emp values( 7566, 'JONES', 'MANAGER', 7839, to_date('2-4-1981','dd-mm-yyyy'), 2975, null, 20 );
insert into emp values( 7788, 'SCOTT', 'ANALYST', 7566, to_date('13-JUL-87','dd-mm-rr') - 85, 3000, null, 20 );
insert into emp values( 7902, 'FORD', 'ANALYST', 7566, to_date('3-12-1981','dd-mm-yyyy'), 3000, null, 20 );
insert into emp values( 7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980','dd-mm-yyyy'), 800, null, 20 );
insert into emp values( 7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-2-1981','dd-mm-yyyy'), 1600, 300, 30 );
insert into emp values( 7521, 'WARD', 'SALESMAN', 7698, to_date('22-2-1981','dd-mm-yyyy'), 1250, 500, 30 );
insert into emp values( 7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-9-1981','dd-mm-yyyy'), 1250, 1400, 30 );
insert into emp values( 7844, 'TURNER', 'SALESMAN', 7698, to_date('8-9-1981','dd-mm-yyyy'), 1500, 0, 30 );
insert into emp values( 7876, 'ADAMS', 'CLERK', 7788, to_date('13-JUL-87', 'dd-mm-rr') - 51, 1100, null, 20 );
insert into emp values( 7900, 'JAMES', 'CLERK', 7698, to_date('3-12-1981','dd-mm-yyyy'), 950, null, 30 );
insert into emp values( 7934, 'MILLER', 'CLERK', 7782, to_date('23-1-1982','dd-mm-yyyy'), 1300, null, 10 );
commit;

create table retired (empno number(4), retire_date date);
insert into retired values( 7902, to_date('3-12-2020','dd-mm-yyyy'));
insert into retired values( 7369, to_date('17-12-2019','dd-mm-yyyy'));

select e.empno, e.ename,d.loc , RETIRE_DATE from emp e , dept d, retired r
where e.empno = r.empno(+) and d.deptno = e.deptno;


select /*+ leading(e r d) */ e.empno, e.ename,d.loc , RETIRE_DATE from emp e , dept d, retired r where e.empno = r.empno(+) and d.deptno = e.deptno;
select /*+ leading(e d r) */ e.empno, e.ename,d.loc , RETIRE_DATE from emp e , dept d, retired r where e.empno = r.empno(+) and d.deptno = e.deptno;
select /*+ leading(d e r) */ e.empno, e.ename,d.loc , RETIRE_DATE from emp e , dept d, retired r where e.empno = r.empno(+) and d.deptno = e.deptno;

select /*+ leading(d r e) */ e.empno, e.ename,d.loc , RETIRE_DATE from emp e , dept d, retired r where e.empno = r.empno(+) and d.deptno = e.deptno;
---> join する条件がないので、無視される。

select /*+ leading(r e d) */ e.empno, e.ename,d.loc , RETIRE_DATE from emp e , dept d, retired r where e.empno = r.empno(+) and d.deptno = e.deptno;
---> outer join なので、r は内部表になる必要がある。無視される。

select /*+ leading(r d e) */ e.empno, e.ename,d.loc , RETIRE_DATE from emp e , dept d, retired r where e.empno = r.empno(+) and d.deptno = e.deptno;
---> join する条件がないので、無視される。

select /*+ leading(r e d) */ e.empno, e.ename,d.loc , RETIRE_DATE from emp e , dept d, retired r where e.empno = r.empno and d.deptno = e.deptno;
---> 外部結合を外すと、r -> e -> d で結合できる。