레코드에 순번 붙이기 응용
레크드에 순번을 붙이는 방법은 앞에서 많이 정리해서 생략하고 응용부터 가도록 하겠다.
중앙값 구하기
| student_id(학생 ID) | weight(체중 kg) |
|---|---|
| A100 | 50 |
| A101 | 55 |
| A124 | 55 |
| B343 | 60 |
| B346 | 72 |
| B378 | 72 |
| C563 | 72 |
| C345 | 72 |
1. 양쪽 끝에서 숫자 세기
SELECT AVG(weight) AS median
FROM (SELECT weight,
ROW_NUMBER () OVER (ORDER BY weight ASC, student_id ASC) AS hi,
ROW_NUMBER () OVER (ORDER BY weight DESC, student_id DESC) AS lo
FROM Weights) TMP
WHERE hi IN (lo, lo+1, lo-1);오름차순, 내림차순한 뒤 같은 속도로 움직인다.
그때 lo와 hi가 똑같은 시점이 중앙값이다.
그런데 왜 lo+1, lo-1 도 있을까? -> 짝수일 경우 2개의 평균으로 구해야 하기 때문.
그래서 AVG도 있는 것이다. 홀수와 짝수의 조건 분기를 IN으로 한꺼번에 수행한 것이 인상깊다.
그러나 정렬 2회, 테이블 스캔 1회로 성능적으로 더 개선할 수 있다.
2. 2 빼기 1은 1
SELECT AVG(weight)
FROM (SELECT weight,
2 * ROW_NUMBER () OVER(ORDER BY weight)
- COUNT(*) OVER() AS diff
FROM Weights) TMP
WHERE diff BETWEEN 0 AND 2;순번을 2배 해 COUNT(*)를 빼주면 홀수개일 경우 1이 중앙값, 짝수개일 경우 0과 2가 중앙 값으로 AVG를 구해준다.
-> 알고리즘을 왜 배우나 알게되는 순간이었다.
이 쿼리의 실행 계획은 스캔 1회, 정렬 1회로 SQL 표준으로 중앙값을 구하는 가장 빠른 쿼리이다.
순번을 사용한 테이블 분할
- 단절 구간 찾기
| num(숫자) |
|---|
| 1 |
| 3 |
| 4 |
| 7 |
집합 지향적 방법
다음 번의 레코드와의 차이가 1이 아니면 단절된 구간이므로 그것을 표시하면 된다.
SELECT (N1.num + 1) AS gap_start,
'~',
(MIN(N2.num) -1) AS gap_end
FROM Numbers N1 INNER JOIN Numbers N2
ON N2.num > N1.num
GROUP BY N1.num
HAVING (N1.num + 1) < MIN(N2.num);N1.num 보다 큰 N2.num을 가져온 뒤, N2.num의 최솟값과 1 차이가 나는지 판단한다.
코드는 매우 간단하며 집향 지향적인 좋은 방법이다.
- 집합 지향적인 방법은 반드시 자기 결합을 사용해야 한다.
-> 쿼리의 비용이 높으며, 실행 계획 변동 위험을 안게 된다.
절차 지향적 방법
- 다음 레코드와 비교
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 Numbers) TMP(num, diff)
WHERE diff <> 1;윈도우 함수를 이용해 현재 레코드의 다음 레코드를 구한 뒤, 이들 두 레코드의 숫자 차이를 diff 필드에 저장해 연산한다.
FOLLOWING : 다음 행
diff : 다음 레코드[MAX(num)] - num <> 1 이면 연속이 아니다.
테이블 접근 1회 / 윈도우 함수에서 정렬 사용
-> 결합을 사용하지 않으므로 성능이 굉장히 안정적이다.
시퀀스 객체, IDENTITY 필드
모두 최대한 사용하지 않는 것이 좋다.
IDENTITY 필드보다는 시퀀스 객체를 사용하라.
시퀀스 객체 정의
CREATE SEQUENCE testseq
START WITH 1
INCREMENT BY 1
MAXVALUE 10000
MINVALUE 1
CYCLE;시퀀스 객체의 문제점
- 표준화가 늦어, 구현에 따라 구문이 달라 이식성이 없다.
- 시스템에서 자동으로 생성되는 값이므로 실제 엔티티 속성이 아니다.
- 성능적인 문제를 일으킨다.
시퀀스 객체는 유일성, 연속성, 순서성을 만족하기때문에 락(lock) 메커니즘이 필요하다.
어떤 사용자가 시퀀스 객체를 사용하고 있다면, 시퀀스 객체를 락해서 다른 사용자로부터의 접근을 블록하는 배타 제어를 수행한다.
따라서 락 충돌로 인해 성능 저하 문제가 발생하고, 연속적으로 사용하면 오버 헤드가 발생한다.
시퀀스 객체로 발생하는 성능 문제의 대처
옵션
-
CACHE : 새로운 값이 필요할 때마다 메모리에 읽어들일 필요가 있는 값의 수를 설정, 연속성을 담보할 수 없음 -> 장애가 발생하면 비어있는 숫자 발생
-
NOORDER : 순서성을 담보하지 않아 오버 헤드를 줄일 수 있다.
순번을 키로 사용할 때의 성능 문제
핫 스팟(Hot Spot) 문제
- 순번처럼 비슷한 데이터를 연속적으로 INSERT하면 물리적으로 같은 영역에 저장된다.
- 저장소의 특정 물리적 블록에만 I/O 부하가 커져 성능 악화가 발생한다.
- 이렇게 부하가 몰리는 부분을 'Hot Spot' or 'Hot Block'라고 한다.
대처
- 연속된 값을 도입하는 경우에 DBMS 내부에서 변화를 주어 분산할 수 있는 구조 (해시)를 사용하는 것
- 인덱스에 일부러 복잡한 필드를 추가해 데이터의 분산도를 높이는 방법
-> INSERT는 빨라지지만, I/O 양이 늘어나 SELECT 구문의 성능이 나빠진다.
시퀀스 객체는 최대한 사용하지 말아야 한다.
IDENTITY 필드
'자동 순번 필드'
테이블에 INSERT가 발생할 때마다 자동으로 순번을 붙여준다.
기능적, 성능적 측면에서 모두 시퀀스 객체보다 심각한 문제를 가진다.
- IDENTITY 필드는 특정한 테이블과 연결된다.
- 구현에 따라 이들을 아예 사용할 수 없거나 제한적으로만 사용할 수 있다.
