#34_140730_TUNING_SORTING OPERATION

# SORTING OPERATION

• sort를 일으키는 SQL

1.     order by

- 인덱스를 이용해서 sort를 회피

2.     그룹함수 : max, min, avg, sum

- 인덱스를 이용해서 sort를 피함

3.     집합 연산자 : minus, union, intersect

- union all 을 사용 또는 SQL 변경를 사용해서 sort를 피함

4.     조인방법 : ort merge join

- 다른 조인방법으로 유도하여 sort를 피함

5.     인덱스 생성시 : create index ….

- 신속하게 인덱스를 생성되게 하여 sort를 피함

문제31. 사원이름과 월급을 출력하는데 월급이 낮은 사원부터 높은 사원 순으로 출력하시오

# 튜닝 전

SELECT ename, sal FROM EMP ORDER BY sal asc;

# 튜닝 후

SELECT /*+ index_asc(emp emp_sal) */ ename, sal FROM EMP WHERE sal >= 0; - index asc는 인덱스를 ascending 하게 읽어라

l index는 수행방법을 알려주는 것이고 필수적으로 where 절에 조건을 걸어줘야 한다

l index asc where 절의 조건 검색

- 숫자 >= 0

- 문자 > ‘  ‘

- 날짜 < to_data(‘9999/12/31’,’RRRR/MM/DD’)

문제32. 월급이 1000 이상인 사원의 이름과 월급을 출력하는데 월급이 낮은 사원부터 높은 사원순으로 출력하시오

# 튜닝 전

SELECT ename, sal FROM EMP WHERE sal >= 1000 ORDER BY sal ASC;

# 튜닝 후

SELECT /* index_asc(emp emp_sal) */ ename, sal FROM EMP WHERE sal >= 1000;

문제34. 1981년도에 입사한 사원들의 이름과 입사일을 출력하는데 최근에 입사한 사원부터 출력하시오

CREATE INDEX emp_hire ON EMP(hiredate);

# 튜닝 전

SELECT ename, hiredate FROM EMP WHERE TO_CHAR(hiredate,'RRRR')='1981' ORDER BY hiredate desc;                                                 - 여기서 주의할 점은 index column을 가공하면index access가 안된다 - 결국 full table scan을 하게 된다

# 튜닝 후

SELECT /*+ index_desc(emp emp_hire) */ ename, hiredate FROM EMP WHERE hiredate BETWEEN TO_DATE('1981/01/01','RRRR/MM/DD')  AND TO_DATE('1981/12/31','RRRR/MM/DD');

문제35. 직업에 인덱스를 생성하고 아래의 SQL을 튜닝하시오

CREATE INDEX EMP_JOB ON EMP(job);

# 튜닝 전
select ename, job, sal from emp Where substr(job,1,5)='SALES'; - full table scan	db block gets        0 consistent gets       46 physical reads        0
# 튜닝 후
SELECT ename, job, sal FROM EMP WHERE job LIKE 'SALES%'; - index scan	db block gets        0 consistent gets       41 physical reads        0

문제36. 아래의 SQL을 튜닝하시오

# 튜닝 전
select ename, job, sal*12 as salary from emp where sal*12 = 36000; - full table scan	db block gets        0 consistent gets       46 physical reads        0
# 튜닝 후
select ename, job, sal*12 as salary from emp where sal = 36000/12; - index scan	db block gets        0 consistent gets       41 physical reads        0

• Group 함수를 사용했을 때 sort 작업에 대한 튜닝 예제

# 튜닝 전
select max(sal) from emp; - AGGREGATE: Single row from group function	db block gets        0 consistent gets       1 physical reads        0
# 튜닝 후
SELECT /*+ index_desc(emp emp_sal) */ sal FROM EMP WHERE sal >= 0 AND rownum=1;	db block gets        0 consistent gets       40 physical reads        0

문제38. 최대월급을 받는 사원의 이름과 월급을 출력하시오

# 튜닝 전
SELECT ename, sal FROM EMP WHERE sal = (SELECT MAX(sal) FROM emp);	db block gets        0 consistent gets       42 physical reads        0
# 튜닝 후
SELECT /*+ index_desc(emp emp_sal) */ ename, sal FROM EMP WHERE sal >= 0 AND rownum=1; - rownum=1; ~> rownum<=1; - 위처럼 = 말고 <= 로 고치면 20배이상 빨라진다(ROWNUM의 경우에만 해당됨)	db block gets        0 consistent gets       40 physical reads        0

문제39. 부서번호, 부서번호별 최대월급을 출력하는데 20번 부서번호만 출력하시오

# 튜닝 전
SELECT deptno, MAX(sal) FROM EMP WHERE deptno = 20 GROUP BY deptno;;	db block gets        0 consistent gets       50 physical reads        0
# 튜닝 후
SELECT /*+ index_desc(emp emp_deptno_sal) */ deptno, sal FROM EMP WHERE deptno = 20 AND ROWNUM <= 1; - 20번 부서번호의 월급들 중에서 ascending으로 정렬된 데이터들의 거꾸로 읽어들이는데 그중에 첫번째 데이터를 읽어들인다	db block gets        0 consistent gets       50 physical reads        0