SQL 튜닝 - 튜닝 활용

1. CASE 함수

IF-THEN-ELSE 논리와 유사한 방식으로 표현식을 해독한다.

 

* 일반 프로그램

IF SAL > 2000
	THEN REVISED_SALARY = SAL
    ELSE REVIESD_SALARY = 2000
END IF

 

* CASE 문

SELECT JOB,
	CASE WHEN SAL>2000
    	THEN SAL
        ELSE 2000
    END as REVISED_SALARY
FROM EMP;

 

2. SUM(DECODE)

모델링의 제1정규화로 인해 반복되는 칼럼의 경우 구분 칼럼을 두고 여러 개의 레코드로 만들어진 집합을 정해진 칼럼 수만큼 확장해서 사용하는 기법

 

* 부서별로 월별 입사자의 평균 급여 구하기

SELECT DEPTNO, 
    AVG(DECODE(MONTH, 1, SAL)) M01,
    AVG(DECODE(MONTH, 2, SAL)) M02,
    AVG(DECODE(MONTH, 3, SAL)) M03,
    AVG(DECODE(MONTH, 4, SAL)) M04,
    AVG(DECODE(MONTH, 5, SAL)) M05,
    AVG(DECODE(MONTH, 6, SAL)) M06,
    AVG(DECODE(MONTH, 7, SAL)) M07,
    AVG(DECODE(MONTH, 8, SAL)) M08,
    AVG(DECODE(MONTH, 9, SAL)) M09,
    AVG(DECODE(MONTH, 10, SAL)) M10,
    AVG(DECODE(MONTH, 11, SAL)) M11,
    AVG(DECODE(MONTH, 12, SAL)) M12
FROM (SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM hiredate) MONTH, SAL FROM EMP)
GROUP BY DEPTNO
ORDER BY DEPTNO;

 

3. Cartesian Product

- 모든 가능한 행들의 조합이 출력된다.

- 첫 번째 테이블의 모든 행이 두 번째 테이블의 모든 행과 조인된다.

- Cartesian Product에 의한 추출된 결과를 살펴보면, 각 테이블의 데이터가 상대방 테이블에 있는 데이터 건만큼 각각 읽혀져 있음을 알 수 있음 = 데이터 복제 -> 반복된 처리로 인한 물리적인 I/O를 개선할 수 있음

 

* 자주 사용하는 방법

- 다른 SQL 상에서 사용하고 있는 테이블과 ROWNUM 조건 활용

- DUAL 활용

- 임시성 테이블(COPY_T, IMSI_T, DUMMY_T) 활용

 

Q. 다음 쿼리를 Cartesian Product를 활용해 변환할 것

SELECT '직군별' CLASS, JOB, COUNT(*)
FROM EMP
GROUP BY JOB
UNION ALL
SELECT '부서별' CLASS, TO_CHAR(DEPTNO) DEPTNO, COUNT(*)
FROM EMP
GROUP BY DEPTNO
UNION ALL
SELECT '총인원' CLASS, NULL, COUNT(*)
FROM EMP;

1) 다른 SQL 상에서 사용하고 있는 테이블과 ROWNUM 조건 활용

SELECT DECODE(RN, 1, '직군별', 2, '부서별', '총인원') CLASS,
        DECODE(RN, 1, JOB, 2, DEPTNO, NULL) JOB, SUM(CNT)
FROM   (SELECT JOB, TO_CHAR(DEPTNO) DEPTNO, COUNT(*) CNT
        FROM EMP
        GROUP BY JOB, DEPTNO),
        (SELECT ROWNUM RN
        FROM DEPT
        WHERE ROWNUM <=3)
GROUP BY RN, DECODE(RN, 1, '직군별', 2, '부서별', '총인원'),
        DECODE(RN, 1, JOB, 2, DEPTNO, NULL)
ORDER BY CLASS;

2) DUAL 활용

