효율적인 update
NULL 채우기
| key | seq | val |
|---|---|---|
| A | 1 | 50 |
| A | 2 | |
| B | 1 | 10 |
| B | 2 | 20 |
| B | 3 |
이렇게 이전의 레코드와 val 값이 같다면 null로 되어 있는 테이블이 있다. 이 NULL 값을 채워보자.
일단 고전적인 SQL 방식으로 생각하면, 상관 서브쿼리를 이용한 방법이 떠오른다.
아래처럼 해당 레코드를 찾아 이 레코드의 val의 값으로, null인 레코드를 채워주면 된다.
- 같은 key 값을 가짐
- 현재 레코드보다 seq 값을 가짐
- val 필드가 NULL이 아님
UPDATE OmitTbl
SET 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))
WHERE val IS 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))
THEN NULL
ELSE val END;레코드 → 필드로 이동
아래와 같이 과목별 점수를 레코드로 가지고 있는 테이블과,
| student_id | subject | score |
|---|---|---|
| A001 | 영어 | 100 |
| B002 | 국어 | 80 |
학생을 기준으로 학생별 모든 과목의 점수를 레코드로 가지고 있는 테이블이 있다고 가정하자.
| student_id | score_en | score_nl | score_mt |
|---|---|---|---|
| A001 | |||
| B002 |
과목별 점수가 레코드로 나타나는 위 테이블에서 과목별 점수를 필드로 갖는 테이블로 이동하려고 한다.
필드를 하나씩
아래처럼 하나씩 update하는 방식을 쉽게 생각할 수 있다.
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 = '수학');이 방법은 update하고 싶은 과목이 늘어날수록 실행해야하는 서브쿼리가 늘어나는 방식이므로 별로다.
다중 필드 할당
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);이렇게 하면 과목의 개수 상관없이 서브 쿼리 한 번에 처리할 수 있다는 장점이 있다.
하지만, 상관 서브쿼리가 하나로 정리된 대신, ScoreRows 테이블에 대한 접근이 범위 검색으로 변하고, MAX 함수의 정렬이 추가되는 트레이드 오프가 발생한다.
하지만 MAX 함수의 정렬은 과목 개수만큼 진행되니, 서브쿼리를 줄이는 것이 훨씬 효율적인 트레이드오프이다.
NOT NULL 제약
update 구문 사용
아래 SQL 구문은 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);첫 번째로, WHERE구에 EXISTS를 사용해 2개의 테이블 사이에 학생 ID가 일치하는 레코드로 한정하고 있다.
두 번째는 COALESCE 함수로 NULL을 0으로 변경해서 대응하고 있다.
MERGE 구문 사용
본래 MERGE 구문은 UPDATE와 INSERT를 한 번에 시행하려고 고안된 기술이다.
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;실행계획을 보면, ScoreRows 테이블에 풀 스캔 1회 + 정렬 1회가 필요하다.
필드 → 레코드 이동
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);같은 테이블의 다른 레코드로 update
| brand | sale_date | price(종가) |
|---|---|---|
| A철강 | 2008-07-12 | 1000 |
| B상사 | 2008-08-12 | 3000 |
위와 같은 테이블을 이용해 현재 비어 있는 아래의 테이블을 채우자.
trend 필드는 이전 종가와 현재 종가를 비교해, 화살표로 나타내자
| brand | sale_date | price | 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은 함수의 매개변수로 받은 숫자가 양수, 음수, 0인지에 따라 1, 0, -1을 반환하는 함수이다.
윈도우 함수 사용
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 vs UPDATE
INSERT & SELECT의 장점
- 일반적으로 UPDATE에 비해 성능적으로 낫다.
- update SQL에서 자기 참조를 허가하지 않는 DB에서 사용할 수 있다.
INSERT & SELECT의 단점
- 같은 크기와 구조를 가진 데이터를 두 개 만들어야 하는 것 ⇒ 저장소 용량을 2배 이상 소비
Stocks2를 뷰로 만들었을 때
- 정보를 항상 최신으로 유지 가능
- 저장소 용량 절약
- Stocks2 뷰에 접근이 발생할 때마다 복잡한 연산이 수행
update와 트레이드오프
Orders, OrderReceipts 이렇게 주문과 주문 명세 테이블이 있다.
Orders와 OrderReceipts는 일대다 관계이며, 주문마다 주문일, 상품의 배송 예정일을 이용해 차이가 3일 이상이면 배송 지연 연락을 보내려고 한다. 어떻게 해야 할까?
SQL 사용
연산에 이용해야 하는 주문일과 배송 예정일은 서로 다른 테이블에 존재하므로 join을 이용해야 한다.
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;모델을 변경하는 방법
사실 Orders 테이블에 0 / 1을 나타내는 배송 지연 플래그 필드를 추가한다면, 검색 쿼리는 그만큼 간단해진다.
모델 변경할 때 주의할 점
높아지는 비용
사실 orders 테이블에 플래그를 추가하는 처리가 필요하다. order 데이터를 넣을 때는 보통 배송 예정일이 정해져 있지 않은 경우가 많아 update를 해줘야 한다. 이는 update 비용이 올라가게 되는 꼴이 되어버린다.
update까지의 time rag 발생
데이터의 실시간성의 문제가 발생한다. OrderReceipts 테이블에서 주문 예정일을 업데이트 한 후, Orders의 배송 지연 플래그를 업데이트 하기 까지의 시간 차가 발생한다.
모델 갱신 비용 발생
RDB 데이터 모델 갱신은 대대적인 수정이 요구된다.대상 테이블에 연관된 로직 등이 많아 리스크가 큰 작업이라 거의 못한다고 볼 수 있다.
