[Sql튜닝] [oracle][MySql] 임시로 생성된 가상테이블 WITH절과 UNION ALL의 조합

Posted Sep 28, 2024

By leewooyup 9 min read

Oracle9, WITH절

🐀 WITH절은 이름이 부여된 서브쿼리이다 (서브쿼리 정의)

WITH절 서브쿼리의 결과를 임시로 생성된 가상테이블로 사용할 수 있다.
이렇게 함으로써 중복쿼리를 줄일 수 있다.
즉, 반복적으로 사용하는 쿼리를 WITH절로 감싸고 QUERY BLOCK 이름을 부여하여 재사용할 수 있다.

🪭 WITH절 안에서 다른 WITH절을 참조할 수 있다.

또한, WITH절을 사용하면 중첩쿼리를 풀 수 있도 있다.
⚠️ WITH절로 쿼리를 나누어 작성하면 각 부분을 독립적으로 디버깅하기 쉽다.

  
-- 중첩쿼리
SELECT e.department_id
     , e.employee_name
     , e.salary
  FROM employees e
 WHERE e.salary > (SELECT AVG(salary)
                     FROM employees
                    WHERE department_id = e.department_id)

-- WITH절 사용
WITH DepartmentAvg AS
(
  SELECT department_id
       , AVG(salary) as avg_salary
    FROM employees
   GROUP BY department_id
)
SELECT e.department_id
     , e.employee_name
     , e.salary
  FROM employee e
  JOIN DepartmentAvg d
    ON e.department_id = d.department_id
 WHERE e.salary > d.avg_salary

뷰와 쓰임새가 비슷하지만 뷰와 다른 점은, VIEW는 한번 만들어 놓으면 DROP할 때까지 없어지지 않는다.
하지만, WITH절은 쿼리문에서만 실행된다.

또한, WITH절이 자주 실행되는 경우 한번만 파싱되고, PLAN계획이 수립되므로 쿼리의 성능향상에 도움이 된다.
즉, WITH절을 여러번 참조하는 쿼리를 만들수록 효과가 증대된다.

  
WITH TABLE_NAME(COLUM1, COLUM2, ...) AS
(
    -- SUB_QUERY
)

  
WITH EX AS
(
    SELECT *
    FROM PRODUCTS
    WHERE STANDARD_COST > 1000
)
SELECT * FROM EX WHERE EX.CATEGORY_ID = '1'
UNION ALL
SELECT * FROM EX WHERE EX.CATEGORY_ID = '2'
UNION ALL
SELECT * FROM EX WHERE EX.CATEGORY_ID = '3'

🌩️ Oracle 11g R2 ver.까지는 WITH절에 SELECT 구문을 반드시 사용해야 한다.

WITH절 동작방식

🥯 Materialize / Inline

Materialize 방식
🔅 오라클 공유 메모리에 GLOBAL_임시테이블 생성
임시테이블을 생성 후, WITH절 결과를 저장하며,
반복호출 시, 쿼리를 실행하지 않고 임시테이블에 저장된 결과를 사용한다.
🌩️ 임시테이블을 Create, Drop하는 행위를 반복하기 때문에, 🐖 자주 수행되는 쿼리문에 사용하면 시스템부하가 생길 수 있다.
Inline 방식
🔅 임시테이블을 생성하지 않고, 메인쿼리에서 바로 사용하는 방식
참조된 횟수만큼 반복적으로 쿼리를 실행한다.
그러므로 호출하는 쿼리에서 딱 한번만 실행하는 경우 유리하다…

  
WITH EX2 AS
(
    SELECT /*+ materialize */ *
      FROM PRODUCTS
      WHERE STANDARD_COST > 1000
)
SELECT * FROM EX2 WHERE EX2.CATEGORY_ID = '1'
UNION ALL
SELECT * FROM EX2 WHERE EX2.CATEGORY_ID = '2'
UNION ALL
SELECT * FROM EX2 WHERE EX2.CATEGORY_ID = '3'

⚠️ Oracle 11g부터 직접 힌트를 사용하여 동작방식을 바꿀 수 있다.

UNION / UNION ALL

🥯 UNION / UNION ALL

