SQL 레벨업 9장 , 10장

9장 갱신과 데이터 모델

 

26강 갱신을 효율적으로

1. NULL 채우기

UPDATE OmitTbl
	SET val = (SELECT val
		FROM OmitTbl	OT1
		WHERE OT1.keycol = (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;

 

 OT2 테이블 스캔의 결과를 MAX 함수로 집약하고 OT1 테이블의 레코드를 특정

데이터 양이 늘어나면 기본 키 인덱스를 사용해서 풀 스캔보다 효율적으로 접근

 

2. 반대로 NULL을 설정

UPDATE OmitTbl
	SET val = CASE WHEN val
		= (SELECT val
		FROM OmitTbl 01
		WHERE 01.keycol = OmitTbl.keycol
		AND 01.seq
		= (SELECT MAX(seq)
		FROM OmitTbl 02
		WHERE 02.keycol = OmitTbl.keycol
		AND 02.seq < OmitTbl.seq))
 	THEN NULL
 	ELSE val END;

 

이전 레코드와 같은 값이면 NULL 값으로 채우기

조건1~3에 해당하면 NULL 입력, 해당하지 않으면 val 값 입력

스칼라 서브쿼리(서브쿼리가 하나의 값을 반환) 로 CASE 문의 매개변수로 사용이 가능

 

 

27강 레코드에서 필드로의 갱신

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.다중 필드 할당

여러 개의 필드는 리스트화해서 한번에 갱신

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);

테이블 접근 횟수 감소로 인한 성능 향상, 코드 간결화

다른 과목에서 반환된 NULL값과 해당 과목의 점수들이 반환되므로 그중 실제 사용할 해당 과목의 점수 값을 가져오기 위해서 CASW문에서 MAX 처리를 함

 

3. NOT NULL 제약이 걸려있는 경우 

1) UPDATE 구문 사용

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

테이블 사이에 일치하지 않는 레코드는 제거하고 일치하는 레코드만 갱신

학생은 존재하지만 과목이 없는 경우 : COALESCE 함수로 NULL을 1로 변경

 

2) 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 studend_id) SR
			ON	(ScoreColsNN.student_id = SR.student_id)
	WHEN MATCHED THEN
	UPDATE SET ScoreColsNN.score_en = SR.score_en,
			coreColsNN.score_nl = SR.score_nl,
			ScoreColsNN.score_mt = SR.score_mt;

 

ON 구로 결합 조건을 하나로 묶어서 코드 간결화

MERGE 구문은 INSERT 와 UPDATE 를 한번에 실행하기 위한 구문

 

28강 필드에서 레코드로 변경

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);

CASE 식을 사용해 3개의 필드 내용을 레코드에 하나씩 넣음

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

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 < Stock.sale_date)))
	WHEN -1 THEN '아래화살표'
	WHEN 0 THEN '오른쪽화살표'
	WHEN 1 THEN '위쪽화살표'
	ELSE NULL
	END
FROM Stocks S2;

상관 서브 쿼리 때문에 테이블에 여러번 접근해야함

 

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;

 

매우 간단하고 효율적

 

3. INSERT 와 UPDATE 비교

INSERT 장점

- UPDATE에 비해 성능적으로 나으므로 고속 처리를 기대 가능

- 갱신 SQL에서 자기 참조 허용하지 않는 데이터베이스에서도 사용 가능

INSERT 단점

- 같은 크기와 구조를 가진 데이터를 두개씩 만들어 저장소 용량 2배 이상 소비

 

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

1. 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), -- SELECT 하기 위해 MAX를 사용
	MAX(ORC.delivery_date - O.order_date) AS max_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;

 

2. 모델 갱신을 사용하는 방법

 쿼리를 통해 찾는것이 아니라 필드를 하나 추가해 해결

 

31강 모델 갱신의 주의점

1. 높아지는 갱신 비용

SQL 구문을 사용해 검색하는 부하를 갱신 부하로 변경

상황에 따라 갱신 비용 증가

 

2. 갱신까지의 타임 랙 (time rag)

갱신까지의 시간차이 발생

3. 모델 갱신 비용 발생

 모델의 수정은 큰 수정 요구됨

 

32강 시야협착 : 관련문제

1. 다시 SQL을 사용한다면

1. 집약함수 사용

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;

 

2. 윈도우 함수 사용

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;

 

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

주문 변경이 일어나는 경우의 처리를 고려해야함

 

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

어려운 문제를 어렵고 복잡하게 풀려고 하기보단 다양한 해결방법 찾아야함

 

10장 인덱스 사용

34강 인덱스와 B-tree

1. B- tree 인덱스

트리구조로 데이터를 저장하는 인덱스

가장 많이 사용됨

리프노드에만 키값을 저장

 

2. 기타 인덱스

- 비트맵 인덱스 : 데이터를 비트 플래그로 변환해서 저장하는 인덱스. 카디널리티가 낮은 필드에 효과를 발휘

- 해시 인덱스 : 검색 외에 효과가 없어서 거의 사용하지 않음

 

35강 인덱스를 잘 활용하려면

1. 카디널리티(값의 균형)

- 모든 레코드에 다른 값이 들어가있는 유일 키 필드 -> 카디널리티가 높다

- 모든 레코드에 같은 값이 들어가 있음 -> 카디널리티가 낮다

2.선택률 (전체중에서 몇개가 선택되는지)