DB & SQL/SQL 레벨업

3장 SQL의 조건 분기

PHM 2023. 8. 7. 10:09

8. UNION을 사용한 쓸데없이 긴 표현

- SQL 에서 조건 분기를 할 때 UNION을 사용해도 좋을지 여부는 신중히 검토해야 한다.

 

1. UNION을 사용한 조건 분기와 관련된 간단한 예제

SELECT item_name, year, price_tax_ex AS price
    FROM Items
    WHERE year <= 2001
UNION ALL
SELECT item_name, year, price_tax_in AS price
    FROM Items
    WHERE year >= 2002;

- 문제점

    ㆍ1. 쓸데없이 길다.

    ㆍ2. 성능이 좋지 않다.

 

- UNION 쿼리는 Items 테이블에 2회 접근하고 있다.

    ㆍ이 두번도 TABLE ACCESS FULL 이 발생하므로, 읽어들이는 비용도 테이블에 크기에 따라 선형으로 증가

 

- 정확한 판단 없이 SELECT 구문 전체를 여러 번 사용해서 코드를 길게 만드는 것은 쓸데없이 테이블 접근을 발생시키며 SQL의 성능을 나쁘게 만든다.

    ㆍ또한 물리 자원(저장소의 I/O 비용)도 쓸데없이 소비하게 된다.

 

2. WHERE 구에서 조건 분기를 하는 사람은 초보자

- SQL 격언

    ㆍ"조건 분기를 WHERE 구로 하는 사람들은 초보자다. 잘 하는 사람은 SELECT 구만으로 조건 분기를 한다."

SELECT item_name, year,
    CASE WHEN year <= 2001 THEN price_tax_ex
         WHEN year >= 2002 THEN price_tax_in END AS price
    FROM Items;

 

3. SELECT 구를 사용한 조건 분기의 실행 계획

- 위의 쿼리를 보면 Items 테이블에 대한 접근이 1회로 줄어들었다.

 

- SQL 구문의 성능이 좋은지 나쁜지는 반드시 실행 계획 레벨에서 판단해야 한다.

 

- UNION을 사용한 분기는 SELECT '구문'을 기본 단위로 분기하고 있다.

    ㆍ구문을 기본 단위로 사용하고 있다는 점에서, 아직 절차 지향형의 발상을 벗어나지 못한 방법이라고 말할 수 있다.

- 반면 CASE식을 사용한 분기는 문자 그대로 '식'을 바탕으로 하는 사고이다.

- 이렇게 '구문'에서 '식'으로 사고를 변경하는 것이 SQL을 마스터하는 열쇠 중 하나이다.

 

9. 집계와 조건 분기

1. 집계 대상으로 조건 분기

UNION을 사용한 방법

SELECT prefecture, SUM(pop_men) AS pop_men, SUM(pop_wom) AS pop_wom
    FROM ( SELECT prefecture, pop AS pop_men, null AS pop_wom
               FROM Population
               WHERE sex = '1'
           UNION
           SELECT prefecturn, NULL AS pop_men, pop AS pop_wom
               FROM Population
               WHERE sex = '2' ) TMP
   GROUP BY prefecture;

- 서브쿼리 TMP는 남성과 여성의 인구가 별도의 레코드가 나온다.

- 이러한 쿼리의 가장 큰 문제는 WHERE 구에서 sex 필드로 분기를 하고, 결과를 UNION 으로 머지한다는 절차 지향적인 구성에 있다.

 

UNION 의 실행 계획

- 실행 계획을 보면 Population 테이블에 풀 스캔이 2회 수행되는 것을 확인할 수 있다.

 

집계의 조건 분기도 CASE 식을 사용

- 이 문제는 CASE 식의 응용 방법으로 굉장히 유명한 표측/표두 레이아웃 이동 문제이다.

SELECT prefecture,
        SUM(CASE WHEN sex = '1' THEN pop ELSE 0 END) AS pop_men,
        SUM(CASE WHEN sex = '2' THEN pop ELSE 0 END) AS pop_wom
    FROM Population
    GROUP BY prefecture;

 

CASE 식의 실행계획

- 중요한 것은 외관이 간단해질 뿐만 아니라 성능도 향상된다는 것이다.

- Population 테이블로의 풀 스캔이 1회로 감소

    ㆍ따라서 UNION을 사용한 경우의 2회에 비해 I/O 비용이 절반으로 감소

- CASE 식은 SQL에서 굉장히 중요한 도구이다.

    ㆍ다양한 표현을 할 수 있는 것은 물론 성능적으로 큰 힘을 발휘한다.

 

2. 집약 결과로 조건 분기

