순서조작 기초 - 레코드에 순번 붙이기
1. 기본 키가 한 개의 필드일 경우
윈도우 함수 사용
SELECT student_id,
ROW_NUMBER() OVER (ORDER BY student_id) AS seq
FROM Weights;
SQL
복사
상관 서브쿼리 사용
SELECT student_id,
(SELECT COUNT(*)
FROM Weights W2
WHERE w2.student_id <= W1.student_id) AS seq
FROM Weights W1;
SQL
복사
결과
스캔 횟수가 1회이고 인덱스 온리 스캔을 사용해 직접적인 접근 회피하여 스캔 횟수가 2회인 상관 서브쿼리보다 성능이 좋음2. 기본 키가 여러 개의 필드로 구성되는 경우
윈도우 함수 사용
SELECT class, student_id,
ROW_NUMBER() OVER (ORDER BY class, student_id) AS seq
FROM Weights2;
SQL
복사
상관 서브쿼리 사용
SELECT class, student_id,
(SELECT COUNT(*)
FROM Weights2 W2
WHERE (W2.class, W2.student_id) <= (W1.class, W1.student_id) ) AS seq
FROM Weights2 W1;
SQL
복사
다중 필드 비교
•
복합적인 필드를 하나의 값으로 연결하고 한꺼번에 비교
•
숫자, 문자열, 문자열과 숫자 전부 가능
•
암묵적인 자료형 변환 발생하지 않아 기본 키 인덱스로 사용 가능
•
필드가 3개 이상일 때 확장 가능
결과
3. 그룹마다 순번 붙이는 경우
윈도우 함수 사용
SELECT class, student_id,
ROW_NUMBER() OVER (PARTITION BY class ORDER BY student_id) AS seq
FROM Weights2;
SQL
복사
상관 서브쿼리 사용
SELECT class, student_id,
(SELECT COUNT(*)
FROM Weights2 W2
WHERE W2.class = W1.class AND W2.student_id <= W1.student_id) AS seq
FROM Weights2 W1;
SQL
복사
결과
4. 순번과 갱신
갱신에서 순번 매기는 방법
seq 순번 필드를 갱신하는 UPDATE 구문 만들기
윈도우 함수 사용
UPDATE Weights3
SET seq = (SELECT seq
FROM ( SELECT class, student_id,
ROW_NUMBER() OVER (PARTITION BY class ORDER BY student_id) AS seq
FROM Weights3) SeqTbl
WHERE Weights3.class = SeqTbl.class
AND Weights3.student_id = SeqTbl.student_id);
SQL
복사
서브쿼리로 SeqTbl이라는 테이블을 만들어 class 그룹마다 순번 매긴 값을 seq 컬럼에 업데이트
•
윈도우 함수로 각 클래스별 학생의 순번을 매김
•
그렇게 매긴 순번 테이블을 기존 테이블과 클래스별 학생 아이디 기준으로 조인해서 순번 칼럼을 업데이트
상관 서브쿼리 사용
UPDATE Weights3
SET seq = (SELECT COUNT(*)
FROM Weights3 W2
WHERE W2.class = Weights3.class
AND W2.student_id <= Weights3.student_id);
SQL
복사
결과
레코드에 순번 붙이기 응용
순번의 성질 연속성, 유일성을 이용하여 다양한 테크닉 사용 가능
1. 중앙값 구하기
중앙값: 숫자를 정렬하고 양쪽 끝부터 수를 셀 때 정중앙에 오는 값,
평균값에 비해 outlier에 영향 받지 않는다는 장점 존재
•
중앙값 구하는 방법
레코드 개수가 홀수일 때: 중앙의 값을 사용
레코드 개수가 짝수일 때: 중앙의 두 값을 평균내어 사용
집합 지향적 방법
테이블을 상위 집합과 하위 집합으로 분할하고 공통 부분을 검색하는 방법
SELECT AVG(weight)
FROM (SELECT W1.weight
FROM Weights W1, Weights w2
GROUP BY W1.weight
-- Set 1 : 하위 집합의 조건
HAVING SUM(CASE WHEN W2.weight>=W1.weight THEN 1 ELSE 0 END) >= COUNT(*)/2
-- Set 2 : 상위 집합의 조건
AND SUM(CASE WHEN W2.weight<=W1.weight THEN 1 ELSE 0 END) >= COUNT(*)/2 ) TMP;
SQL
복사
1) HAVING 구와 CASE 식의 함수로 상위 집합과 하위 집합 분할
2) AVG 함수를 사용해 레코드 수가 짝수일 때는 평균 처리되도록 함
코드가 복잡해 이해하기 어렵고 자기 결합을 수행하여 성능이 나쁨
절차 지향적 방법 (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);
SQL
복사
만약 홀수일 경우 hi=lo가 될 것이고 짝수일 경우 hi 값은 lo-1, lo+1 중 하나가 될 것이므로 IN 연산자로 한꺼번에 비교
주의 1) RANK, DENSE_RANK 대신 ROW_NUMBER 함수를 사용해야 레코드 집합에 자연수 할당해서 연속성과 유일성 가질 수 있음
집합 지향적 방법과 비교할 때 결합이 제거되었고 정렬이 1회 늘어났으므로 성능 개선되었다고 볼 수 있음
절차 지향적 방법 (2)
SELECT AVG(weight) AS median
FROM (SELECT weight,
2 * ROW_NUMBER() OVER (ORDER BY weight) - COUNT(*) OVER() AS diff
FROM Weights) TMP
WHERE diff BETWEEN 0 AND 2;
SQL
복사
ROW_NUMBER() == (모든 레코드 개수의 절반 ± 1) 이 될 때 중간 값이라고 볼 수 있음
절차 지향적 방법 (1)과 비교할 때 정렬이 1회 줄어듦
2. 순번을 사용한 테이블 분할
테이블을 여러 그룹으로 분할하기
단절 구간 찾기
비어있는 숫자를 다음과 같이 처럼 출력하기
집합 지향적 방법: 집합의 경계선
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);
SQL
복사
N2.num을 사용해 현재 레코드 값 N1.num 보다 큰 숫자의 집합을 조건으로 지정
min(N2.num)으로 N1.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;
SQL
복사
레코드의 순서를 활용하여 현재 레코드 값(num)과 다음 레코드의 숫자 차이(diff)를 비교
정렬이 사용되지만 결합을 사용하지 않아 성능이 안정적임
3. 테이블에 존재하는 시퀀스 구하기
테이블에 존재하는 수열 그룹화
집합 지향적 방법: 집합의 경계선
SELECT MIN(num) AS low,
'~'
MAX(num) AS high
FROM (SELECT N1.num,
COUNT(N2.num) - N1.num
FROM Numbers N1 INNER JOIN Numbers N2 ON N2.num <= N1.num
GROUP BY N1.num) N(num, gp)
GROUP BY gp;
SQL
복사
자기 결합으로 num 필드 조합 만든 후 MIN, MAX 값으로 경계를 구함
(?) gp는 뭐하는 값인지 모르겠다.
결과
자기 결합 수행 후 극치 함수(MIN, MAX)로 집약 수행
절차 지향형 방법: 다음 레코드 하나와 비교
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) - num AS prev_diff,
ROW_NUMBER() OVER (ORDER BY num) As seq
FROM numbers) TMP1) TMP2) TMP3
WHERE low IS NOT NULL;
SQL
복사
실행과정
1) TMP 1 서브쿼리
현재 레코드와 전후 레코드의 차이 구해 prev_diff, next_diff 저장
차이가 1보다 크면 비어있는 부분이 존재한다는 것
2) TMP 2 서브쿼리
CASE 식으로 차이가 1보다 큰지 확인해 각 시퀀스의 양쪽 지점이 되는 값(low, high 필드) 계산
3) TMP 3 서브쿼리
high값이 없는 레코드에 현재 레코드 이후의 레코드들을 돌며 가장 작은 high값 가져옴
low값이 없는 레코드는 무시
서브쿼리의 크기에 따라 중간 결과를 메모리에 유지할지 저장소를 사용할지 결정되므로 성능 측면에서 집합 지향 쿼리에 비해 좋은지 알 수 없음
시퀀스 객체, IDENTITY 필드, 채번 테이블
•
최대한 사용하지 않기
•
사용한다면 IDENTITY보다 시퀀스 객체를 사용하기
1. 시퀀스 객체
•
테이블 또는 뷰처럼 스키마 내부에 존재하는 객체 중 하나
•
CREATE문으로 생성
CREATE SEQUENCE testseq
START WITH 1
INCREMENT BY 1
MAXVALUE 100000
MINVALUE 1
CYCLE;
SQL
복사
•
초깃값, 증가값, 최댓값, 최솟값, 최댓값에 도달했을 때 순환 유무 등의 옵션을 지정 가능
•
시퀀스 객체가 생성하는 순번은 유일성, 연속성, 순서성을 가짐
시퀀스 객체의 문제점
•
표준화가 늦어 구현에 따라 구문이 다름 → 이식성 없고 사용할 수 없는 구현도 있음
•
시스템에서 자동 생성된 값으로 실제 엔티티 속성이 아님
•
성능적 문제 발생
시퀀스 객체로 발생하는 성능 문제
사용자가 시퀀스 객체에서 NEXT VALUE 검색할 때 처리 과정은 아래와 같음
1) 시퀀스 객체에 배타 락 적용
위와 같은 과정으로 인해 동시에 여러 사용자가 시퀀스 객체에 접근할 경우 락 충돌으로 인한 성능 저하 문제 발생
대처법
CACHE, NOORDER 객체로 성능 문제 완화 가능
•
CACHE
새로운 값이 필요할때마다 메모리에 읽어들일 필요가 있는 값의 수를 설정하는 옵션
값이 클수록 접근 비용 줄일 수 있음
시스템 장애 발생 시 연속성을 담보할 수 없음
•
NOORDER
순서성을 담보하지 않아 오버헤드 줄임
순번을 키로 사용할 때 성능 문제
DBMS의 저장 방식으로 인해 순번처럼 비슷한 데이터 연속으로 INSERT 시 물리적으로 같은 영역에 저장되어 특정 물리적 블록에만 I/O 부하 커져 성능 저하
핫 스팟: I/O 부하가 몰리는 특정 물리적 블록
대처법
1) 일종의 해시와 같이 DBMS 내부에서 연속된 값을 변화를 주어 제대로 분산할 수 있도록 구조 변경
-> I/O양이 늘어나 SELECT 구문 성능이 나빠질 수 있으며 구현의존적 방법임
2) 인덱스에 복잡한 필드를 추가하여 데이터의 분산도를 높임
-> 복잡한 필드 추가할 경우 불필요한 의미를생성하므로 다른 개발자가 이해하기 어려울 수 있음
결론: 리스크를 인지하고 사용하자
2. IDENTITY 필드
•
테이블의 필드로 정의하고 테이블에 INSERT 발생할 때마다 자동으로 순번 붙여줌
•
특정한 테이블과 연결되어 여러 테이블에서 사용 불가
•
CACHE, NOORDER를 지정할 수 없거나 제한적으로만 사용 가능
3. 채번 테이블
•
과거 사용하던 순번 생성하는 전용 테이블
•
테이블를 활용해 유사적으로 시퀀스 객체 락 메커니즘을 구현한 것