23강 - 레코드에 순번 붙이기
기본 키가 한 개의 필드일 경우
•
윈도우 함수를 사용
ROW_NUMBER를 이용한다.
•
상관 서브쿼리를 사용
MySQL과 같이 ROW_NUMBER 함수를 사용할 수 없는 경우, 상관 서브쿼리를 사용한다.
위의 서브쿼리는 재귀 집합을 만들고 요소 수를 COUNT 함수로 센다.
•
윈도우 함수를 사용하는 것이 성능 측면에서 좋다.
◦
윈도우 함수: 스캔 횟수가 1회이고, 인덱스 온리 스캔을 사용하므로 테이블에 직접적인 접근을 피한다.
◦
상관 서브쿼리: W1과 W2, 총 2회의 스캔이 실행된다.
기본 키가 여러 개의 필드로 구성되는 경우
학급(class)와 학생 ID(student_id)가 기본 키인 경우
테이블 정의
•
윈도우 함수를 사용
ROW_NUMBER를 사용하는 경우, ORDER BY의 키에 필드를 추가하면 된다.
•
상관 서브쿼리를 사용
다중 필드 비교를 사용한다.
◦
필드 자료형을 원하는대로(숫자와 문자열, 문자열과 숫자 등) 지정할 수 있다.
◦
암묵적인 자료형 변환도 발생하지 않으므로 기본 키 인덱스도 사용할 수 있다. → ??
◦
필드가 3개 이상일 때도 간단하게 확장할 수 있다.
그룹마다 순번을 붙이는 경우
테이블을 그룹(학급)으로 나누고 그룹마다 내부 레코드에 순번을 붙이는 경우
•
윈도우 함수를 사용
클래스 필드에 PARTITION BY를 적용한다.
•
상관 서브쿼리를 사용
순번과 갱신
갱신에서 순번을 매기는 경우
테이블 정의
•
윈도우 함수를 사용
순번 할당 쿼리를 SET 구로 넣는다. ROW_NUMBER를 쓸 경우에는 서브쿼리를 함께 사용해야 한다.
UPDATE "08_Weight3"
SET seq=(SELECT seq
FROM (SELECT class,
student_id,
ROW_NUMBER() OVER (PARTITION BY class ORDER BY student_id) AS seq
FROM "08_Weight3") SeqTb1
WHERE "08_Weight3".class = SeqTb1.class
AND "08_Weight3".student_id = SeqTb1.student_id);
SQL
복사
•
상관 서브쿼리를 사용
UPDATE "08_Weight3"
SET seq = (SELECT COUNT(*)
FROM "08_Weight3" W2
WHERE W2.class = "08_Weight3".class
AND W2.student_id <= "08_Weight3".student_id);
SQL
복사
24강 - 레코드에 순번 붙이기 응용
중앙값 구하기
•
집합 지향적 방법
SELECT AVG(weight)
FROM (SELECT W1.weight
FROM "08_Weight" W1,
"08_Weight" W2
GROUP BY W1.weight
HAVING SUM(CASE WHEN W2.weight >= W1.weight THEN 1 ELSE 0 END) >= COUNT(*) / 2
AND SUM(CASE WHEN W2.weight <= W1.weight THEN 1 ELSE 0 END) >= COUNT(*) / 2) TMP;
SQL
복사
CASE 식에 표현한 2개의 특성 함수로 모집합 Weights를 상위 집합과 하위 집합으로 분할한다.
특성 함수
◦
단점 2가지
▪
코드가 복잡해서 가독성이 떨어진다.
▪
성능이 나쁘다.
Nested Loops가 수행되는데 W1과 W2는 모두 같은 테이블이므로 자기 결합이 수행된다.
⇒ 윈도우 함수를 쓰자 !
절차 지향적 방법 1: 양쪽 끝에서 레코드 하나씩 세어 중간을 찾음
SELECT AVG(weight) AS median
FROM (SELECT weight,
ROW_NUMBER() OVER (ORDER BY weight , student_id) AS hi,
ROW_NUMBER() OVER (ORDER BY weight DESC, student_id DESC) AS lo
FROM "08_Weight") TMP
WHERE hi IN (lo, lo + 1, lo - 1);
SQL
복사
•
홀수와 짝수일 경우의 조건 분기를 IN 연산자로 한꺼번에 수행한다.
•
주의점
◦
순번을 붙일 때 반드시 ROW_NUMBER 함수를 사용해야 한다. 비슷한 RANK 또는 DENSE_RANK 함수는 7위 다음에 9위라던지 11위가 두 명이 될 수 있다.
◦
ORDER BY의 정렬 키에 weight 필드 뿐만 아니라 기본 키인 student_id도 포함해야 한다. ⇒ 왜 ???
•
테이블에 대한 접근이 1회로 감소하고 결합이 사용되지 않는 대신 정렬이 2회로 늘었다. 2개의 ROW_NUMBER에서 사용하는 정렬 순서가 오름차순과 내림차순으로 다르기 때문이다.
절차 지향적 방법 2: 2 빼기 1은 1
SELECT AVG(weight)
FROM (SELECT weight,
2 * ROW_NUMBER() OVER (ORDER BY weight) - COUNT(*) OVER () AS diff
FROM "08_Weight") TMP
WHERE diff BETWEEN 0 AND 2;
SQL
복사
•
정렬을 한 번만 사용하므로 성능이 좋다.
순번을 사용한 테이블 분할
데이터를 특정 기준으로 그룹화해보자 - 단절 구간 찾기
1부터 시작하는 순번 테이블이 있다. 일련의 비어있는 숫자를 출력해보자.
테이블 정의
•
집합 지향적 방법
SELECT (N1.num + 1) AS gap_start,
'~',
(MIN(N2.num) - 1) AS gap_end
FROM "08_numbers" N1
INNER JOIN "08_numbers" N2
ON N2.num > N1.num
GROUP BY N1.num
HAVING (N1.num + 1) < MIN(N2.num); --HAVING구: 단절이 있다는 것을 의미
SQL
복사
집합 지향적인 방법은 반드시 자기 결합을 사용해야 한다. 위의 실행 계획에서도 N1과 N2에 Nested Loops로 결합이 일어난다.
•
절차 지향적 방법 - 다음 레코드와 비교
윈도우 함수로 현재 레코드의 다음 레코드를 구하고, 이들 두 레코드의 숫자 차이를 diff 필드에 저장해 연산한다.
SELECT num + 1 AS gap_start,
'~',
(num + diff - 1) AS gap_end
FROM (SELECT num,
MAX(num)
OVER (ORDER BY num
ROWS BETWEEN 1 FOLLOWING
AND 1 FOLLOWING) - num
FROM "08_numbers") TMP(num, diff)
WHERE diff <> 1;
SQL
복사
“08_numbers” 테이블에 접근이 1번만 발생하고 윈도우 함수에서 정렬이 실행된다. 결합을 사용하지 않으므로 성능이 더 안정적이다.
테이블에 존재하는 시퀀스 구하기
•
집합 지향적 방법
SELECT MIN(num) AS low,
'~',
MAX(num) AS high
FROM (SELECT N1.num,
COUNT(N2.num) - N1.num
FROM "08_numbers" N1
INNER JOIN "08_numbers" N2
ON N2.num <= N1.num
GROUP BY N1.num) N(num, gp)
GROUP BY gp;
SQL
복사
◦
N1, N2에 자기 결합을 수행하고, 극치 함수(MIN 또는 MAX)로 집약(GroupAggregate)을 수행한다. 이때 정렬 대신 해시(HashAggregate)가 사용된다고 하는데 직접 돌려본 결과 GroupAggregate를 수행한다.
HashAggregate와 GroupAggregate
•
절차 지향적 방법 - 다음 레코드 하나와 비교
SELECT low, high
FROM (SELECT low,
CASE
WHEN high IS NULL
THEN MIN(high)
OVER (ORDER BY seq ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
ELSE high END AS high
FROM (SELECT CASE
WHEN COALESCE(prev_diff, 0) <> 1
THEN num
ELSE NULL END AS low,
CASE
WHEN COALESCE(next_diff, 0) <> 1
THEN num
ELSE NULL END AS high,
seq
FROM (SELECT num,
MAX(num) OVER (ORDER BY num ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) - num AS next_diff,
num - MAX(num) OVER (ORDER BY num ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_diff,
ROW_NUMBER() OVER (ORDER BY num) AS seq
FROM "08_numbers") TMP1) TMP2) TMP3
WHERE low IS NOT NULL;
SQL
복사
현재 레코드와 전후의 레코드를 비교한다.
◦
Subquery Scan on tmp1: 서브쿼리의 결과를 일시 테이블(메모리)에 전개한다. 이 때 일시 테이블의 크기가 크면 비용이 높아질 가능성이 있다. 즉, 절차 지향 쿼리가 집합 지향 쿼리에 비해 좋은지는 서브쿼리의 크기에도 의존한다.
25강 - 시퀀셜 객체. IDENTITY 필드, 채번 테이블
표준 SQL에는 순번을 다루는 기능으로 시퀀셜 객체와 IDENTITY 필드가 있다. 비교적 새로운 기능이고, 현재 대부분의 구현에서 사용할 수 있다.
모두 최대한 사용하지 않도록 하고, 사용하게 된다면 IDENTITY 필드보다는 시퀀셜 객체를 사용하자.
시퀀셜 객체
테이블 또는 뷰처럼 스키마 내부에 존재하는 객체.
시퀀스 객체 정의 예
•
시퀀스 객체의 문제점
◦
표준화가 늦어서, 구현에 따라 구문이 달라 이식성이 없고, 사용할 수 없는 구현도 있다.
◦
시스템에서 자동으로 생성되는 값이므로 실제 엔티티 속성이 아니다.
◦
성능적인 문제를 일으킨다. ⇒ 실무에서 무시할 수 없는 문제
•
시퀀스 객체가 생성하는 순번이 가지는 특성 3가지
◦
유일성: 중복값이 생성되지 않는다.
◦
연속성: 생성된 값에 비어있는 부분이 없다.
◦
순서성: 순번의 대소 관계가 역전되지 않는다.
시퀀스 객체는 기본 설정에서 위 3가지 성질을 모두 만족하는 순번을 생성한다. 따라서 동시 실행 제어를 위해 배타 락을 수행한다. 따라서 동시에 여러 사용자가 시퀀스 객체에 접근하는 경우 락 충돌로 인해 성능 저하 문제가 발생한다. 또한 어떤 사용자가 연속적으로 시퀀스 객체를 사용하는 경우 오버 헤드가 발생한다.
예시
•
시퀀스 객체로 발생하는 성능 문제의 대처 방법(옵션) 2가지
◦
CACHE: 새로운 값이 필요할 때마다 메모리에 읽어들일 필요가 있는 값의 수를 설정하는 옵션. 이 값을 크게 하면 접근 비용을 줄일 수 있다. 단, 부작용으로 시스템 장애가 발생할 때 연속성을 담보할 수 없다.
◦
NOORDER: 순서성을 담보하지 않아서 오버 헤드를 줄인다. 단, 순서성을 담보할 수 없다.
•
순번을 키로 사용할 때의 성능 문제
순번처럼 비슷한 데이터를 연속으로 INSERT하면 물리적으로 같은 영역에 저장된다. 이 때 저장소의 특정 물리적 블록(Hot Spot)에만 I/O 부하가 커지므로 성능 악화가 발생한다.
◦
이에 대한 대처 방법 2가지
▪
연속된 값을 도입하는 경우라도 DBMS 내부에서 변화를 주어 제대로 분산할 수 있는 구조(일종의 해시)를 사용한다.
▪
인덱스에 일부러 복잡한 필드를 추가해서 데이터의 분산도를 높인다.
INSERT 구문 자체는 빨라지지만 범위 검색 등에서 I/O 양이 늘어나 SELECT 구문의 성능이 나빠질 수 있다.
시퀀스 객체는 최대한 사용하지 말아야 하며, 사용한다면 어떤 위험이 있는지 확실하게 인지해야 한다.
IDENTITY 필드
자동 순번 필드. 테이블의 필드로 정의하고, 테이블에 INSERT가 발생할 때마다 자동으로 순번을 붙여준다.
시퀀스 객체는 적어도 CACHE, NOORDER를 지정할 수 있지만 IDENTITY 필드는 구현에 따라 이들을 아예 사용할 수 없거나 제한적으로 사용할 수 있다.
채번 테이블
예전에 사용되던 방식. 테이블에서 유사적으로 시퀀스 객체를 구현한 것으로, 시퀀스 객체의 락 메커니즘을 테이블을 활용해 직접 구현했다. 성능이 제대로 나오지도 않고, 시퀀스 객체의 CACHE와 NOORDER 같은 개선 방법도 없다.