UNION을 사용한 조건 분기

SELECT emp_name,
        MAX(team) AS team
    FROM Employees
    GROUP BY emp_name
HAVING COUNT(*) = 1
UNION
SELECT emp_name,
        '2개를 겸무' AS team
    FROM Employees
    GROUP BY emp_name
HAVING COUNT(*) = 2
UNION
SELECT emp_name,
        '3개 이상을 겸무' AS team
    FROM Employees
    GROUP BY emp_name
HAVING COUNT(*) >= 3;

 

UNION의 실행 계획

- 3개의 쿼리를 머지하는 쿼리이므로 예상대로 Employees 테이블에 대한 접근도 3번 발생

 

CASE 식을 사용한 조건 분기

SELECT emp_name,
        CASE WHEN COUNT(*) = 1 THEN MAX(team)
             WHEN COUNT(*) = 2 THEN '2개를 겸무'
             WHEN COUNT(*) >= 3 THEN '3개 이상을 겸무'
         END AS team
     FROM Employees
 GROUP BY emp_name;

 

CASE 식을 사용한 조건 분기의 실행계획

- 이렇게 CASE 식을 사용하면 테이블에 접근 비용을 3분의 1로 줄일 수 있다.

- 추가적으로 GROUP BY의 HASH 연산도 3회에서 1회로 줄어들었다.

- 앞에서 'WHERE 구에서 조건 분기를 하는 사람은 초보자' 라고 말했는데, 마찬자기로 'HAVING 구에서 조건 분기를 하는 사람도 초보자' 다.

 

10. 그래도 UNION이 필요한 경우

- 지금까지 UNION을 사용하는 것이 나쁘다는 식으로 소개했다.

- 하지만 UNION을 사용하지 않으면 안되는 경우도 있다.

    ㆍ또한 UNION을 사용하는 것이 오히려 성능적으로 좋은 경우도 있다.

 

1. UNION을 사용할 수 밖에 없는 경우

- 여러 개의 테이블에서 검색한 결과를 머지하는 경우

SELECT col_1
    FROM Table_A
    WHERE col_2 = 'A'
UNION ALL
SELECT col_3
    FROM Table_B
    WHERE col_4 = 'B';

- FROM 구에서 테이블을 결합하면 CASE식을 사용해 원하는 결과를 구할 수 있다.

    ㆍ하지만 그렇게 하면 필요 없는 결합이 발생해서 성능적으로 악영향이 발생한다. ( UNION 을 사용한다면 발생하지 않는다. )

 

2. UNION을 사용하는 것이 성능적으로 더 좋은 이유

- UNION을 사용하는 편이 더 성능이 좋을 수 있는 경우는 인덱스와 관련된 경우이다.

- UNION을 사용했을 때 좋은 인덱스(압축을 잘 하는 인덱스)를 사용하지만, 이외의 경우에는 테이블 풀 스캔이 발생하면, UNION을 사용한 방법이 성능적으로 좋을 수 있다.

 

UNION을 사용한 방법

SELECT key, name,
        date_1, flg_1,
        date_2, flg_2,
        date_3, flg_3
    FROM ThreeElements
    WHERE date_1 = '2013-11-01'
      AND flg_1 = 'T'
UNION
SELECT key, name,
        date_1, flg_1,
        date_2, flg_2,
        date_3, flg_3
    FROM ThreeElements
    WHERE date_2 = '2013-11-01'
      AND flg_2 = 'T'
UNION
SELECT key, name,
        date_1, flg_1,
        date_2, flg_2,
        date_3, flg_3
    FROM ThreeElements
    WHERE date_3 = '2013-11-01'
      AND flg_3 = 'T';

- 머지되는 3개의 SELECT 구문에서 다른 것은 WHERE 구 뿐이다.

- 이때 포인트가 인덱스이다.

    ㆍ이 쿼리를 최적의 성능으로 수행하려면 다음과 같은 필드 조합에 인덱스가 필요하다.

CREATE INDEX IDX_1 ON ThreeElements (date_1, flg_1);
CREATE INDEX IDX_2 ON ThreeElements (date_2, flg_2);
CREATE INDEX IDX_3 ON ThreeElements (date_3, flg_3);

- 이러한 인덱스가 WHERE 구에서 (date_n, flg_n) 라는 필드 조합을 사용할 때 빠르게 만들어 준다고 생각하자.

 

OR을 사용한 방법

- 이러한 문제를 UNION을 사용하지 않고 푼다면 어떻게 풀까?

SELECT key, name,
        date_1, flg_1,
        date_2, flg_2,
        date_3, flg_3
    FROM ThreeElements
