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

UNION을 사용한 조건 분기는 SQL 초보자가 좋아하는 기술 중 하나입니다. 일반적으로 WHERE 구만 조금씩 다른 여러 개의 SELECT 구문을 합쳐서 복수의 조건에 일치하는 하나의 결과 집합을 얻고 싶을 때 사용하니다. 하지만 이런 방법은 성능적인 측면에서 굉장히 큰 단점을 가지고 있습니다. 외부적으로는 하나의 SQL 실행처럼 보이지만, 내부적으로 여러개의 SELECT 구문을 실행하는 실행 계획으로 해석됩니다. 따라서 테이블에 접근하는 횟수가 많아져서 I/O 비용이 크게 늘어납니다.  따라서 SQL에서 조건 분기는 UNION을 사용해도 좋을지 신중히 검토해야 합니다. 

 

 

UNION의 문제점

---

• WHERE 구에서 조건분기하기

2001년도 이하인 경우 price_tax_ex가 price이고 2001년도 이상인 경우 price_tax_in을 price로 하는 쿼리문입니다

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;

 

 위 SQL의 문제점은 쿼리가 쓸데없이 길다는 것과 실행 계획 성능이 나쁘다는 것입니다. UNION ALL 때문에 Items 테이블에 2회 접근을 하므로 TABLE ACCESS FULL이 2번 발생하여 테이블 크기에 따라서 읽어드리는 비용이 선형으로 증가합니다. 따라서 UNION을 조건 분기로 사용하는 것은 가급적 회피해야 합니다.

 

 

• SELECT 구문에서 CASE 식을 이용해 분기하여 개선

위의 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;

 

위 SQL은 Items 테이블에 1회만 접근하므로 TABLE ACCESS FULL이 1회로 줄어들어 성능이 약 2배 좋아졌습니다.

 

 

집계대상으로 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은 Population 테이블에 풀 스캔이 총 2회 수행됩니다.

 

 

• SELECT 구문에서 CASE 식을 이용해 분기하여 개선

위의 SQL의 경우 집계를 위해 UNION이 아닌  SELECT에서 분기하여 성능을 높이고 코드 양을 줄일 수 있습니다.

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;

 

위 SQL은 Population 테이블에 풀 스캔이 1회로 줄었습니다. 이렇게 CASE 식으로 조건분기를 사용하면 UNION을 사용하지 않을 수 있습니다. 따라서 테이블 접근을 줄일 수 있으므로 SQL에서 CASE 사용은 굉장히 중요합니다.

 

 

 

집약 결과로 조건분기하기

---

직원과 직원이 소속된 팀을 관리하는 테이블에서 직원이 속한 팀에 따라서 직원명과 팀명을 나타내는 쿼리입니다.

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

 

이 경우 조건 분기가 레코드가 아닌 집합의 레코드 수입니다. 따라서 조건분기가 WHERE가 아니라 HAVING입니다. 하지만 UNION을 사용하고 있기 때문에 WHERE 분기와 크게 다르지 않습니다.

 

• SELECT 구문에서 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;

 

위 SQL을 사용하면 테이블의 접근 비용을 3분의 1로 줄일 수 있습니다. 이는 집약 결과(COUNT 함수의 리턴값)를  CASE 식의 입력으로 사용했기 때문입니다. 집약 함수의 결과가 스칼라가 되어 이 값을 CASE 식에 넣을 수 있습니다. WHERE 절에 조건분기 하는것보다, HAVING절에 조건분기 하는 것보다 SELECT 절에서 조건분기 하는 것이 유리합니다.

 

 

UNION을 사용하는 경우

---

• 테이블이 다른 데이터를 머지

UNION이 항상 성능에 문제가 되는 것은 아닙니다. UNION이 성능이 더 좋은 경우도 있습니다. 대표적으로 SELECT를 사용하는 구문의 테이블이 다른 경우입니다. 즉, 여러 개의 테이블에서 검색한 결과를 머지하는 경우입니다.

 

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

 

• UNION 사용이 성능이 더 좋은 경우

UNION을 사용했을 때 좋은 인덱스를 사용하고 이외의 경우에 테이블 풀 스캔이 발생하면, UNION을 사용한 방법이 성능이 더 좋을 수 있습니다. 날짜와 플래그를 조회할 수 있는 테이블이 있다고 가정해 보겠습니다.

 

SELECT key, name,
	date_1, fig_1,
    date_2, fig_2,
    date_3, fig_3
  FROM ThreeElements
WHERE date_1 = '2013-11-01'
  AND fig_1 ='T'
UNION
SELECT key, name,
	date_1, fig_1,
    date_2, fig_2,
    date_3, fig_3
  FROM ThreeElements
WHERE date_2 = '2013-11-01'
  AND fig_2 ='T'
UNION
SELECT key, name,
	date_1, fig_1,
    date_2, fig_2,
    date_3, fig_3
  FROM ThreeElements
WHERE date_3 = '2013-11-01'
  AND fig_3 ='T'

 

위 SQL은 3개의 SELECT를 UNION으로 연결하였습니다. 쿼리를 최적의 성능으로 수행하려면 아래의 인덱스 조합이 필요합니다.

CREATE INDEX IDX_01 ON ThreeElements (date_1, fig_1);
CREATE INDEX IDX_02 ON ThreeElements (date_2, fig_2);
CREATE INDEX IDX_03 ON ThreeElements (date_3, fig_3);

 

위의 인덱스 덕분에 INDEX RANGE SCAN이 가능합니다. ThreeElements 테이블 레코드 수가 많아지고 WHERE 구의 검색조건에서 레코 수를 많이 압축할수록, 테이블 풀 스캔보다 훨씬 빠른 접근 속도를 낼 수 있습니다.

 

• OR 절을 사용한 경우

SELECT key, name,
	date_1, fig_1,
    date_2, fig_2,
    date_3, fig_3
  FROM ThreeElements
WHERE (date_1='2013-11-01' AND fig_1='T')
	OR (date_2='2013-11-01' AND fig_2='T')
    OR (date_3='2013-11-01' AND fig_3='T')

 

위 SQL은 SELECT 구문이 하나로 줄어들었기 때문에 테이블 접근이 1회로 줄어들었습니다. 하지만 이때 인덱스가 사용되지 않고 테이블 풀스캔이 수행됩니다. WHERE 구문에서 OR를 사용하면 해당 필드에 부여된 인덱스를 사용할 수 없습니다.

 

• IN 절을 사용한 경우

SELECT key, name,
	date_1, fig_1,
    date_2, fig_2,
    date_3, fig_3
  FROM ThreeElements
WHERE ('2013-11-01' ,'T')
	IN ((date_1 , fig_1)
       (date_2 , fig_2)
       (date_3 , fig_3))

 

위 SQL은 OR절을 IN절로 표현하였는데 실행 계획은 OR을 사용할 때와 같습니다. 이는 다중필드 방식으로 경우에 따라 성능 향상이 가능합니다. 

 

 

참고

* SQL 레벨업