SQL 레벨업 Ch.9 — 갱신과 데이터 모델

SQL의 대부분은 SELECT 구문이고, UPDATE나 DELETE 같은 갱신은 깊이 다뤄 볼 기회가 적다. 그 결과 갱신 SQL은 검색 SQL 이상으로 비효율적으로 작성되기 쉽다. 이번 장은 갱신을 효율적으로 수행하는 방법을 다룬다.

NULL 채우기

keycol	seq	val
A	1	50
A	2
A	3
B	4	60
B	5
B	6	72
B	7
B	8

이전 레코드와 값이 같으면 val을 생략한 테이블이다. 비어 있는 val을 채우려 한다. 커서나 호스트 언어로 레코드를 하나씩 읽는 반복계가 먼저 떠오르지만 좋은 방법이 아니다.

상관 서브쿼리를 사용한 접근

채울 값의 조건은 세 가지다.

• 같은 keycol을 가진다.
• 현재 레코드보다 seq가 작다.
• val이 NULL이 아니다.

UPDATE OmitTbl
   SET val = (SELECT val
                FROM OmitTbl OT1
               WHERE OT1.keycol = OmitTbl.keycol -- 같은 keycol
                 AND OT1.seq = (SELECT MAX(seq)
                                  FROM OmitTbl OT2
                                 WHERE OT2.keycol = OmitTbl.keycol
                                   AND OT2.seq < OmitTbl.seq -- 자신보다 작은 seq
                                   AND OT2.val IS NOT NULL)) -- 값이 있는 레코드
 WHERE val IS NULL;

내가 이해한 흐름은 이렇다.

• 안쪽 서브쿼리: 자신보다 앞에 있고 값이 채워진 레코드 중 seq가 가장 큰 것, 즉 가장 가까운 레코드를 찾는다.
• 바깥 서브쿼리: 그 seq에 해당하는 레코드의 val을 가져온다.
• WHERE val IS NULL: 비어 있는 레코드만 갱신한다.

데이터가 늘어나면 (keycol, seq) 기본 키 인덱스를 활용할 가능성이 높아 반복계보다 성능이 좋을 수 있다.

레코드에서 필드로의 갱신

student_id(학생 ID)	subject(과목)	score(점수)
A001	영어	100
A001	국어	58
A001	수학	90
B002	영어	77
B002	국어	60
C001	영어	72
C003	국어	49
C003	사회	100

과목별로 한 행씩 저장된 ScoreRows의 점수를, 학생당 한 행에 score_en(영어), score_nl(국어), score_mt(수학) 필드를 가진 ScoreCols 테이블로 옮긴다. 점수가 없는 과목은 NULL로 둔다.

방법 1. 필드를 하나씩 갱신

UPDATE ScoreCols
   SET score_en = (SELECT score
                     FROM ScoreRows SR
                    WHERE SR.student_id = ScoreCols.student_id
                      AND subject = '영어'),
       score_nl = (SELECT score
                     FROM ScoreRows SR
                    WHERE SR.student_id = ScoreCols.student_id
                      AND subject = '국어'),
       score_mt = (SELECT score
                     FROM ScoreRows SR
                    WHERE SR.student_id = ScoreCols.student_id
                      AND subject = '수학');

간단하고 명확하지만 상관 서브쿼리를 3개 실행해야 한다. 갱신할 과목이 늘어날수록 서브쿼리도 늘어나 성능이 더 나빠진다.

방법 2. 다중 필드 할당

여러 필드를 리스트로 묶어 한 번에 갱신하는 다중 필드 할당(Multiple Fields Assignment)을 사용한다.

UPDATE ScoreCols
   SET (score_en, score_nl, score_mt)
     = (SELECT MAX(CASE WHEN subject = '영어'
                        THEN score
                        ELSE NULL END) AS score_en,
               MAX(CASE WHEN subject = '국어'
                        THEN score
                        ELSE NULL END) AS score_nl,
               MAX(CASE WHEN subject = '수학'
                        THEN score
                        ELSE NULL END) AS score_mt
          FROM ScoreRows SR
         WHERE SR.student_id = ScoreCols.student_id);

서브쿼리가 하나로 합쳐져 코드가 간단해지고, 갱신할 필드가 늘어나도 서브쿼리 수가 늘지 않는다.

• 테이블 접근이 3회에서 1회로 줄어든다.
• 대신 유일 검색(INDEX UNIQUE SCAN)이 범위 검색(INDEX RANGE SCAN)으로 바뀌고, MAX 함수를 위한 정렬이 추가된다.

