■기타 sql 튜닝 방법
문제81. 아래의 sql 을 분석함수를 이용하지 않은 sql로 작성하시오!
(오늘의 마지막 문제)
select empno, ename, sal, sum(sal) over(order by empno)누적치
from emp;
>>>>>>>>>>>>>
select e.empno, ename, sal,
(select sum(Sal)
from emp
where empno between (select min(empno)
from emp)
and e.empno)누적치
from emp e
order by e.empno asc;
**inline view 로는 안되는건가**
select e.empno, ename, sal, 누적치
from emp e,
(select empno, sum(sal)누적치
from emp
group by empno) ee
where e.empno = ee.empno
and
ee.empno between (select min(empno)
from emp)
and e.empno
order by e.empno;
■ 기타 튜닝 방법 ---> sql 재작성 방법
조인순서의 중요성, 조인 힌트, 인덱스 관련 힌트들을 알면
sql 튜닝을 할 수는 있는데
1. 로지컬 옵티마이져를 제어하는 힌트
2. SQL 재작성
■ 1. 로지컬 옵티마이저를 제어하는 힌트
SQL
↓
Query Transformer(로지컬 옵티마이저) -> sql 변경
↓
힌트-> 옵티마이져 <-테이블 분석정보(통계정보)
↓
실행계획
↓
실행
■Query Transformer(로지컬 옵티마이져)를 제어하는 힌트
1. no_merge, merge
*no_merge : view 나 inline view 를 해체하지 말아라
NO_MERGE는 메인쿼리와 인라인뷰가 합쳐지는 것 즉 병합을 하지말고 인라인 뷰 먼저 실행 하라는 것입니다.
* merge: view 나 inline view를 해체해라
2. no_unnest, unnest
*no_unnest : 서브쿼리로 수행해라
* unnest: 서브쿼리를 조인문으로 수행해라
3. expand_gset_union : grouping sets 을 union으로 변경해라
4. no_query_transformation : 로지컬 옵티마이져에게 쿼리 변경하지 마라
문제100.
이름, 부서위치, 월급을 출력하는 view 를 생성하시오
(view 이름은 emp100)
create view emp100
as
select e.ename, d.loc, e.sal
from emp e, dept d
where e.deptno = d.deptno;
문제101. emp100 과 salgrade 를 조인해서 이름,월급, 부서위치, 급여등급을 출력하시오
select e.ename, e.sal, e.loc, s.grade
from emp100 e, salgrade s
where e.sal between s.losal and hisal;
실행계획에 emp100 이 없다.
로지컬 옵티마이져(쿼리 트랜스포머)가 emp100을 해체해서 sql을 바꿔버렸다.
select e.ename, e.sal, e.loc, s.grade
from emp100 e, salgrade s
where e.sal between s.losal and hisal;
↓
Query transformer 가 view 를 해체하고
↓
select e.ename, e.sal, d.loc, s.grade
from emp e, dept d, salgrade s
where e.deptno = d.deptno
and
e.sal between s.losal and s.hisal;
그럼 힌트를 줘보면
select /*+ leading(s e) use_nl(e) */
e.ename, e.sal, e.loc, s.grade
from emp100 e, salgrade s
where e.sal between s.losal and hisal;
안바뀐다.
SQL
↓
Query Transformer(로지컬 옵티마이저) -> sql 변경
↓
힌트-> 옵티마이져 <-테이블 분석정보(통계정보)
↓
실행계획
↓
실행
Query Transformer 가 view 를 해체하는 바람에
/*+ leading(s e) use_nl(e) */ 힌트가
아무런 영향을 미치지 못하고 있다.
로지컬 옵티마이져가 해체한 뷰를 옵티마이져가 실행한다.
즉, 로지컬 옵티마이져를 조정해야 한다.
문제102.
아래의 sql 의 힌트가 작동되도록 새로운 힌트를 주시오
select /*+ leading(s e) use_nl(e) */
e.ename, e.sal, e.loc, s.grade
from emp100 e, salgrade s
where e.sal between s.losal and hisal;
1. no_merge, merge
*no_merge : view 나 inline view 를 해체하지 말아라
* merge: view 나 inline view를 해체해라
select /*+ no_merge(e1) leading(s e1) use_nl(e1) */
e1.ename, e1.sal, e1.loc, s.grade
from emp100 e1, salgrade s
where e1.sal between s.losal and hisal;
emp100의 view 를 해체 안하고 통째로 드라이빙 한걸
볼 수 있다.
**e1 이라고 알리어스 주는 이유는 emp 랑 헷갈리지 말라는....
문제103.
위의 실행계획에서 view emp100안의 emp와 dept의 조인순서를 변경하시오
select /*+ no_merge(e1) leading(s e1) use_nl(e1)
leading(e1.e e1.d) use_nl(e1.d) */
e1.ename, e1.sal, e1.loc, s.grade
from emp100 e1, salgrade s
where e1.sal between s.losal and hisal;
dept --> emp 에서
emp --> dept 로 바뀐걸 확인할 수 있다.
문제104.
위의 sql 로 아래의 실행계획이 나오게 하시오
select /*+ leading(e1 s) use_merge(s)
no_merge(e1) leading(e1.e e1.d) use_hash(e1.d) */
e1.ename, e1.sal, e1.loc, s.grade
from emp100 e1, salgrade s
where e1.sal between s.losal and hisal;
문제105.
위의 sql 의 emp100 을 in line view 로 풀어서 작성하시오
select e1.ename, e1.sal, e1.loc, s.grade
from (select e.ename, e.sal, d.loc
from emp e, dept d
where e.deptno = d.deptno) e1, salgrade s
where e1.sal between s.losal and hisal;
서브쿼리로 묶었음에도 불구하고(in line view)
로지컬옵티마이져가 in line view 를 해체했다.
문제106.
위의 sql 의 in line view 를 해체하지 못하도록
힌트를 작성하시오
select e1.ename, e1.sal, e1.loc, s.grade
from (select /*+ no_merge */
e.ename, e.sal, d.loc
from emp e, dept d
where e.deptno = d.deptno) e1, salgrade s
where e1.sal between s.losal and hisal;
subquery = in line view =>> view
no_merge : 서브쿼리 안의 emp, dept 를 해체하지 마라
해체 안하면 더 빠르다. 검색속도 향상
문제107.
이름에 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%' ;
실제 실행 계획을 보면
select /*+ gather_plan_statistics */
ename, sal, job, deptno
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) */
rowid rn
from emp
where ename like '%EN%'
or
ename like '%IN%' ) v
where e.rowid = v.rn;
로지컬옵티마이저가 서브 해체함.
select e.ename, e.sal, e.job, e.deptno
from emp e,
(select /*+ no_merge index_ffs(emp emp_ename) */
rowid rn
from emp
where ename like '%EN%'
or
ename like '%IN%' ) v
where e.rowid = v.rn;
근데 이런 작은값은 해시타게 하지 말고 nested loop ㄱㄱ
select /*+ leading(v e) use_nl(e) */
e.ename, e.sal, e.job, e.deptno
from emp e,
(select /*+ no_merge index_ffs(emp emp_ename) */
rowid rn
from emp
where ename like '%EN%'
or
ename like '%IN%' ) v
where e.rowid = v.rn;
서브먼저 실행되게 해야 한다(그게 빠른 튜닝)
그래서 outer 쿼리에 use_nl 먹여줌.
문제108.
사원번호, 이름, 월급, 사원 테이블의 최대월급,
사원 테이블의 최소월급,
사원 테이블의 토탈월급,
사원 테이블의 평균월급을 출력하시오
(분석함수 쓰지 마시오)
튜닝전:
select empno, ename, sal,
(select max(sal) from emp)최대월급,
(select min(sal) from emp)최소월급,
(select sum(sal) from emp)토탈월급,
(select round(avg(sal)) from emp)평균월급
from emp;
emp테이블을 5번 access 하고 있어서 악성!!
튜닝후:
RPAD 방법
select empno, ename, sal,
substr(total,1,10) 최대,
substr(total,11,10) 최소,
substr(total,21,10) 토탈,
substr(total,31,10) 평균
from ( select empno, ename, sal ,
( select
rpad(max(sal),10,' ') ||
rpad(min(sal),10,' ') ||
rpad(sum(sal),10,' ') ||
rpad(round(avg(sal)),10,' ')
from emp ) as total
from emp
) ;
↓
여기에 no_merge
select empno, ename, sal,
substr(total,1,10) 최대,
substr(total,11,10) 최소,
substr(total,21,10) 토탈,
substr(total,31,10) 평균
from ( select /*+ no_merge */ empno, ename, sal ,
( select
rpad(max(sal),10,' ') ||
rpad(min(sal),10,' ') ||
rpad(sum(sal),10,' ') ||
rpad(round(avg(sal)),10,' ')
from emp ) as total
from emp
) ;
*no_merge 를 안 쓰면 in line view를 해체해서
튜닝전으로 출력한다.
지금 11g는 업그레이드 되서 알아서 잘 뽑지만
옛날버전은 해체해서 뽑았다.
※no_merge 힌트를 사용해야 in line view 를 해체하지 않고
emp 테이블을 2번만 access 해서 결과를 출력할 수 있다.
LENGTH 방법
select e.empno, e.ename, e.sal,
substr(ee.줄줄이, 1, lm)최대월급,
substr(ee.줄줄이, lm+1, lmi)최소월급,
substr(ee.줄줄이, lm+lmi+1, ls)토탈월급,
substr(ee.줄줄이, lm+lmi+ls+1, las)평균월급
from emp e,
(select max(sal)||min(sal)||sum(sal)||round(avg(sal))줄줄이,
length(max(sal)) lm,
length(min(sal)) lmi,
length(sum(sal)) ls,
length(round(avg(sal)) )las
from emp) ee ;
select empno, ename, sal,
(select max(sal), min(Sal), sum(sal), round(avg(sal))
from emp)
from emp;
에러뜸.
※스칼라 서브쿼리의 특징?
스칼라 서브쿼리는 딱 한개의 결과만 리턴할 수 있다.
select empno, ename, sal,
(select max(sal)||min(Sal)||sum(sal)||round(avg(sal))
from emp)
from emp;
스트링으로 묶으면 한개값.
문제109.(점심시간 문제)
이름,주소,나이,전공, 우리반 최대 나이,
우리반 최소 나이,
우리반 토탈 나이,
우리반 평균 나이를 출력하시오
select ename, address, age, major,
substr(total,1,10) 최대,
substr(total,11,10) 최소,
substr(total,21,10) 토탈,
substr(total,31,10) 평균
from ( select /*+ no_merge */
ename, address, age, major,
( select
rpad(max(age),10,' ') ||
rpad(min(age),10,' ') ||
rpad(sum(age),10,' ') ||
rpad(round(avg(age)),10,' ')
from emp2 ) as total
from emp2
) ;
■ 7.기타 튜닝 방법 ---> sql 재작성 방법
조인순서의 중요성, 조인 힌트, 인덱스 관련 힌트들을 알면
sql 튜닝을 할 수는 있는데
1. 로지컬 옵티마이져를 제어하는 힌트
no_merge, merge
no_unnest, unnest
expand_gset_to_union
no_query_transformation : 쿼리 변환하지 마시오.
2. SQL 재작성
■SQL 재작성
분석함수를 이용하지 않은 sql ----> 분석함수를 이용한 sql
분석함수를 이용하지 않은 sql<---- 분석함수를 이용한 sql
문제110.
아래의 sql 을 분석함수를 사용하지 않는 sql로 변경하시오
튜닝후:
select empno, ename, sal,
sum(sal) over(order by sal asc)누적치
from emp;
sal 이 중복값이 있어서 잘못 된 값 나옴.
중복값 없는 empno 를 기준삼아야 한다.
↓
select empno, ename, sal,
sum(sal) over(order by sal, empno asc)누적치
from emp;
sal을 낮은순서부터 나오게 하고 싶다
↓
with emp_sal as (select rownum as rn, e.*
from (select empno, ename, sal
from emp
order by sal) e)
select e1.empno, e1.ename, e1.sal,
(select sum(sal)
from emp_sal e2
where e2.rn <= e1.rn) 누적치
from emp_sal e1;
튜닝전:
select e.empno, ename, sal,
(select sum(Sal)
from emp
where e.empno >= empno
) 누적치
from emp e
order by empno asc;
문제111.
아래의 분석함수를 이용한 sql 을 분석함수를 이용하지 않은
sql 로 변경하시오
튜닝후: select deptno, empno, ename, sal,
sum(sal) over(partition by deptno
order by empno) 누적치
from emp;
튜닝전:
select e.deptno, e.empno, ename, sal,
(select sum(sal)
from emp
where e.deptno = deptno and
e.empno >= empno
)누적치
from emp e
order by deptno, empno asc;
문제112.(sql 마지막 문제)
아래의 sql 을 분석함수를 이용하지 않은 sql로 변경하시오
튜닝후: select empno, ename, sal,
lag(sal, 1) over (order by sal )이전행
from emp;
튜닝전:
with 절
with emp_sal as (select rownum as rn, e.*
from (select empno, ename, sal
from emp
order by sal) e)
SELECT e1.empno, e1.ename, e1.sal,
(SELECT MAX(e.sal)
FROM emp_sal e
WHERE e.rn < e1.rn) 이전행
FROM emp_sal e1
ORDER BY 3;
반장답_많은 서브쿼리
select empno, ename, sal,
(select sal
from (select rownum rn, e3.*
from (select *
from emp e
order by sal) e3) e4
where e4.rn+1 = e2.rn) 이전행
from (select rownum rn, e1.*
from (select * from emp e order by sal) e1) e2;
'sql_tuning' 카테고리의 다른 글
| Section2. 최적 저장 구조를 위한 고려사항과 Indexing 및 Full Scan의미 (0) | 2023.10.29 |
|---|---|
| Section2. 오라클 아키텍처 개요 (0) | 2023.10.29 |
| 6. 병렬처리 (0) | 2019.04.02 |
| 5. 파티셔닝 (0) | 2019.04.02 |
| 4. 서브쿼리 튜닝 (0) | 2019.04.02 |
