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

UNION을 사용한 조건 분기는 WHERE 구만 조금씩 다른 여러 개의 SELECT 구문을 합쳐서, 복수의 조건에 일치하는 하나의 결과 집합을 얻고 싶을 때 사용한다.

성능적인 측면의 단점

• 외부적으로는 하나의 SQL 구문을 실행하는 것처럼 보이지만, 내부적으로는 여러 개의 SELECT 구문을 실행하는 실행 계획으로 해석
• 테이블에 접근하는 횟수가 많아져서 I/O 비용이 크게 증가

⇒ 따라서, 무조건적으로 UNION을 사용해서는 안된다.

UNION을 사용한 조건 분기 예제

이 테이블은 각각의 상품에 대해 세금 포함 가격과 세금이 포함되지 않은 가격 데이터가 있다.

item_id	year	item_name	price_tax_ex	price_tax_in
100	2000	머그컵	500	525
100	2002	티스푼	500	525

요구사항

→ 2002년부터 법이 개정되면서, 세금이 포함된 가격을 표시하는 게 의무

→ 2001년까지의 상품은 세금이 포함되지 않은 가격, 2002년부터의 상품은 세금이 포함된 가격을 price 필드로 표시해야 함

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;

• 조건이 배타적이므로, 중복된 레코드가 발생 X

문제점

• 쓸데없이 길다 ⇒ 거의 같은 쿼리 두 번 실행
• 성능이 좋지 않다.

UNION을 사용했을 때 실행 계획

QUERY PLAN                             
--------------------------------------------------------------------
 Append  (cost=0.00..2.48 rows=12 width=47)
   ->  Seq Scan on items  (cost=0.00..1.15 rows=6 width=47)
         Filter: (year <= 2001)
   ->  Seq Scan on items items_1  (cost=0.00..1.15 rows=6 width=47)
         Filter: (year >= 2002)
(5 rows)

• Items 테이블에 2회 접근 → Table ACCESS FULL 발생,
• 저장소의 I/O 포함한 비용 또한, 테이블의 크기에 따라 선형으로 증가하게 된다

WHERE 구에서 조건 분기를 하면 초보자

위에서 UNION을 사용해 실행하려던 SQL 구문을, 아래와 같이 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;

결과는 같지만, 성능적으로는 훨씬 좋은 쿼리이다. (테이블의 크기가 커질수록, 명확)

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

QUERY PLAN                       
-------------------------------------------------------
Seq Scan on items  (cost=0.00..1.18 rows=12 width=47)
(1 row)

UNION을 사용한 쿼리문과 다른 점

• SQL문 가독성 증가
• 테이블 접근 2회 → 1회로 감소

사실 RDB와 SQL이 가진 컨셉은 “사용자가 데이터에 접근 경로라는 물리 레벨의 문제를 의ㄴ식하지 않도록 하자” 인데, 아직 이렇게 실행 계획 레벨에서 SQL 성능을 판단해줘야 한다.

SQL을 마스터하는 열쇠

• 기본 단위를 항상 “식”으로 생각하자
• UNION을 사용한 분기는 SELECT’구문’을 기본 단위로 분기헀지만, CASE 식을 사용한 분기는 문자 그대로 ‘식’을 이용했다.
  • 구문 → 식으로 사고를 변경하는 것이 핵심
• “문제를 절차 지향형 언어로 해결한다면, 어떤 IF 조건문을 사용할까? ⇒ “SQL의 CASE로는 어떻게 해결할 수 있지?” 방향으로 사고하자

집계 & 조건 분기

아래와 같은 테이블이 있다고 가정하자. (남자=1, 여자=2)

prefecture(지역이름)	sex(성별)	pop(인구)
성남	1	60
성남	2	40
수원	2	100

각 지역별 여성 인구와 남성 인구를 하나의 row로 출력하는 결과를 받으려고 한다.

집계 대상으로 조건 분기

UNION 방식

절차 지향적인 사고방식이라면,

남성의 인구를 지역별로 구한 다음, 여성의 인구를 지역별로 구한 뒤 merge하는 방법(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 prefecture, NULL AS pop_men, pop AS pop_wom
          FROM Population
          WHERE sex = '2') TMP --여성
