[SQL] 특정 key에 대한 고유 순번 매기기

특정 column들의 정보가 모두 일치하면 동일한 자료라고 판단하고, 중복되는 동일한 자료들에 대해 동일한 고유 순번을 매겨 갱신하는 query 설계

우리가 웹 어플리케이션을 운영하기 위해 필요한 사용자 정보를 통합 관리하는 곳이 자체 DB와는 별개로 존재했다. 정보가 변경되면 해당 자료가 우리 쪽으로 연동되어 오고, 우리는 프로시저를 스케줄링해서 자료들을 각종 웹 어플리케이션에서 참조하기 위해 필요한 형태로 가공하는 형태였다. 원래는 연동되어 오는 자료들에 이 자료들이 동일한 사항에 대한 자료인지를 확인할 수 있는 고유한 사항 순번 정보가 포함되어 있었지만, 연동 데이터베이스 설계가 변경되면서 일부 사항 순번이 누락되어 들어오는 문제가 있었다. 다시 미진했던 설계를 합의해서 사항 순번이 정확히 연동되어 올 수 있도록 개선이 이루어졌지만, 테스트 등을 위해 이미 누락된 자료들에 대해서도 사항 순번 정보가 필요한 상황이었다. 이에 따라 고유한 사항 순번을 포함하도록 자료들을 갱신하기 위한 query를 설계하게 되었다.

ROWNUM

UPDATE tbl_rcd a
   SET rcd_seq = (
    SELECT MIN(rn) 
      FROM (
        SELECT ROWNUM rn, r.*
          FROM tbl_rcd r
    ) b
     WHERE a.key1 = b.key1
       AND a.key2 = b.key2
       AND a.key3 = b.key3
);

가장 간단하게는 조회된 결과에 대해 행 번호를 매겨주는 Pseudo Column인 rownum을 사용하는 아이디어를 생각할 수 있다. 모든 record들에 대해 행 번호를 매겨 두고, 특정 key에 해당하는 column들의 값이 일치하는 record들 중 최소인 값에 해당하는 rownum을 고유 순번으로 취하는 것이다. key 값이 동일하면 동일한 순번 값을 가지게 되고, key 값이 다르면 다른 순번 값을 가지게 되므로 고유 순번의 조건 자체는 만족하게 되지만, 전체 record들에 대해 매긴 행 번호들의 일부를 순번 값으로 가져오기 때문에 연속된 순번이 매겨지는 것은 아니다.

key에 대한 GROUP BY

UPDATE tbl_rcd a
   SET rcd_seq = (
    SELECT rn
      FROM (
        SELECT key1, key2, key3, ROWNUM rn
          FROM (
            SELECT key1, key2, key3
              FROM tbl_rcd
             GROUP BY key1, key2, key3
        )
    ) b
     WHERE a.key1 = b.key1
       AND a.key2 = b.key2
       AND a.key3 = b.key3
);

전체 record들에 대해 행 번호를 매기는 아이디어를 발전시켜 생각해 본다면, key 값에 대해 번호를 매겨서 그 자체를 고유 순번으로 사용하도록 query를 설계할 수 있을 것이다. key column들에 대해 group by 절을 사용해 select하면 서로 다른 key value들을 결과로 얻을 수 있고, 이 결과에 대해 다시 행 번호를 매기면 key value에 대한 고유 순번이 된다. 이 방식은 연속된 순번을 매긴다는 점은 충족하지만, group by phrase에서 row의 손실이 일어나고 이에 따라 key column들 간의 비교를 통해 고유 순번을 탐색하는 과정에서 nested된 table full scan이 발생하기 때문에 성능 상의 문제가 발생한다. 실제로 테스트 결과 1만 건을 갱신할 때는 약 2.5초, 5만 건을 갱신할 때는 1분 이상이 소요되어 수십만 건 단위의 갱신에는 이용하기 어려운 면을 보였다.

DENSE_RANK

UPDATE tbl_rcd a
   SET rcd_seq = (
    SELECT seq
      FROM (
        SELECT pk, DENSE_RANK() OVER (
            PARTITION BY null
                ORDER BY key1, key2, key3
        ) seq
          FROM tbl_rcd
    ) b
     WHERE a.pk = b.pk
);

결국 만들고자 하는 query statement의 목적은 테이블의 모든 record들을 갱신하는 것이기 때문에, subquery 단에서 row의 손실이 발생하지 않는 집계 함수를 사용한다면 key column들이 아닌 primary key의 비교만으로 subquery의 고유 순번을 탐색할 수 있고, nested table full scan 없이 보다 최적화된 execution plan을 유도할 수 있다. 연속된 순번을 매기기 위해서는 DENSE_RANK 함수를 사용하고, 전체에 대한 순번이기 때문에 PARTITION BY 절에는 null을 지정하거나 아예 사용하지 않으면 된다. 테스트 결과 5만 건을 갱신할 때 약 3.1초 정도 정도가 소요되어 앞서 key column에 대한 group by phrase를 사용하는 것보다 대량 처리 측면에서 개선된 모습을 보였다.

MERGE INTO (Oracle 한정)

MERGE
 INTO tbl_rcd a
USING (
    SELECT pk, DENSE_RANK() OVER (
        PARTITION BY null
            ORDER BY key1, key2, key3
    ) seq
      FROM tbl_rcd
) b
ON (a.pk = b.pk)
WHEN MATCHED THEN
UPDATE
   SET a.rcd_seq = b.seq;

Oracle Database를 사용한다면 Oracle의 Dialect인 merge into 문으로 위 query를 대체할 수 있다. merge into 문의 경우에는 record들의 갱신이 한 번에 이루어지기 때문에 적절히 사용하는 경우에는 성능 개선을 기대할 수 있다. 다만 테스트 결과 5만 건을 갱신할 때 약 0.9초 정도 정도가 소요되어 단순 update문을 사용하는 것보다 개선된 성능을 확인할 수 있었다.