오라클 시퀀스(Sequence) 생성, 삭제, 값변경

오라클 시퀀스 생성, 삭제, 값변경에 대해 알아보도록 하겠습니다. 오라클은 시퀀스라는 기능을 제공하는데 시퀀스는 무엇이며 왜 사용해야 하고 어떻게 사용하는지 알아보겠습니다.

오라클 시퀀스

시퀀스란?

오라클 데이터베이스의 시퀀스란 일련번호를 생성해 주는 기능의 객체입니다. DB의 특성상 여러 명이 접근이 가능하고 동시다발적으로 데이터 저장이 발생할 때 특정 칼럼에 대해 유일한 번호를 생성해 데이터의 유일성을 보장해 줍니다. 보통 테이블 구조를 잡을 때 기본키는 유일해야 하기 때문에 일련번호의 숫자로 지정하는 경우가 많습니다. 이런 경우 저장할 때 테이블 데이터의 MAX+1로 지정해서 가장 마지막 일련번호 다음 번호로 지정할 수 있지만 공교롭게 같은 시점에 여러 사용자가 저장을 하게 되면 MAX+1의 경우는 중복키가 발생할 수 있습니다. 그래서 이런 경우를 방지하고자 시퀀스를 사용하게 됩니다. 또한 증가값을 임의로 설정해 일정한 범위의 증가값을 설정할 수도 있습니다.

시퀀스 생성

시퀀스 생성은 어떻게 하는지 알아보겠습니다.

CREATE SEQUENCE 시퀀스이름
    START WITH 시작값 -- 시퀀스가 시작할 숫자를 입력합니다. (기본값 : 1)
    INCREMENT BY 증가값 -- 시퀀스가 증가할 숫자를 입력합니다. (기본값 : 1)
    MAXVALUE 최대값 -- 시퀀스의 최대값을 지정합니다.(기본값 : 10^27-1)
    MINVALUE 최소값 -- 시퀀스의 최소값을 지정합니다.(기본값 : 1)
    CYCLE | NOCYCLE -- 최대값 도달 시 CYCLE은 최소값부터 다시 증가, NOCYCLE은 일련번호 생성을 중지합니다. (기본값 : NOCYCLE)
    CACHE 캐시크기 -- 일련번호 미리 캐싱하는 크기를 지정(기본값 : 20)
 ;

시퀀스 사용방법은 다음과 같습니다.

-- 시퀀스를 생성합니다.
CREATE SEQUENCE SEQ_CUSTOMER
  START WITH 1   -- 일련번호는 1로 시작합니다.
  INCREMENT BY 1  -- 일련번호 1씩 증가합니다.
  MAXVALUE 9999999  -- 일련번호 최대값은 9999999입니다.
  NOCYCLE;    -- 사이클은 지정하지 않습니다.

-- 시퀀스를 사용한 INSERT 예제 입니다. ( CUST_SEQ = 1로 저장됩니다.)
INSERT INTO CUSTOMER(CUST_SEQ, CUST_NO, CUST_NAME)
VALUES(SEQ_CUSTOMER.NEXTVAL, 10, 'TEST');

-- CUST_SEQ = 2로 저장됩니다.
INSERT INTO CUSTOMER(CUST_SEQ, CUST_NO, CUST_NAME)
VALUES(SEQ_CUSTOMER.NEXTVAL, 20, 'TEST2');

시퀀스를 사용할 때 현재 시퀀스의 값도 확인할 수 있습니다. 단, CURRVAL은 같은 세션에서 시퀀스 증가가 한번 이뤄지고 나서 사용을 할 수 있습니다. 만약 아무 이벤트 없이 CURRVAL부터 조회하게 되면 오류가 발생하게 됩니다.

SELECT SEQ_CUSTOMER.CURRVAL FROM DUAL;

시퀀스 변경

시퀀스는 사용 용도에 따라 변경할 수 있습니다. 시퀀스는 유니크한 값을 제공하기 위한 객체로 데이터가 생성되어 있는 상태에서 데이터 확인 또는 충분한 검토 없이 시퀀스 변경은 심각한 상황을 초래할 수 있습니다. 때로는 시퀀스 변경보다는 삭제 후 새로 생성하는 게 나을 때도 있으니 시퀀스 변경 시에는 신중하게 적용하여야 합니다. 다음은 시퀀스 증가값 변경하는 예제입니다.