GROUP BY prefecture;

아래는 위 SQL문에서 UNION만 실행한 결과다.

null AS pop_wom 또는 null AS pop_men 과 같이 작성하면, 아래처럼 null 값을 가진 column이 결과에 포함된다.

prefecture | pop_men | pop_wom 
------------+---------+---------
 광명       |         |      50
 용인       |         |     200
 용인       |      20 |        
 성남       |      60 |        
 광명       |     100 |        
 수원       |      90 |        
 성남       |         |      40
 수원       |         |     100
 일산       |     100 |        
 일산       |         |     100
(10 rows)

위와 같이 남성과 여성의 인구가 따로 나온 결과를 GROUP BY 를 이용해 하나의 레코드로 집약

UNION을 이용한 쿼리 실행 계획

QUERY PLAN                                        
------------------------------------------------------------------------------------------
 HashAggregate  (cost=2.75..2.85 rows=10 width=98)
   Group Key: population.prefecture
   ->  HashAggregate  (cost=2.48..2.58 rows=10 width=90)
         Group Key: population.prefecture, population.pop, (NULL::integer)
         ->  Append  (cost=0.00..2.40 rows=10 width=90)
               ->  Seq Scan on population  (cost=0.00..1.12 rows=5 width=15)
                     Filter: (sex = '1'::bpchar)
               ->  Seq Scan on population population_1  (cost=0.00..1.12 rows=5 width=15)
                     Filter: (sex = '2'::bpchar)

테이블 full scan이 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를 이용한 쿼리 실행 계획

QUERY PLAN                            
------------------------------------------------------------------
 HashAggregate  (cost=1.23..1.28 rows=5 width=23)
   Group Key: prefecture
   ->  Seq Scan on population  (cost=0.00..1.10 rows=10 width=13)

테이블 full scan 한 번 실행

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

집약 결과로 조건 분기

아래와 같은 테이블이 있다고 가정

emp_id(직원ID)	team_id(팀ID)	emp_name(직원 이름)	team(팀)
201	1	Joe	상품기획
201	2	Joe	개발
202	2	Jim	개발

• 소속된 팀 1개 → 팀의 이름 그대로 출력
• 소속된 팀 2개 → ‘2개를 겸무’라는 문자열 출력
• 소속된 팀 3개 → ‘3개 이상을 겸무’라는 문자열 출력

예상 결과

	   emp_name     |        team        
------------------+--------------------
 Carl             | 영업              
 Bree             | 3개 이상을 겸무
 Jim              | 개발              
 Kim              | 2개를 겸무
 Joe              | 3개 이상을 겸무

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;

앞서 봤던 여성, 남성 인구를 출력하는 SQL과 달리, 집합 결과의 레코드 수에 따라 조건 분기가 발생하는 것이 다르다.

따라서, 조건 분기가 WHERE 구가 아니라 HAVING 구에 지정

WHERE 구의 조건 분기 → 집합 대상
HAVING 구의 조건 분기 → 집합 결과

UNION의 실행 계획

QUERY PLAN                                        
-----------------------------------------------------------------------------------------
 HashAggregate  (cost=3.79..3.83 rows=4 width=100)
   Group Key: employees.emp_name, (max(employees.team))
   ->  Append  (cost=1.19..3.77 rows=4 width=100)
         ->  HashAggregate  (cost=1.19..1.26 rows=1 width=49)
               Group Key: employees.emp_name
               Filter: (count(*) = 1)
               ->  Seq Scan on employees  (cost=0.00..1.11 rows=11 width=39)
         ->  HashAggregate  (cost=1.17..1.23 rows=1 width=49)
               Group Key: employees_1.emp_name
               Filter: (count(*) = 2)
               ->  Seq Scan on employees employees_1  (cost=0.00..1.11 rows=11 width=17)
         ->  HashAggregate  (cost=1.17..1.23 rows=2 width=49)
               Group Key: employees_2.emp_name
               Filter: (count(*) >= 3)
               ->  Seq Scan on employees employees_2  (cost=0.00..1.11 rows=11 width=17)

