8장 SQL의 순서

23. 레코드에 순번 붙이기

- 최근 SQL은 순서와 순번을 다루기 위한 기능들을 추가하고 있다.

    ㆍ예를 들어 시퀀스 객체 또는 ID 필드와 같은 순번을 붙일 수 있는 기능을 비롯해,

         지금까지 사용해왔던 윈도우 함수 역시 이러한 요구에 대응한 기능이다.

 

1. 기본 키가 한 개의 필드일 경우

윈도우 함수 사용

# 기본 키가 한 개의 필드일 경우(ROW_NUMBER)
SELECT student_id,
        ROW_NUMBER() OVER (ORDER BY student_id) AS seq
    FROM Weights;

 

상관 서브쿼리를 사용

- MySQL처럼 ROW_NUMBER 함수를 사용할 수 없는 환경에서는 상관 서브쿼리를 사용해야 한다

# 기본 키가 한 개의 필드일 경우(상관 서브쿼리)
SELECT student_id,
        (SELECT COUNT(*)
             FROM Weights W2
             WHERE W2.student_id <= W1.student_id) AS seq
     FROM Weights W1;

 

- 성능 측면에서는 윈도우 함수를 사용하는 편이 좋다.

- 윈도우 함수에서는 스캔 횟수가 1회이다.

- 한편 상관 서브쿼리를 사용하는 방법에서는 2회의 스캔이 실행된다.

 

2. 기본 키가 여러 개의 필드로 구성되는 경우

윈도우 함수를 사용

# 기본 키가 여러 개의 필드로 구성되는 경우(ROW_NUMBER)
SELECT class, student_id,
        ROW_NUMBER() OVER (ORDER BY class, student_id) AS seq
    FROM Weights2;

 

상관 서브쿼리를 사용

- 가장 간단한 방법은 다중 필드 비교를 사용하는 것

    ㆍ다중 필드 비교는 이름 그대로 복합적인 필드를 하나의 값으로 연결하고 한꺼번에 비교하는 기능

# 기본 키가 여러 개의 필드로 구성되는 경우 (상관 서브쿼리: 다중 필드 비교)
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;

- 이 방법의 장점은 필드 자료형을 원하는대로 지정할 수 있다는 것이다.

    ㆍ문자열, 문자열과 숫자라도 가능하다.

- 암묵적인 자료형 변환도 발생하지 않으므로 기본 키 인덱스도 사용할 수 있다.

 

 

3. 그룹마다 순번을 붙이는 경우

윈도우 함수를 사용

- 윈도우 함수로 구현할려면 class 필드에 PARTITION BY를 적용해준다.

# 학급마다 순번 붙이기(ROW_NUMBER)
SELECT class, student_id,
        ROW_NUMBER() OVER ( PARTITION BY class ORDER BY student_id ) AS seq
    FROM Weights2;

 

상관 서브쿼리를 사용

# 학급마다 순번 붙이기(상관 서브쿼리)
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;

 

4. 순번과 갱신

윈도우 함수를 사용

- ROW_NUMBER를 쓸 경우에는 서브쿼리를 함께 사용해야 한다.

# 순번 갱신(ROW_NUMBER)
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);

 

 

상관 서브쿼리를 사용

# 순번 갱신(상관 서브쿼리)
UPDATE Weights3
    SET seq = (SELECT COUNT(*)
                 FROM Weights3 W2
                 WHERE W2.class = Weights3.class
                   AND W2.student_id <= Weights3.student_id);

 

 

---

24. 레코드에 순번 붙이기 응용

- 테이블의 레코드에 순번을 붙일 수 있다면, SQL에서 자연 수열(순번)의 성질을 활용한 다양한 테크닉을 사용할 수 있다.

- 연속성이란 '5 다음 9'처럼 건너뛰는 일이 없는 것

- 유일성은 '수열 내부에 있는 하나의 숫자는 한 번만 나타난다' 라는 것

- 두 가지 모두 자연 수열에서는 당연한 성질이지만 다양하게 활용할 수 있다.

 

1. 중앙값 구하기

- 중앙값(median)이란 숫자를 정렬하고 양쪽 끝에서부터 수를 세는 경우 정중앙에 오는 값이다.

- 단순 평균(mean)과 다르게 아웃라이어에 영향을 받지 않는다는 장점이 있다.

- 홀수라면 중앙의 값을 그냥 사용하고 짝수라면 중앙에 있는 두 개의 값의 평균을 사용한다.

 

집합 지향적 방법