CREATE SEQUENCE SEQ_CUST
START WITH 1
INCREMENT BY 1
MAXVALUE 500;

SELECT SEQ_CUST.NEXTVAL FROM DUAL; 
SELECT SEQ_CUST.CURRVAL FROM DUAL; -- 일련번호 2
SELECT SEQ_CUST.NEXTVAL FROM DUAL; 
SELECT SEQ_CUST.CURRVAL FROM DUAL; -- 일련번호 3

-- 증가값을 2로 변경합니다.
ALTER SEQUENCE SEQ_CUST INCREMENT BY 2;

SELECT SEQ_CUST.NEXTVAL FROM DUAL; 
SELECT SEQ_CUST.CURRVAL FROM DUAL; -- 일련번호 5

시퀀스 변경의 예를 한 가지 더 알려 드리겠습니다. 보통 시스템을 구축할 때 개발서버와 운영서버를 따로 구성하게 됩니다. 데이터베이스도 개발 DB, 운영 DB를 따로 구성을 합니다. 개발 DB와 운영 DB의 모든 객체정보는 동일하게 맞춰놓습니다. 스타트는 동일하게 맞춰놓지만 운영 DB는 실서버라 개발 DB보다 데이터가 훨씬 많이 적재가 되고 시퀀스의 일련번호도 차이가 나게 됩니다. 이렇게 운영을 하다가 어느 순간 개발 DB 쪽에 운영 DB의 정보가 필요할 때 데이터 전체를 개발 DB로 마이그레이션 하는 시점이 옵니다. 그럼 이때 문제가 발생합니다. 시퀀스를 사용하고 있는 테이블이 USER 테이블이라고 가정을 하고 개발 DB의 시퀀스 번호가 2000번이라고 가정을 했을 때 운영 DB는 이보다 훨씬 데이터가 많을 테니 시퀀스 번호가 4500번이라고 가정하겠습니다. 데이터 마이그레이션을 하게 되면 테이블에 데이터 4500건이 개발 DB에 적재가 됩니다. 마이그레이션 된 시점에는 문제가 없지만 개발 DB에서 시퀀스를 사용해 데이터를 적재할 때 2001번이 일련번호로 발생할 것이고 데이터 INSERT시에 중복키 오류가 발생하게 됩니다. 이 문제를 해결해 봅시다. 다음은 시퀀스 값을 변경하는 예제입니다.

-- 1. 최대 시퀀스번호를 찾아옵니다. MAX(USER_SEQ)가 4500이라 가정 하겠습니다.
SELECT MAX(USER_SEQ) FROM USER;

-- 2. 현재 시퀀스 상태 확인
SELECT SEQ_USER.NEXTVAL FROM dual;
SELECT SEQ_USER.CURRVAL FROM dual;  -- 2001 이라 가정하겠습니다.

-- 3. 증가값 구하기
SELECT 4500 - 2001 FROM DUAL; -- 2499

-- 4. 현재 시퀀스 증가값 더해주기
ALTER SEQUENCE SEQ_USER INCREMENT BY 2499;

-- 5. 현재 시퀀스 상태 확인
SELECT SEQ_USER.NEXTVAL FROM dual;
SELECT SEQ_USER.CURRVAL FROM dual; -- 4501

이제 데이터 등록을 하더라도 중복키 오류가 발생하지 않을 것입니다.

시퀀스 삭제

시퀀스의 삭제 또한 시퀀스 변경처럼 신중하게 실행해야 합니다. 시퀀스를 삭제하게 되면 사용하고 있는 테이블에서 일련번호를 가져올 수 없게 되기 때문에 시퀀스 값을 참조하는 프로그램을 수정하고 시퀀스를 삭제해야 합니다. 시퀀스를 사용하고 있는 테이블을 삭제하는 경우 시퀀스 객체는 별도로 삭제해 주어야 합니다. 시퀀스 삭제하는 예제는 다음과 같습니다.

DROP SEQUENCE SEQ_USER;

지금까지 시퀀스에 대해 알아봤습니다. 데이터를 관리하는 관점으로 포스팅을 하려고 노력하는데 이해가 잘 되실지 모르겠네요. 궁금하신 점이 있으면 언제든 문의하세요.