WHERE (date_1 = '2013-11-01' AND flg_1 = 'T')
   OR (date_2 = '2013-11-01' AND flg_2 = 'T')
   OR (date_3 = '2013-11-01' AND flg_3 = 'T');

- SELECT 구문이 하나로 줄어들었기 때문에 ThreeElements 테이블에 대한 접근이 1회로 줄었다.

    ㆍ하지만 이때 인덱스가 사용되지 않고, 그냥 테이블 풀 스캔이 수행된다.

    ㆍ이렇게 WHERE 구문에서 OR을 사용하면 해당 필드에 부여된 인덱스를 사용할 수 없다.

 

- 따라서 이러한 경우에 UNION과 OR의 성능 비교는 결국 3회의 인덱스 스캔 VS 1회의 테이블 풀 스캔 중에서 어떤 것이 더 빠른지에 대한 문제가 된다.

    ㆍ이는 테이블 크기와 검색 조건에 따른 선택 비율(레코드 히트율)에 따라 답이 달라진다.

    ㆍ하지만 테이블이 크고, WHERE 조건으로 선택되는 레코드의 수가 충분히 작다면 UNION이 더 빠르다.

    ㆍ따라서 UNION을 사용하는 경우가 더 빠를 수 도 있다.

 

IN을 사용한 방법

SELECT key, name,
        date_1, flg_1,
        date_2, flg_2,
        date_3, flg_3
    FROM ThreeElements
WHERE ('2013-11-01', 'T')
        IN ((date_1, flg_1),
             (date_2, flg_2),
             (date_3, flg_3));

- 이는 다중 필드(multiple fields, 또는 행식(row expression)) 라는 기능을 사용한 방법이다.

- IN의 매개변수로는 단순한 스칼라뿐만 아니라, 이렇게 (a, b, c)와 같은 값의 리스트(배열)을 입력할 수도 있다.

- OR을 사용했을 때보다 간단하고 이해하기 쉬울 수 있지만 실행 계획은 OR을 사용할 때와 같다.

    ㆍ따라서 성능적인 문제도 같다.

 

 CASE 식을 사용한 방법

SELECT key, name,
        date_1, flg_1,
        date_2, flg_2,
        date_3, flg_3
    FROM ThreeElements
WHERE CASE WHEN date_1 = '2013-11-01' THEN flg_1
           WHEN date_2 = '2013-11-01' THEN flg_2
           WHEN date_3 = '2013-11-01' THEN flg_3
           ELSE NULL END = 'T';

- 실행 계획은 OR, IN을 사용할 때와 같다.

    ㆍ따라서 성능적으로 같은 문제를 안게 된다.

- 이러한 쿼리를 사용할 때는 주의점이 있다.

    ㆍ비즈니스 룰을 조금 변경하면 UNION, OR, IN을 사용할 때와 다른 결과가 나온다.

11. 절차 지형형과 선언형

- 예외적인 몇 가지 상황을 제외하면 UNION을 사용하지 않는 것이 성능적으로도 좋고 가독성도 좋다는 것이다.

- 원래 UNION이 조건 분기를 위해 만들어진 것이 아니므로 당연한 결과이다.

- 반대로 CASE 식은 조건 분기를 위해 만들어졌으므로, CASE식을 사용하는 것이 훨씬 자연스러운 것이다.

 

1. 구문 기반과 식 기반

- SQL 초보자는 절차 지향적인 세계에서 살고 있다.

    ㆍ이는 대부분 처음 배우는 프로그래밍 언어가 절차 지향형 프로그래밍 언어이기 때문이다.

    ㆍ그 세계에서 생각의 기본 단위는 '구문(statement)' 이다.

- 하지만 SQL 중급자 이상은 선언적인 세계이다.

    ㆍ여기서의 기본 단위는 '식(expression)'이다.

 

- SQL의 기본적인 체계는 선언형이다.

-이 세계의 주역은 '구문'이 아니라 '식'이다.

- 절차 지향형 언어가 CASE 구문으로 분기하는 것을, SQL은 CASE식으로 분기한다.

 

2. 선언형의 세계로 도약

 

마치며..

- SQL의 성능은 저장소의 I/O를 얼마나 감소시킬지 있을지가 열쇠

- UNION에서 조건 분기를 표현한다면 "내가 쓸데없이 길게 쓰고 있는 것은 아닐까?" 라는 것을 항상 의식할 것

- IN 또는 CASE 식으로 조건 분기를 표현할 수 있다면, 테이블에의 스캔을 크게 감소시킬 가능성이 있음

- 이를 위해서라도, 구문에서 식으로의 패러다임 전환을 연습