여기서 중요한 것은 MAX 함수다. 한 학생의 레코드가 과목별로 여러 개이므로, 집약해서 하나의 행으로 만들어야 한다.

NOT NULL 제약이 있는 경우

갱신 대상 테이블(ScoreColsNN)의 점수 필드에 NOT NULL 제약이 있다면 두 가지를 추가한다.

UPDATE ScoreColsNN
   SET (score_en, score_nl, score_mt)
     = (SELECT COALESCE(MAX(CASE WHEN subject = '영어'
                                 THEN score
                                 ELSE NULL END), 0) AS score_en,
               COALESCE(MAX(CASE WHEN subject = '국어'
                                 THEN score
                                 ELSE NULL END), 0) AS score_nl,
               COALESCE(MAX(CASE WHEN subject = '수학'
                                 THEN score
                                 ELSE NULL END), 0) AS score_mt
          FROM ScoreRows SR
         WHERE SR.student_id = ScoreColsNN.student_id)
 WHERE EXISTS (SELECT *
                 FROM ScoreRows
                WHERE student_id = ScoreColsNN.student_id);

• COALESCE: 점수가 없는 과목의 NULL을 0으로 바꾼다.
• EXISTS: 두 테이블에 모두 존재하는 student_id만 갱신 대상으로 삼는다.

같은 테이블의 다른 레코드로 갱신

brand(종목)	sale_date(거래일)	price(종가)
A철강	2008-07-01	1000
A철강	2008-07-04	1200
A철강	2008-08-12	800
B상사	2008-06-04	3000
B상사	2008-09-11	3000

이 Stocks 테이블에 trend 필드를 추가한 Stocks2 테이블을 만든다. trend는 직전 거래일의 종가와 비교해 올랐으면 ‘↑’, 내렸으면 ‘↓’, 같으면 ’→’이고, 종목별 첫 거래일은 비교 대상이 없으므로 NULL이다.

방법 1. 상관 서브쿼리

INSERT INTO Stocks2
SELECT brand, sale_date, price,
       CASE SIGN(price -
                 (SELECT price
                    FROM Stocks S1
                   WHERE brand = Stocks.brand
                     AND sale_date =
                         (SELECT MAX(sale_date)
                            FROM Stocks S2
                           WHERE brand = Stocks.brand
                             AND sale_date < Stocks.sale_date)))
            WHEN -1 THEN '↓'
            WHEN 0 THEN '→'
            WHEN 1 THEN '↑'
            ELSE NULL
       END
  FROM Stocks;

SIGN 함수는 양수면 1, 음수면 -1, 0이면 0을 반환한다.

실행 계획에는 기본 키 인덱스에 대한 인덱스 온리 스캔, 기본 키 인덱스를 사용한 테이블 접근, 테이블 풀 스캔이 모두 나타난다. 테이블 접근 횟수를 줄이면 더 개선할 수 있다.

방법 2. 윈도우 함수

INSERT INTO Stocks2
SELECT brand, sale_date, price,
       CASE SIGN(price -
                 MAX(price) OVER (PARTITION BY brand
                                      ORDER BY sale_date
                                       ROWS BETWEEN 1 PRECEDING
                                                AND 1 PRECEDING))
            WHEN -1 THEN '↓'
            WHEN 0 THEN '→'
            WHEN 1 THEN '↑'
            ELSE NULL
       END
  FROM Stocks S2;

실행 계획이 훨씬 단순해지고, Stocks 테이블 접근도 풀 스캔 한 번으로 줄어든다.

INSERT SELECT와 UPDATE 비교

INSERT SELECT의 장점은 다음과 같다.

• UPDATE보다 성능이 좋아 고속 처리를 기대할 수 있다.
• 참조 대상 테이블과 갱신 대상 테이블이 다르므로, 자기 참조를 허용하지 않는 데이터베이스에서도 사용할 수 있다.

단점은 같은 크기와 구조의 데이터를 두 벌 만들어야 해서 저장소 용량을 2배 이상 쓴다는 것이다.

Stocks2를 테이블이 아니라 뷰로 만드는 방법도 있다. 저장소 용량을 절약하고 정보를 항상 최신으로 유지할 수 있지만, 뷰에 접근할 때마다 복잡한 연산이 수행되어 Stocks2를 조회하는 쿼리의 성능이 낮아진다. 결국 성능과 동기성의 트레이드오프다.