총 3번의 테이블 full scan이 발생

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;

• 테이블의 접근 비용 1/3로 감소
• GROUP BY의 hash 연산도 3회 → 1회로 감소

⇒ 집약 결과를 CASE 식의 입력으로 사용

❓ 어떻게 집약 결과를 CASE식에 사용할 수 있었을까?
COUNT 또는 SUM 과 같은 집약 함수의 결과는 1개의 레코드로 압축, 즉 스칼라값이 되기 떄문에 CASE 식의 매개변수에 집약 함수를 넣을 수 있다.

WHERE 구에서 조건 분기를 사용해도 바보지만, HAVING 구에서 사용해도 바보다.

그래도 UNION이 필요한 경우

UNION을 사용할 수 밖에 없다

머지 대상이 되는 SELECT 구문에서 사용하는 테이블이 다른 경우

테이블이 달라도, CASE 식을 사용할 수 있다. FROM 구에서 테이블을 결합 후, CASE 식을 사용해도 되는데, 이때 불필요한 결합이 발생해 성능을 더 악화시킬 수 있다.

UNION을 사용할 때 성능적으로 좋은 경우

아래와 같은 테이블이 있다고 가정
이 테이블의 레코드는 (date_n, fig_n)이라는 3개의 짝에서 하나의 짝에만 값이 있고, 다른 짝은 모두 (NULL, NULL)이다.

key	name	date_1	fig_1	date_2	fig_2	date_3	fig_3
1	a	2013-11-01	T
2	b			2013-11-01	T
3	c			2013-11-01	F

이 테이블의 레코드 중, 특정 날짜를 값으로 갖고 있고, 그 날짜와 짝이되는 fig_n의 값이 T인 레코드를 출력하자.

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) ;

위와 같이 Index를 생성.

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';

그렇다면 위처럼 UNION을 사용했을 때, 위에서 생성한 Index를 사용할 것이다.

실행 계획을 살펴보면, 현재 데이터가 적기 때문에 Full scan을 3번 발생하도록 계획을 얻지만, 실제로 데이터가 많아지면 3번 다 Index scan이 발생한다는 것을 알 수 있다.

OR 사용

만약 이 문제를 UNION이 아닌, OR를 사용한다면?

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');

QUERY PLAN                                                                                            
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on threeelements  (cost=0.00..1.15 rows=1 width=29)
   Filter: (((date_1 = '2013-11-01'::date) AND (flg_1 = 'T'::bpchar)) OR ((date_2 = '2013-11-01'::date) AND (flg_2 = 'T'::bpchar)) OR ((date_3 = '2013-11-01'::date) AND (flg_3 = 'T'::bpchar))

위와 같이 table full scan이 발생한다.

이때 주목해야할 점은 WHERE 구문에서 OR을 사용하면 해당 필드에 부여된 인덱스를 사용할 수 없다는 점

따라서 결론적으론,

3회의 인덱스 스캔(UNION) VS 1회의 테이블 풀 스캔(OR)

어떤 것이 빠른 지에 대한 문제이다.

테이블 크기와 검색 조건에 따른 선택 비율에 따라 다르게 선택할 수 있다.

만약, 테이블이 크고, WHERE 조건으로 선택되는 레코드의 수가 적다면, 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));

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 사용했을 때와 성능은 같다.

하지만 비즈니스 룰을 조금 변경한다면 다른 결과가 나올 수 있다.

만약, (date_n, flg_n)의 짝이 정해져 있는 바가 없다면, 아래와 같은 데이터가 추가될 수 있다.

INSERT INTO ThreeElements VALUES ('7', 'g', '2013-11-01', 'F', NULL, NULL, '2013-11-01', 'T');

하지만, 위에서 본 CASE 식을 사용한 SQL구문을 사용한다면, (date_3, flg_3) = ('2013-11-01', 'T') 임에도 불구하고, 첫 번째 조건 WHEN date_1 = '2013-11-01' 에서 CASE 식을 빠져나오기 때문에, flg_1이 F로 판단되어 결과 데이터에 포함이 될 수 없다.