갱신과 데이터 모델

갱신은 효율적으로

NULL 채우기

문제. NULL인 값을 이전 레코드의 값으로 채우기

UPDATE OmitTbl
   SET val = (
  SELECT val
  FROM OmitTbl OT1
  WHERE OT1.keycol = OmitTbl.keycol
    AND OT1.seq = (
      SELECT MAX(seq)
      FROM OmitTbl OT2
      WHERE OT2.keycol = OmitTbl.keycol
        AND OT2.seq < OmitTbl.seq
        AND OT2.val IS NOT NULL
    )
  )
WHERE val IS NULL

다음과 같은 조건을 갖는 레코드 집합을 구하고, 그 중 가장 큰 seq 필드를 가진 레코드를 찾음
1. 같은 keycol 필드를 가짐
2. 현재 레코드보다 작은 seq 필드를 가짐
3. val 필드가 NULL이 아님

반대로 NULL을 작성

문제. 위에서 채워진 값을 반대로 NULL로 바꾸기

UPDATE OmitTbl
   SET val = 
CASE WHEN val= (
  SELECT val
  FROM OmitTbl O1
  WHERE O1.keycol = OmitTbl.keycol
  AND O1.seq = (
    SELECT MAX(seq)
    FROM OmitTbl O2
    WHERE O2.keycol = OmitTbl.keycol
      AND O2.seq < OmitTbl.seq
      AND O2.val IS NOT NULL))
THEN NULL
ELSE val END

레코드에서 필드로의 갱신

문제. 레코드 기반 테이블에서 필드 기반 테이블로 데이터 이동

필드를 하나씩 갱신

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개의 상관 서브쿼리를 실행하므로 성능적으로 좋지 않음
과목 수가 늘어날 수록 성능 악화

다중 필드 할당

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)

테이블 접근은 1회로 감소, INDEX UNIQUE SCAN이 INDEX RANGE SCAN으로 변함, MAX 함수 정렬 추가
다중 필드 할당을 모든 SQL에서 지원하지는 않지만 표준 SQL 기능이므로 지원 가능성 높음
MAX 함수를 이용하여 서브쿼리로 단일한 값을 리턴

NOT NULL 제약이 걸려있는 경우

UPDATE 구문 사용

필드를 하나씩 갱신

UPDATE ScoreColsNN
SET score_en = COALESCE((
  SELECT score
  FROM ScoreRows
  WHERE student_id = ScoreColsNN.student_id
 AND subject = '영어'), 0),
        score_nl = COALESCE((
  SELECT score
  FROM ScoreRows
  WHERE student_id = ScoreColsNN.student_id
 AND subject = '국어'), 0),
        score_mt = COALESCE((
  SELECT score
  FROM ScoreRows
  WHERE student_id = ScoreColsNN.student_id
 AND subject = '수학'), 0)
 WHERE EXISTS (
SELECT *
FROM ScoreRows
WHERE student_id = ScoreColsNN.student_id)

다중 필드 할당

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으로 변경

MERGE 구문 사용

MERGE INTO ScoreColsNN
   USING (
SELECT student_id,
  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
GROUP BY student_id) SR
ON (ScoreColsNN.student_id = SR.student_id)
 WHEN MATCHED THEN
UPDATE SET ScoreColsNN.score_en = SR.score_en,
  ScoreColsNN.score_nl = SR.score_nl,
  ScoreColsNN.score_mt = SR.score_mt

UPDATE 사용 시 두 곳에 분산되어 있던 결합조건을 ON 구로 한 번에 처리할 수 있음
MERGE 구문은 UPDATE와 INSERT를 한 번에 시행하는 것이 목적이지만 UPDATE 또는 INSERT만 수행해도 문제는 없음

필드에서 레코드로 변경

갱신 대상 레코드를 subject 값에 따라 분기

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

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

문제. 이전 종가와 현재 종가를 비교해서 trend값 채우기

상관 서브쿼리 사용

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을 반환하는 부호 조사 함수

상관 서브쿼리 사용으로 인해 테이블 접근 횟수가 많음

윈도우 함수 사용

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

INSERT와 UPDATE 어떤 것이 좋을까?

INSERT SELECT의 장점
- UPDATE에 비해 우수한 성능
- MySQL처럼 갱신 SQL에서 자기 참조를 허가하지 않는 DB에서도 사용 가능
INSERT SELECT의 단점
- 같은 크기와 구조를 가진 데이터를 두 개 만들어야 하므로 저장소 용량을 2배 이상 소비

갱신이 초래하는 트레이드오프

문제. 주문마다 주문일(order_date)와 상품의 배송 예정일(delivery_date)의 차이를 구해, 그 차이가 3일 이상인 주문번호를 찾기