- 테이블을 상위 집합과 하위 집합으로 분할하고 그 공통 부분을 검색하는 방법이다.

# 중앙값 구하기(집합 지향적 방법) : 모집합을 상위와 하위로 분할
SELECT AVG(weight)
    FROM (SELECT W1.weight
            FROM Weights W1, Weights W2
            GROUP BY W1.weight
                       -- S1(하위 집합)의 조건
           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;

- 이 해법의 포인트는 HAVING 구이다.

- CASE 식에 표현한 두 개의 특성 함수로 모집합 Weights를 상위 집합과 하위 집합으로 분할한다.

- 외측의 AVG 함수는 테이블의 레코드 수가 짝수일 때 평균을 계산하기 위해 사용하는 것이다.

- 이 방법에는 두 가지 단점이 있다.

    ㆍ1. 코드가 복잡해서 무엇을 하고 있는 것인지 한 번에 이해하기 힘들다는 것

    ㆍ2. 성능이 나쁘다.

 

절차 지향적 방법1 - 세계의 중심을 향해

- SQL에서 자연수의 특징을 활용하면 '양쪽 끝부터 숫자 세기'

# 중앙값 구하기(절차 지향형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);

- 주의할 점

    ㆍ1. 순번을 붙일 때 반드시 ROW_NUMBER 함수를 사용해야 한다.

    ㆍ2. ORDER BY의 정렬 키에 필드뿐만 아니라 기본 키도 포함해야 한다.

 

절차 지향적 방법2 - 2 빼기 1은 1

- ROW_NUMBER를 사용한 방법은 굉장히 깔끔하지만 성능적으로 이런 방법이 가장 좋다고 할 수 없다.

# 중앙값 구하기(절차 지향형 방법)2: 반환점 발견
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;

- ROW_NUMBER 함수로 구한 순번을 2배해서 diff를 구하고, 거기에서 COUNT(*) 을 빼는 것이다.

 

2. 순번을 사용한 테이블 분할

 

 

3. 테이블에 존재하는 시퀀스 구하기

 

 

---

25. 시퀀스 객체. IDENTITY 필드, 채번 테이블

- 표준 SQL에는 순번을 다루는 기능으로 시퀀셜 객체와 IDENTITY 필드가 존재한다.

- 모두 최대한 사용하지 않고, 사용할 수 없더라도 꼭 필요한 부분에만 사용하자.

- 그리고 IDENTITY 필드보다는 시퀀스 객체를 사용하자

 

1. 시퀀스 객체

- 시퀀스 객체는 '객체'라는 용어에서 알 수 있듯이 테이블 또는 뷰처럼 스키마 내부에 존재하는 객체 중 하나이다.

# 시퀀스 객체 정의 예
CREATE SEQUENCE testseq
START WITH 1
INCREMENT BY 1
MAXVALUE 100000
MINVALUE 1
CYCLE;

- 주요 지정 옵션은 구현환경에 따라 차이가 있지만 'START(초깃값)', 'INCREMENT(증가값)', 'MAXVALUE(최댓값)', 'MINVALUE(최솟값)', 'CYCLE(최댓값에 도달했을 때 순환 유무)' 정도의 옵션은 대부분의 구현에서 지원한다.

- 이렇게 만들어진 시퀀스 객체는 SQL 구문 내부에서 접근해 수열을 생성할 수 있다.

 

- 이 기능이 가장 자주 사용되는 장소는 바로 INSERT 구문 내부이다.

- 시퀀스 객체로 만들어진 순번을 기본 키로 사용해 레코드를 INSERT 하는 것이다.

# 시퀀스 객체를 사용한 레코드 INSERT 구문
INSERT INTO HogeTbl VALUES(NEXT VALUE FOR nextval, 'a', 'b', ...);

시퀀스 객체의 문제점

1. 표준화가 늦어서, 구현에 따라 구문이 달라 이식성이 없고, 사용할 수도 없는 구현도 있다.

2. 시스템에서 자동으로 생성되는 값이므로 실제 엔티티 속성이 아니다

3. 성능적인 문제를 일으킨다.

- 구현간 이식성을 신경쓰지 않아도 되는 경우는 1번이 단점이라고 볼 수 없다.

- 2번도 실무에서는 무시되는 경우가 많다.

- 3번은 무시할 수 없다!

 

시퀀스 객체로 발생하는 성능 문제

- 시퀀스 객체의 로직에 의존하는 문제

- 시퀀스 객체가 생성하는 순번의 3가지 특성

    ㆍ유일성

    ㆍ연속성

    ㆍ순서성

- 순서성은 순번의 대소 관계가 역전되지 않는다는 의미이다.

 

- 시퀀스 객체는 기본 설정에서 이러한 세 가지 성질을 모두 만족하는 순번을 생성한다.

- 따라서 동시 실행 제어를 위해  락 매커니즘이 필요하다.

- 어떤 사용자가 시퀀스 객체를 사용하고 있다면, 시퀀스 객체를 락해서 다른 사용자로부터의 접근을 블록하는 배타 제어를 수행한다.

- 사용자 A가 시퀀스 객체에서 NEXT VALUE를 검색할 떄의 처리를 간단하게 나타낸다면 다음과 같다.

    ㆍ1. 시퀀스 객체에 배타 락을 적용

    ㆍ2. NEXT VALUE를 검색

    ㆍ3. CURRENT VALUE를 1만큼 증가

    ㆍ4. 시퀀스 객체에 배타 락을 해제

 

시퀀스 객체로 발생하는 성능 문제의 대처

- 시퀀스 객체로 발생하는 성능 문제를 완화하는 방법으로 CACHE와 NOORDER 객체가 있다.

- CACHE는 새로운 값이 필요할 때마다 메모리에 읽어들일 필요가 있는 값의 수를 설정하는 옵션이다.

    ㆍ구현의 따라 기본값은 다르지만, 이 값을 크게 하면 접근 비용을 줄일 수 있다.

    ㆍ다만 CACHE 옵션을 사용할 때는 부작용으로 시스템 장애가 발생할 때 연속성을 담보할 수 없다.

    ㆍ따라서 장애가 발생하면 비어있는 숫자가 발생한다.

- NOORDER 옵션은 순서성을 담보하지 않아서 오버 헤드를 줄이는 효과가 있다

    ㆍ다만 순서성을 담보하고 싶은 경우에는 사용할 수 없다.

 

순번을 키로 사용할 떄의 성능 문제

- 순번처럼 비슷한 데이터를 연속으로 INSERT하면 물리적으로 같은 영역에 저장된다.

- 이때 저장소의 특정 물리적 블록에만 I/O 부하가 커지므로 성능 악화가 발생한다.

- 이렇게 I/O 부하가 몰리는 부분을 '핫 스팟(Hot Spot)' 또는 '핫 블록(Hot Block)'이라고 부른다.

- 이러한 문제에 대처하기가 거의 불가능하다는 점이다.

 

순번을 키로 사용할 때의 성능 문제에 대처

- 이러한 문제를 완화할 수 있는 방법은 크게 두 가지가 있다.

    ㆍ1. Oracle의 역 키 인덱스처럼, 연속된 값을 도입하는 경우라도 DBMS 내부에서 변화를 주어

           제대로 분산할 수 있는 구조(일종의 해시)를 사용하는 것이다.

    ㆍ2. 인덱스에 일부러 복잡한 필드를 추가해서 데이터의 분산도를 높이는 것이다.

          ▷ 하지만 반대로 범위 검색 등에서 I/O양이 늘어나 SELECT 구문의 성능이 나빠질 위험이 있다.

 

2. IDENTITY 필드

- IDENTITY 필드는 '자동 순번 필드' 라고도 한다.

- 테이블의 필드로 정의하고, 테이블에 INSERT가 발생할 때마다 자동으로 순번을 붙여주는 기능이다.

 

- 이러한 IDENTITY 필드는 기능적, 성능적 측면에서 모두 시퀀스 객체보다 심각한 문제를 가진다.

    ㆍ시퀀스 객체는 테이블과 독립적이므로 여러 테이블에서 사용할 수 있다.

    ㆍ반대로 IDENTITY 필드는 트가정한 테이블과 연결된다.

- IDENTITY 필드를 사용할 때의 이점은 거의 없다고 말홰도 좋다.

 

3. 채번 테이블

- 옛날에 만들어진 애플리케이션에서는 채번 테이블이라는 순번을 생성하는 전용 테이블을 사용하는 경우

 

---

마치며..

- SQL 에서 초기에 배제했던 절차 지향형이 윈도우 함수라는 형태로 부활

- 윈도우 함수를 사용하면 코드를 간결하게 기술할 수 있으므로 가독성 향상

- 윈도우 함수는 결합 또는 테이블 접근을 줄이므로 성능 향상을 가져옴

- 시퀀스 객체 또는 IDENTITY 필드는 성능 문제를 일으키는 원인이 되므로 사용할 때 주의가 필요