SQL의 대부분은 SELECT 구문이라고 할 수 있다.
반면 UPDATE, DELETE라는 갱신을 위한 기능은 상세하게 다뤄볼 기회가 거의 없다.
그 결과 갱신과 관련된 SQL 구문은 검색 SQL 구문 이상으로 비효율적으로 성능이 좋지 않은 방향으로 작성된다.
이번 장을 통해 갱신을 효율적으로 수행할 수 있는 SQL을 배워보자 !
1. NULL 채우기
| keycol | seq | val |
|---|---|---|
| A | 1 | 50 |
| A | 2 | |
| A | 3 | |
| B | 4 | 60 |
| B | 5 | |
| B | 6 | 72 |
| B | 7 | |
| B | 8 |
다음 테이블은 이전 레코드와 같은 값을 가질 경우 생략한 테이블이다.
여기에 값들을 채울 예정이다.
[val IS NULL] 로 입력해야 한다는 것은 쉽게 알 수 있다.
대부분 커서(cursor) 또는 호스트 언어로 레코드를 하나씩 읽고 반복문을 돌리는, 반복계를 사용한 접근법을 떠올릴 것이다.
-> 좋지 않은 방법이라는 것 또한 대부분 알고 있다.
상관 서브 쿼리를 사용한 접근
- 같은 keycol 필드를 가짐
- 현재 레코드보다 작은 seq 필드를 가짐
- val 필드가 NULL이 아님
UPDATE OmitTbl
SET val = (SELECT val
FROM OmitTb1 OT1
WHERE OT1.keycol = OmitTbl.keycol1 # 같은 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)) # NOT NULL
WHERE val IS NULL;내가 이해한 바로 다시 설명
- 자신보다 작은 seq, val != null => 값이 채워져있고, 내 전의 필드
- MAX(seq) -> 내 전의 필드 중 값이 있는 가장 가까운 필드
- 그 필드랑 OT1의 seq랑 똑같을 때
- WHERE val IS NULL -> 비어있는 값을 UPDATE
실행 계획
데이터양이 늘어날 경우 (keycol, seq)를 기본 키 인덱스로 활용할 확률이 높아 반복계에 비해 성능이 높을 수 있다.
레코드에서 필드로의 갱신
| student_id(학생 ID) | subject(과목) | score(점수) |
|---|---|---|
| A001 | 영어 | 100 |
| A001 | 국어 | 58 |
| A001 | 수학 | 90 |
| B002 | 영어 | 77 |
| B002 | 국어 | 60 |
| C001 | 영어 | 72 |
| C003 | 국어 | 49 |
| C003 | 사회 | 100 |
원하는 필드
| student_id(학생 ID) | score_en(영어 점수) | score_nl(국어 점수) | score_mt(수학 점수) |
|---|---|---|---|
| A001 | |||
| B002 | |||
| C001 | |||
| C003 | |||
| D004 |
알 수 없는 값은 NULL을 입력한다.
방법 1. 필드를 하나씩 갱신
UPDATE ScoreCols
SET score_en = (SELECT score
FROM ScoreRows SR
WHERE SR.student_id = ScoreColse.student_id
AND subject = '영어'),
score_nl = (SELECT score
FROM ScoreRows SR
WHERE SR.student_id = ScoreColse.student_id
AND subject = '국어'),
score_mt = (SELECT score
FROM ScoreRows SR
WHERE SR.student_id = ScoreColse.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 = ScoreColse.student_id);서브쿼리를 한꺼번에 처리할 수 있어 성능도 향상되고 코드도 간단해진다.
갱신해야 할 필드의 수가 늘어나도, 서브쿼리의 수가 늘어나지 않으므로 성능적으로 악화될 염려가 없다.
테이블 접근 3회 -> 1회로 감소
- 유일 검색(INDEX UNIQUE SCAN) -> 범위 검색(INDEX RANGE SCAN)
- MAX 함수의 정렬이 추가됨
- 스칼라 서브쿼리
서브쿼리 내부를 보면 CASE 식으로 과목별 점수를 검색한다. 이때 중요한 것은 MAX함수를 적용하는 것이다.
특정 학생의 레코드가 여러개일 수 있으므로 이렇게 집약해야 한다.
NOT NULL일 경우
UPDATE ScoreCols
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 = ScoreColse.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 |
| brand(브랜드) | sale_date(거래일) | price(종가) | trend(트랜드) |
|---|
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에 비해 성능적으로 나으므로 고속 처리를 기대할 수 있다.
- 자기 참조를 허가하지 않는 데이터베이스에서도 INSERT SELECT를 사용할 수 있다. (참조 대상 테이블과 갱신 대상 테이블이 서로 다른 테이블)
단점
- 같은 크기와 구조를 가진 데이터를 두 개 만들어야 한다.
-> 저장소 용량을 2배 이상 소비한다.
Stocks2 테이블을 뷰로 만드는 방법
- 저장소 용량을 절약할 수 있다.
- 정보를 항상 최신으로 유지할 수 있다.
-> 뷰에 접근이 발생할 때마다 복잡한 연산이 수행돼 Stocks2에 접근하는 쿼리의 성능이 낮아진다.
성능과 동기성의 트레이드오프