SELECT DECODE(RN, 1, '직군별', 2, '부서별', '총인원') CLASS,
        DECODE(RN, 1, JOB, 2, DEPTNO, NULL) JOB, SUM(CNT)
FROM   (SELECT JOB, TO_CHAR(DEPTNO) DEPTNO, COUNT(*) CNT
        FROM EMP
        GROUP BY JOB, DEPTNO),
        (SELECT 1 RN
        FROM DUAL
        UNION ALL
        SELECT 2 RN
        FROM DUAL
        UNION ALL
        SELECT 3 RN
        FROM DUAL
        )
GROUP BY RN, DECODE(RN, 1, '직군별', 2, '부서별', '총인원'),
        DECODE(RN, 1, JOB, 2, DEPTNO, NULL)
ORDER BY CLASS;

3) 임시성 테이블 활용

SELECT DECODE(RN, 1, '직군별', 2, '부서별', '총인원') CLASS,
        DECODE(RN, 1, JOB, 2, DEPTNO, NULL) JOB, SUM(CNT)
FROM   (SELECT JOB, TO_CHAR(DEPTNO) DEPTNO, COUNT(*) CNT
        FROM EMP
        GROUP BY JOB, DEPTNO),
        (SELECT COL1 RN
        FROM DUMMY_T
        WHERE COL1 <=3
        )
GROUP BY RN, DECODE(RN, 1, '직군별', 2, '부서별', '총인원'),
        DECODE(RN, 1, JOB, 2, DEPTNO, NULL)
ORDER BY CLASS;

4. Rollup과 Cube 함수

계층적 분류를 포함혹 있는 데이터의 집계에 적합한 함수

 

* ROLLUP

SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP(DNAME, JOB);

부서별 인원과 급여 소계, 직군별 인원과 급여 소계, 총인원과 총급여액

* CUBE

SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY CUBE(DNAME, JOB);

조합 가능한 모든 경우의 수

5. Grouping Sets

(예시)

SELECT DNAME, JOB, MGR, SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY GROUPING SETS((DNAME, JOB, MGR),(DNAME, JOB), (JOB, MGR))
ORDER BY DNAME;

6. Analytical FUNCTION

* 그룹 내 순위 관련: ROW_NUMBER, RANK, DENSE_RANK

부서별로 급여가 높은 사람 순으로 순위 구하는 sql

SELECT DEPTNO, ENAME, SAL,
    ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS RK1,
    RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS RK2,
    DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS RK3
FROM EMP;

- ROW_NUMBER: 단순 행번호

- RANK: 실생활에서 순위 매기는 방식

- DENSE_RANK: 실생활에서 순위 매기는 방식X 순위

* 일반 그룹 함수 관련: AVG, SUM, MAX, MIN, COUNT

 

* 그룹 내 데이터 순서 관련: FIRST_VALUE, LAST_VALUE, LAG, LEAD

 

* 그룹 내 비율 관련: RATIO_TO_REPORT(해당 칼럼 백분율), PERCENT_RANK, CUME_DIST, NTILE

7. Merge

* 선언

MERGE INTO 실행할 테이블(넣을 테이블)

USING 수정 혹은 업데이트의 내용(가져올 테이블)

ON (두 테이블의 조인 조건) --꼭 괄호가 쳐져 있어야 한다.

WHEN MATCHED THEN
	맞을 경우 실행할 쿼리문
    
WHEN NOT MATCHED THEN
	틀릴 경우 실행할 쿼리문

(예시)

MERGE INTO EMP E
USING DEPT D
ON (E.DEPTNO = D.DEPTNO)
WHEN MATCHED THEN
    UPDATE SET E.JOB = 'ANALYST'
WHEN NOT MATCHED THEN
    INSERT (DEPTNO, ENAME, JOB, EMPNO)
    VALUES(90, 'HONG GILDONG', 'ADMIN', 10);

모든 직업이 ANALYST로 변경된 것을 볼 수 있다.