두개의 SELECT문을 서로 합치고 싶은데, 중복데이터를 한번만 출력하고 싶다면 UNION을 사용한다.
결과값을 자동으로 정렬한 후 반환한다.
단, SELECT문의 컬럼명, 컬럼위치, 컬럼수가 동일해야 한다..
그렇지 않으면 🐖 ORA-01789: 질의 블록은 부정확한 수의 결과 열을 가지고 있습니다. 라는 에러메세지를 출력한다.
컬럼명이 같지 않다면 alias를 사용하여 억지로라도 같게 만들어줘야 한다.

서로 합치고 싶으나, 중복데이터를 모두 조회하고 싶다면 UNION ALL을 사용한다.
자동정렬을 안되니 정렬을 해줘야하는데, 마지막에 연결한 SELECT문에만 ORDER BY(정렬) 가능하다.
그렇지 않으면 🐖 ORA-00933: SQL 명령어가 올바르게 종료되지 않았습니다. 라는 에러메세지를 출력한다.

🍪 JOIN / UNION
JOIN은 키를 기준으로 테이블을 연결하여, 컬럼을 확장하여 보여주고
UNION은 다른 테이블의 ROW를 합쳐서 보여준다.

예제(MySql): WITH문으로 계층쿼리 작성하기

⚔️ 예제(MySql): WITH문으로 계층쿼리 작성하기

재귀적인 쿼리를 이용하면, 쿼리의 결과를 반복적으로 조회하고 처리할 수 있다.

  
-- 예제(1)
WITH RECURSIVE cte_count(num) AS
(
    SELECT 1 AS num
      FROM DUAL
     UNION ALL
    SELECT c.num + 1
      FROM cte_count c
     WHERE c.num < 10 -- 재귀적 쿼리가 끝날 수 있도록 적절한 종료조건이 있어야 한다
)
SELECT num
  FROM cte_count;

-- 예제(2)
WITH RECURSIVE employee_hierarchy(eid, ename) AS
(
    -- FOUNDATION CASE (Non-Recursive 문장) (첫번째 루프에서만 실행)
    -- 재귀적 프로세스를 시작하기 위해 반환할 초기 데이터 집합 정의
    SELECT level
         , emp_id AS eid
         , emp_name AS ename
      FROM employees
     WHERE emp_name = 'John Doe'
     UNION ALL
    -- RECURSIVE CASE (Recursive 문장) (읽어올 때마다 행의 위치가 기억)
    -- FOUNDATION CASE 반환 데이터로부터 실행되며, 각 재귀적 단계에서 결과집합에 새로운 행 추가
    SELECT cte.level + 1
         , e.emp_id  -- 두번째 쿼리부터는 별칭 생략 가능, 단 컬럼의 개수와 데이터 타입이 일치해야한다.
         , e.name
      FROM employees e
      JOIN employee_hierarchy eh
        ON e.emp_id = eh.manager_id
     ORDER BY e.emp_id -- ORDER BY절은 마지막 SELECT문에만 사용가능
)
SELECT level, eid, ename
  FROM employee_hierachy;

WITH RECURSIVE 구문은 가상 테이블을 생성하면서 가상 테이블 자신의 값을 참조하여 값을 결정할 때 사용된다.

  
WITH level1 AS
(
  SELECT emp_id AS eid
       , emp_name AS ename
       , department AS edept
    FROM employees
   WHERE emp_name = 'John Doe'
)
, level2 AS
(
  SELECT e.emp_id AS eid
       , e.emp_name AS enmae
       , e.department AS edept
    FROM employees e
   WHERE level1 l1
      ON e.manager_id = li.eid
)
SELECT eid, ename, edept, '1' AS level
  FROM level1
 UNION ALL
SELECT eid, ename, edept, '2' AS level
  FROM level2
 ORDER BY level, ename;

위 쿼리와 같이 계층 깊이가 고정되고, 유연성이 부족해지지만 재귀적인 쿼리를 이용하지 않고도
다중 WITH절만으로도 계층형 쿼리를 구현할 수 있다.

Sql튜닝

This post is licensed under CC BY 4.0 by the author.

Oracle9, WITH절

WITH절 동작방식

UNION / UNION ALL

예제(MySql): WITH문으로 계층쿼리 작성하기

Trending Tags