#37_140805_TUNING_VIEW JOIN

# VIEW JOIN

문제93. 이름과 월급과 부서위치를 출력하는 view를 생성하고 이 view를 salgrade 테이블을 조인해서 이름과 월급과 부서위치와 급여등급(grade)를 출력하시오

CREATE VIEW emp5000

AS

SELECT e.ename, e.sal, d.loc

FROM EMP e, DEPT d

WHERE e.deptno=d.deptno;

SELECT /*+ no_merge(v) */ e.ename, e.sal, e.loc, s.grade

FROM EMP5000 e, SALGRADE s

WHERE sal BETWEEN losal AND hisal;

- view를 해체하지 않도록 no_merge 힌트 사용

- 실행계획에 view가 나오지 않는다면 view를 해체하지 않는 것이다

- 그러나 아래와 같은 실행계획은 view를 해체하고 변형기가 SQL을 다시 만들어서 실행계획을 세운다

문제94. 아래와 같은 실행계획이 나오게 하시오

SELECT /*+ no_merge(v) leading(v s) use_nl(s) */ v.ename, v.sal, v.loc, s.grade

FROM EMP5000 v, SALGRADE s

WHERE sal BETWEEN losal AND hisal;

문제95. 아래와 같은 실행계획이 나오도록 하시오(p9-28, global 힌트 사용)

SELECT /*+ no_merge(v) leading(v s) leading(v.e v.d) use_hash(v.d) use_nl(s) */ v.ename, v.sal, v.loc, s.grade

FROM EMP5000 v, SALGRADE s

WHERE sal BETWEEN losal AND hisal;

문제96. 이름에 index를 걸고 en 또는 in이 포함되어 있는 사원들의 이름과 월급과 직업과 부서번호를 출력하고 index를 이용하여 수행하도록 튜닝하시오

CREATE INDEX emp_ename ON EMP(ename);

SELECT ename, sal, job, deptno

FROM EMP

WHERE ename LIKE '%EN%' OR ename LIKE '%IN%';

- 이름 외에도 sal, job, deptno 정보를 함께 조회하기 때문에 full scan을 한다

방법1. create index emp_ename on emp(ename);

위에 생성한 인덱스에서 이름에 EN 또는 IN이 포함되어 있는 이름과 rowid를 인덱스에서 읽어오시오

SELECT ename, ROWID

FROM EMP

WHERE ename LIKE '%EN%' OR ename LIKE '%IN%';

방법2. 위의 실행계획을 index fast full scan이 되게 하시오

SELECT /*+ index_ffs(emp emp_name) */ ename, ROWID

FROM EMP

WHERE ename LIKE '%EN%' OR ename LIKE '%IN%';

- 만약에 index fast full scan을 하지 않는다면 not null 제약이 안걸려 있기 때문이다

- SQL> ALTER table EMP

MODIFY ename NOT NULL;

방법3. 위에서 찾은 rowid 3개로 테이블을 엑세스 해서 이름과 월급과 부서번호 직업을 출력하시오

SELECT ename, sal, job, deptno FROM EMP WHERE rowid IN (SELECT /*+ index_ffs(emp emp_ename) */ ROWID    FROM EMP    WHERE ename LIKE '%EN%' OR ename LIKE '%IN%'); - 위처럼 수행하면 쿼리를 2번 쓰기 때문에 조인을 이용해야 한다 - 위의 결과는 아래와 같다

SELECT /*+ index(emp emp_ename) */ ename, ROWID FROM EMP WHERE ename LIKE '%EN%' OR ename LIKE '%IN%';

SELECT e.ename, e.sal, e.job, e.deptno FROM EMP e, (SELECT /*+ index_ffs(emp emp_ename) */ ename, ROWID rn FROM EMP             WHERE ename LIKE '%EN%' OR ename LIKE '%IN%') v WHERE e.rowid=v.rn; - 조인문장을 썼지만 full table scan을 하는 것을 확인할 수 있다

방법4. in line view를 이용해서 최종 튜닝

SELECT /*+ no_merge(v) */ e.ename, e.sal, e.job, e.deptno FROM EMP e, (SELECT /*+ index_ffs(emp emp_ename) */ ename, ROWID rn FROM EMP             WHERE ename LIKE '%EN%' OR ename LIKE '%IN%') v WHERE e.rowid=v.rn; - in line view를 이용해서 join을 수행한다 - 아래의 통계정보를 보면 원래 41개의 buffer가 발생하지만 11개로 줄은 것을 확인할 수 있다    db block gets        0 consistent gets       11 physical reads        0