SQL 튜닝비법 - 서브쿼리와 함수

  1. 서브 쿼리란? 조인의 또 다른 형태로 하나의 SQL 내에 존재하는 또 다른 SELECT 문을 의미한다.
  2. 서브 쿼리의 위치는 SELECT, FROM, WHERE, ORDER BY 절에서 모두 사용가능하나 GROUP BY 절에서는 사용할 수가 없다.

  3. 메인 쿼리와 서브 쿼리의 관계 : 메인 쿼리는 가장 바깥쪽에서 동작되는 쿼리문이고, 서브 쿼리는 SELECT, FROM, WHERE, ORDER BY 절 내에서 동작되는 쿼리문이다. 서브 쿼리 종류의 따라서 메인 쿼리와 서브 쿼리의 실행 순서가 결정된다.
    1. 예시
      메인 쿼리와 서브 쿼리
      -- 메인 쿼리
      SELECT empno, ename
      FROM emp
      WHERE deptno IN (
      -- 서브 쿼리
      SELECT deptno
      FROM dept
      WHERE dname = 'DEV')
      ;
  4. 서브 쿼리의 종류
    1. NESTED 서브 쿼리 : WHERE 절에서 동작되는 서브 쿼리로 메인 쿼리와의 연결고리가 없이 동작되는 서브 쿼리를 의미한다.
      1. 예시
        NESTED 서브 쿼리
        -- 메인 쿼리
        SELECT empno, ename
        FROM emp
        WHERE deptno = (
        -- NESTED 서브 쿼리
        SELECT deptno
        FROM dept
        WHERE dname = 'DEV')
        ;
      2. 그림
        상황에 따라서 메인 쿼리가 먼저 실행될 수도 있다.
    2. CORRELATED 서브 쿼리 : WHERE 절에서 동작되는 서브 쿼리로 메인 쿼리와의 연결고리가 있어 동작 시에 메인 쿼리보다 나중에 실행되게 되는 서브 쿼리로 메인 쿼리에서 정보를 받아와야 동작이 가능한 서브 쿼리를 의미한다.
      1. 예시
        CORRELATED 서브 쿼리
        -- 메인 쿼리
        SELECT empno, ename
        FROM emp
        WHERE EXISTS (
        -- CORRELATED 서브 쿼리
        SELECT 'X'
        FROM dept
        WHERE dept.deptno = emp.deptno 
        AND dept.dname = 'SALES')
        ;
      2. 그림

    3. Inline View : FROM 절 내에 있는 서브 쿼리를 의미한다.
      1. 예시
        Inline View
        SELECT empno,
                     deptno
        FROM ( -- Inline View
                      SELECT empno,
                                  deptno
                       FROM emp
                     WHERE ename = 'SMITH')
        WHERE empno = '1234'
        ;
    4. SCALAR 서브 쿼리 : SELECT, WHERE, ORDER BY 절 모두에서 사용하며, 단 하나의 데이터와 단 하나의 컬럼을 가져와야하는 서브 쿼리를 의미하며, 함수의 성질을 갖는다.
      1. 예시
        SCLAR 서브 쿼리
        -- 메인 쿼리
        SELECT e.ename,
                     (-- SCALAR 서브 쿼리
                      SELECT d.dname
                        FROM dept d
                      WHERE d.deptno = e.deptno) as dname
                      -- 1 Row, 1 Column
        FROM emp e
        ;

        -- 함수로 생성하여 사용
        CREATE OR REPLACE FUNCTION f_dmn(a_dno IN dept.deptno%TYPE)
        RETURN VARCHAR2
        RESULT_CACHE
        RELIES_ON(dept)
        AS
          h_dnm dept.dname%type := NULL;
        BEGIN
          SELETE dname INTO h_dnm
            FROM dept
          WHERE deptno = a_dno;

          RETURN h_dnm;
        END;
        /

        SELECT ename,
                     f_dnm(deptno) as dname
        FROM emp
        ;
      2. 그림
  5. 함수
    1. 자주 사용하는 함수
      1. 합계 출력하는 함수로 GROUP BY 절에서 사용된다.
        1. ROLLUP() : 괄호 내에 컬럼을 기준으로 합계를 구하는 함수이다.
        2. CUBE() : 괄호 내에 컬럼을 기준으로 합계와 소계를 모두 구하는 함수이다.
        3. GROUPING SETS() : 괄호 내에 컬럼을 기준으로 합계와 소계를 모두 구하는 함수로 ROLLUP(), CUBE() 함수의 기능을 모두 사용 가능하다.
      2. 분석 함수 (Analytical Function)
        1. 구문 형식은 아래와 같다.
          Analytical Function 형식
          SELECT Anlytical_Function (arguments) OVER ([Partition By 컬럼] [Order By 절] [Windowing 절])
          FROM 테이블 명
          WHERE 조건
        2. 구문 형식에 대한 설명은 아래와 같다.
          1. Arguments : 함수에 따라서 0~3개의 인자가 지정된다.
          2. Partition By 절 : 전체 집합을 기준에 의해 소그룹으로 나눈다.
          3. Order By 절 : 어떤 항목에 대한 정렬 기준을 기술한다.
          4. Windowing 절 : 함수에 의해서 제어하고자 하는 데이터 범위를 정의하며, 해당 절에 아무것도 없는 경우 데이터 범위는 전체를 의미한다.
            1. Row를 사용한 범위 선정 방법

            2. Range를 사용한 범위 선정 방법

          5. 함수 종류
            1. ROW_NUMBER() : 순서대로 Row 수를 나타낸다.
            2. RANK() : 순서대로 순위를 매기는 함수로 동일 데이터에 대해서 동일한 순위를 매기며, 그 이후는 떨어진만큼의 순서를 나타낸다. ( 1, 1, 1, 4)
            3. DENSE_RANK() : 순서대로 순위를 매기는 함수로 동일 데이터에 대해서 동일한 순위를 매기며 그 이후는 순서대로 진행한다. (1, 1, 1, 2)
            4. SUM() : 테이블에 대해서 기준별로 누계를 구한다.
            5. RATIO_TO_REPORT() : Partition By 절만 존재하며 기준에 대한 백분율을 구한다.

      참고도서 : 실전사례로 살펴보는 SQL 튜닝비법