스파케티 쿼리

18.1 목표 : SQL 쿼리 줄이기

문제를 푸는데 두 개 이상의 SQL을 사용하는 것은 아예 고려하지 않는다.

18.2 안티패턴: 복잡한 문제를 한 번에 풀기

SQL은 하나의 쿼리나 문장으로 많은 것을 할 수 있다.
그러나 모든 작업을 한 줄의 코드로 해치워야 한다는 접근방법이 좋은 생각이라는 뜻은 아니다.
다른 프로그래밍 언어를 사용할 때도 이런 습관을 가지고 있는가?
(내 생각 : 각각 언어의 도구적인 특성을 무시한 것으로 오해될 수 있다고 말할 수 있다.)

의도하지 않은 제품

카테시안 곱 : 두 테이블 간에 관계를 제한하는 조건이 없을 때 발생한다.

 
SELECT p.product_id,
  COUNT(f.bug_id) AS count_fixed,
  COUNT(o.bug_id) AS count_open
FROM BugsProducts p
LEFT OUTER JOIN (BugsProducts bpf JOIN Bugs f USING (bug_id)) f 
  ON (p.bug_id = f.bug_id AND f.status = 'FIXED')
LEFT OUTER JOIN (BugsProducts bpo JOIN Bugs o USING (bug_id)) o 
  ON (p.bug_id = o.bug_id AND o.status = 'OPEN')
WHERE p.product_id = 1
GROUP BY p.product_id;

조건 예상값 결과값
product_id =1 and f.status = 'FIXED' 1 77
product_id =1 and f.status = 'OPEN' 7 77

그래도 충분하지 않다면

• 복잡한 SQL은 잘못된 결과를 얻을 수 있다.
• 작성하기도 어렵고, 수정하기도 어렵고, 디버깅하기도 어렵다.
• 실행할 때의 비용이 증가 한다

SQL을 복잡하게 작성하면, 이를 개선하는데 비용과 시간이 필요하게 되며,
시간은 자신에게나 프로젝트에나 소중한 것이다.

 
필자의 의도는 절대 그렇지 않겠지만... 발로 짠 SQL이라면 물론 그렇다..
나라면...
SELECT p.product_id,
  COUNT(case when f.status = 'FIXED' then 1 end) AS count_fixed,
  COUNT(case when f.status = 'OPEN' then 1 end) AS count_open
FROM BugsProducts p
     JOIN Bugs f ON ((p.bug_id = f.bug_id)
GROUP BY p.product_id;

18.3 안티패턴 인식 방법

• 합계와 개수가 dhoo 이렇게 크지?
  : 의도하지 않은 카테시안 곱으로 데이터 집합이 뻥튀기 됐다
• 나는 하루 종일 이 괴물 같은 SQL쿼리와 씨름했어
  : 지나치게 긴 SQL쿼리와 씨름하고 있었다면, 접근방법을 재고해야 한다.
• 우리 데이터베이스 리포트에는 아무것도 추가할 수 없어. SQL쿼리가 어떻게 동작하는지 이해하려면 시간이 엄청나게 오래 걸릴 거야
  : 아무도 손델 수 없는 지나치게 복잡한 쿼리는 작성하지 말아야 한다.
• 쿼리에 DISTINCT를 하나 더 추가해봐
  : 잘못된 쿼리의 결과값은 보정되지만 DBMS는 부가 작업에 대한 부하를 받게 된다.

18.4 안티패턴 사용이 합당한 경우

모든 결과를 정렬된 순서로 묶어서 보아야 할 경우....

18.5 해법: 분할해서 정복하기

한 번에 하나씩

SELECT p.product_id, COUNT(f.bug_id) AS count_fixed
FROM BugsProducts p
LEFT OUTER JOIN Bugs f ON (p.bug_id = f.bug_id AND f.status = 'FIXED')
WHERE p.product_id = 1
GROUP BY p.product_id;

SELECT p.product_id, COUNT(o.bug_id) AS count_open
FROM BugsProducts p
LEFT OUTER JOIN Bugs o ON (p.bug_id = o.bug_id AND o.status = 'OPEN')
WHERE p.product_id = 1
GROUP BY p.product_id;

Union 연산

(SELECT p.product_id, f.status, COUNT(f.bug_id) AS bug_count
 FROM BugsProducts p
 LEFT OUTER JOIN Bugs f ON (p.bug_id = f.bug_id AND f.status = 'FIXED')
 WHERE p.product_id = 1
 GROUP BY p.product_id, f.status)

 UNION ALL

(SELECT p.product_id, o.status, COUNT(o.bug_id) AS bug_count
 FROM BugsProducts p
 LEFT OUTER JOIN Bugs o ON (p.bug_id = o.bug_id AND o.status = 'OPEN')
 WHERE p.product_id = 1
 GROUP BY p.product_id, o.status)

ORDER BY bug_count;

SQL을 이용한 자동생성

코드 생성은 새로운 코드를 출력하는 코드를 작성하는 기법이다.

SELECT CONCAT('UPDATE Inventory '
  ' SET last_used = ''', MAX(u.usage_date), '''',
  ' WHERE inventory_id = ', u.inventory_id, ';') AS update_statement
FROM ComputerUsage u
GROUP BY u.inventory_id;

생성된 문장을 각각 수행시킨다.
(몇 분만에 작성했다고 한다. 테스트 시간 컴파일 기타 적재 등등..시간은?)

나라면..
merge into Inventory i
using (SELECT u.Inventory_id, max(u.usage_date) usage_date 
        FROM ComputerUsage u
       GROUP BY u.Inventory_id) U
on (u.Inventory_id = i.Inventory_id)
WHEN MATCHED THEN 
     UPDATE SET last_usedate = u.usage_date;

결론

필자의 내용을 보면 DBMS 데이터를 저장하고 있는 저장소고 옵티마이져는 블랙박스로 보고 있는 듯 하다.
그래서 잘 모르는 블랙박스의 구조에 대해서 이해하기 보다는 간단하게 확인할 수 있는 방법을 사용하듯이 DBMS를 바라보는 것으로 느껴진다.

• 최초작성일 : 2011년 12월 15일
• 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
• {*}이 문서의 내용은 인사이트(insight) 에서 출간한 'SQL AntiPatterns : 개발자가 알아야 할 25가지 SQL 함정과 해법'를 참고하였습니다.*