8장 - SQL의 순서

프로그래밍을 할 때, 특정 순서를 가진 데이터를 다루는 일이 자주 있다.

그런데 SQL은 전통적으로 순번을 다루기 위한 기능을 가지고 있지 않다.

하지만 실제로는 레코드에 적당한 순번을 붙여야 하는 경우가 많은데, 최근 SQL은 순서와 순번을 다루기 위한 기능들을 추가하고 있다.

결과적으로 현대의 SQL은 전통적인 집합 지향적 성향에 절차 지향적 생각이 섞인, 하이브리드 언어로 변화하고 있다.

레코드에 순번 붙히기

레코드에 순번을 붙이는 방법은 크게 두 가지가 있다.

윈도우 함수를 사용하는 방법과 상관 서브쿼리를 사용하는 방법인데, 보통 윈도우 함수의 성능이 더 좋고 가독성도 뛰어나기 때문에 여기서는 윈도우 함수 예시만 보여준다.

select student_id,
       row_number() over (order by student_id) seq
from weights;

순번을 이용한 중앙값 구하기

-- 무게의 중앙값 찾기
select avg(weight) median
from (select weight,
             row_number() over (order by weight, student_id) hi
             row_number() over (order by weight desc, student_id desc) lo
      from weights) tmp
where hi in (lo, lo - 1, lo +1);

weigth 컬럼을 오름차순으로 번호를 붙이고, 내림차순으로 번호를 붙인 뒤에 같은 순번을 가지고 있는 열이나 값의 차이가 1인 열을 찾는 쿼리다.

그런데 왜 student_id도 같이 정렬하는 것일까?

만약 무게가 같은 학생들이 있다면 정렬의 기준이 동일하다고 확신할 수 없기 때문이다.

 

여기서 성능을 더 향상시키고 싶다면, 정렬이 2번 사용되는 이슈를 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;

순번을 이용한 테이블 분할

+------+
| num  |
+------+
|    1 |
|    3 |
|    4 |
|    7 |
|    8 |
|    9 |
|   12 |
+------+

위와 같은 테이블이 있을 때, 연속성이 끊긴 부분을 보여주는 테이블을 만든다고 해보자.

 

<집합 지향적 방법>

select (n1.num + 1) gap_start,
       '~',
       (min(n2.num) - 1) 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);

이 쿼리문으로 다음과 같은 결과를 만들 수 있다.

+-----------+---+---------+
| gap_start | ~ | gap_end |
+-----------+---+---------+
|         2 | ~ |       2 |
|         5 | ~ |       6 |
|        10 | ~ |      11 |
+-----------+---+---------+

하지만 Nested Loops 결합이 일어나, 비용이 높고 실행 계획 변동 위험을 유발한다.

 

<절차 지향적 방법>

select num + 1 as gap_start,
       '~',
       (num + diff - 1) gap_end
from (select num,
             (max(num) over (order by num rows between 1 following and 1 following) - num) diff
             from numbers) tmp
where diff != 1;

이는 테이블에 1회 접근하고, 윈도우 함수에서 정렬이 실행되며, 결합을 사용하지 않기 때문에 성능이 안정적이다.

특이한 점은 SQL 레벨에서 보면, 집합 지향적 방법에서는 Nested Loops 반복이 사용되었는데, 절차 지향적 방법은 반복이 사용되지 않는다.

 

순번을 다루는 또 다른 방법 - 시퀀스 객체

일단 시퀀스 객체는 최대한 사용하지 않는 것이 좋다.

 

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

create sequence testseq
start with 1
increment by 1
maxvalue 100000
minvalue 1
cycle;

 

<시퀀스 객체의 성능 문제>

시퀀스 객체는 유일성, 연속성, 순서성을 보장하기 위해 락 메커니즘이 필요하다.

따라서 여러 사용자가 시퀀스 객체에 접근하는 경우 락 충돌로 인해 성능 저하 문제가 발생한다.

 

두 번째로 핫스팟과 관련된 문제다.

핫스팟은 순번처럼 비슷한 데이터를 연속으로 삽입하는 경우 물리적으로 같은 영역에만 저장되기 때문에 I/O 부하가 커지는 것을 말한다.