Ch.2의 경우 기본적인 SQL을 설명하는 단원이라 읽기는 했지만 정리는 스킵했다.
쓸데없는 UNION의 사용
UNION은 외부적으로 하나의 SQL 구문을 실행하는 것처럼 보이지만, 내부적으로는 여러 개의 SELECT 구문을 실행하는 실행 계획으로 해석된다. 따라서 I/O 비용이 크게 증가한다.
UNION을 사용해도 좋을지 여부는 신중히 검토해야한다.
다음과 같은 쿼리가 있다고 가정해보자.
SELECT item_name, year, price_tax_ex AS price
FROM Items
WHERE year <= 2001
UNION ALL
SELECT item_name, year, price_tax_ex AS price
FROM Items
WHERE year >= 2002;
거의 같은 쿼리를 두 번이나 실행하고 있다는 점도 문제지만, 성능적으로 문제가 된다.
UNION을 사용 했을 때의 실행 계획에서 Item 테이블에 2회 접근한다.
-> TABLE ACCESS FULL(index없이 테이블을 모두 스캔하는 것)도 2번 발생한다. 읽어오는 비용도 테이블의 크기에 따라 선형적으로 증가하게 된다.
UNION은 간단하게 레코드를 합칠 수 있다는 점에서 편리하지만, 물리 자원과 SQL의 성능을 나쁘게 만드므로 정확한 판단 하에 사용해야 한다.
개선된 쿼리
SELECT item_name, year,
CASE WHEN year <= 2001 THEN price_tax_ex
WHEN year >= 2002 THEN price_tax_in END AS price
FROM Items;UNION을 사용한 쿼리와 같은 결과를 출력하지만 성능적으로 CASE를 쓴 쿼리가 훨씬 좋다.
Items 테이블 접근 횟수 : 1회
TABLE ACCESS FULL : 1회
-> UNION을 사용한 구문보다 성능이 2배 좋아짐
UNION의 기본 단위는 SELECT '구문'이다. 이는 아직 절차 지향형의 발상을 벗어나지 못한 방법이다. 반면, CASE의 기본 단위는 '식'이다.
'구문'에서 '식'으로 사고를 변경하는 것이 SQL을 마스터하는 열쇠 중 하나이다.
집계와 조건 분기
다음과 같은 테이블이 있다고 가정하자.
| prefecture(지역 이름) | sex(성별) | pop(인구) |
|---|---|---|
| 성남 | 1 | 60 |
| 성남 | 2 | 40 |
| 수원 | 1 | 30 |
| 수원 | 2 | 40 |
| 광명 | 1 | 50 |
| 광명 | 2 | 60 |
| 일산 | 1 | 20 |
| 일산 | 2 | 15 |
성별 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'
GROUP BY prefecture;남성의 인구를 지역별로 구하고, 여성의 인구를 지역별로 구한 뒤 UNION을 활용해 합치는 방법은 절차지향적인 방식이다.
또한 테이블에 접근하는 횟수가 늘어나 성능적으로 문제가 됨을 앞에서 학습했다.
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;외관이 간단해질 뿐만 아니라 성능도 캐시를 고려하지 않았을 때 2배로 증가한다.
'WHERE'구와 'HAVING'구에서 조건 분기를 하는 사람은 초보자이다.
UNION이 필요한 경우
대표적으로 SELECT 구문들에서 사용하는 테이블이 다른 경우이다.
물론 CASE 식을 사용할 수 있지만, 필요 없는 결합에 의해 성능적으로 악영향이 발생한다. 따라서 실행 계획을 확인해서 어떤 것이 더 좋은지 확인해야 한다.
인덱스와 관련된 경우
UNION을 사용했을 때 좋은 인덱스(압축을 잘 하는 인덱스)를 사용하지만, 이외의 경우에는 테이블 풀 스캔이 발생한다면, UNION을 사용한 방법이 성능적으로 더 좋을 수 있다.
예제 테이블
| key | name | date_1 | flg_1 | date_2 | flg_2 | date_3 | flg_3 |
|---|---|---|---|---|---|---|---|
| 1 | a | 2013-11-01 | T | ||||
| 2 | b | 2013-11-01 | T | ||||
| 3 | c | 2013-11-01 | F | ||||
| 4 | d | 2013-12-30 | T | ||||
| 5 | e | 2013-11-01 | T | ||||
| 6 | f | 2013-12-01 | F |
다음 테이블에서 date가 2013-11-01이고, flg가 T인 레코드들만 추출하고 싶다.
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';성능과 실행 계획을 살펴보자. 이때 집중해야 할 것은 인덱스이다.
(인덱스에 관한 내용은 10장에서 설명해준다고 한다.)
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);이러한 인덱스를 만들어 테이블의 풀스캔보다 훨씬 빠른 접근 속도를 기대할 수 있다고 한다.
요점은 다음과 같다. OR, IN, CASE를 활용했을 경우 1회의 테이블 풀 스캔이 일어난다.
3회의 인덱스 스캔 VS 1회의 테이블 풀 스캔 중에 어떤 것이 빠른지 트레이드오프 해야한다.
테이블의 크기와 검색 조건에 따른 선택 비율에 따라 답이 달라진다.
테이블이 크고, WHERE 조건으로 선택되는 레코드의 수가 충분히 작다면 UNION이 더 빠르다.
이 부분은 10장을 학습한 뒤 다시 보겠다.
이렇게 3장을 끝냈다. SQL이 너무나 싫었는데 실무에서 데이터를 조회할 일이 생겨 JOIN이랑 다른 잡다한 쿼리들을 날릴 일이 생겨 쓰다 보니 SQL이 재밌어졌다.
추가적으로 1장에서 학습한 DB에 메모리 부분이 인상깊어 하루 종일 생각났다.