SQL을 사용하는 방법

주문일과 배송 예정일의 차이

SELECT O.order_id,
           O.order_name,
  	 ORC.delivery_date - O.order_date AS diff_days
FROM Orders O
		 INNER JOIN OrderReceipts ORC
   			ON O.order_id = ORC.order_id
 WHERE ORC.delivery_date - O.order_date >= 3

주문 단위로 집약

SELECT O.order_id
           MAX(O.order_name),
  	 MAX(ORC.delivery_date - O.order_date) AS diff_days
FROM Orders O
		 INNER JOIN OrderReceipts ORC
   			ON O.order_id = ORC.order_id
 WHERE ORC.delivery_date - O.order_date >= 3
 GROUP BY O.order_id

order_id와 order_name이 일대일 대응한다면 GROUP BY 구에 order_name을 추가해도 됨

모델 갱신을 사용하는 방법

SQL을 사용할 경우 검색 처리에 드는 비용이 높음
결합은 실행 계획의 변동 리스크가 있어 장기적으로 봤을 때 성능을 불안정하게 만듦
SQL을 사용하지 않고 Orders 테이블에 배송 지연 플래그를 추가하고 플래그를 갱신

모델 갱신의 주의점

높아지는 갱신비용

검색 부하를 갱신 부하로 바꿈
플래그 값이 변경되는 경우는 갱신 비용 상

갱신까지의 시간 랙(Time Rag) 발생

배송 예정일이 주문 등록 후에 갱신되는 경우 Orders 테이블의 배송 지연 플래그 필드와 OrderReceipts 테이블의 배송 예정일 필드가 실시간으로 동기화되지 않으므로 차이 발생
실시간성이 중요한 업무일수록 생신 간격이 짧아져야 함
완전한 실시간을 요구하는 경우 동일 트랜잭션으로 처리해야 하는데 성능과 실시간성 사이 트레이드오프 발생
모델 갱신비용 발생
RDB 데이터 모델 갱신은 코드 기반의 수정에 비해 대대적인 수정이 필요
갱신 대상 테이블을 사용하는 다른 처리에 문제가 발생할 가능성 존재
실제 운용에 들어가면 모델 변경은 거의 불가능
시야 협착 : 관련 문제
문제. 주문번호마다 몇 개의 상품이 주문되었는지 조회
다시 SQL을 사용한다면

집약 함수를 사용

SELECT O.order_id,
           MAX(O.order_name) AS order_name,
     MAX(O.order_date) AS order_date,
     COUNT(*) AS item_count
FROM Orders O
		 INNER JOIN OrderReceipts ORC
     		ON O.order_id = ORC.order_id
 GROUP BY O.order_id

윈도우 함수를 사용

SELECT O.order_id,
           O.order_name
     O.order_date
     COUNT(*) OVER (PARTITION BY O.order_id) AS item_count
FROM Orders O
		 INNER JOIN OrderReceipts ORC
     		ON O.order_id = ORC.order_id

두 방법 모두 실행 비용, 실행 계획은 비슷
가독성, 확장성 측면에서는 윈도우 함수가 좋음
다시 모델 갱신을 사용한다면
Orders 테이블에 상품 수를 추가
한 번 등록한 주문을 나중에 변경할 경우 수정 가능성 있음, 동기/비동기 문제 고려 필요
초보자보다 중급자가 경계해야
어려운 문제를 어려운 상태인 채 풀면 복잡한 코드 또는 프로그램이 만들어져 시스템 전체 관점에서 비효율이 발생할 수 있음
데이터 모델을 지배하는 자가 시스템을 지배한다
‘현명한 데이터 구조와 멍청한 코드의 조합’이 ‘멍청한 데이터 구조와 현명한 코드의 조합’보다 좋음

갱신과 데이터 모델

갱신은 효율적으로

NULL 채우기

반대로 NULL을 작성

레코드에서 필드로의 갱신

필드를 하나씩 갱신

다중 필드 할당

NOT NULL 제약이 걸려있는 경우

필드에서 레코드로 변경

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

상관 서브쿼리 사용

윈도우 함수 사용

INSERT와 UPDATE 어떤 것이 좋을까?

갱신이 초래하는 트레이드오프

SQL을 사용하는 방법

모델 갱신을 사용하는 방법

모델 갱신의 주의점

높아지는 갱신비용

갱신까지의 시간 랙(Time Rag) 발생

모델 갱신비용 발생

시야 협착 : 관련 문제

다시 SQL을 사용한다면

다시 모델 갱신을 사용한다면

초보자보다 중급자가 경계해야

데이터 모델을 지배하는 자가 시스템을 지배한다