00 개요
- 회사에서 .sql 파일에서 CREATE SEQUENCE가 나오는데 시퀀스가 뭔지 알아보기 위해 개념을 정리해보려 한다
01 Sequence란
1. 정의
- '순서'
- 일렬번호 (유일한UNIQUE 값) 생성해주는 DBMS (Oracle, Vertica 등) 객체
- MySQL에서 auto_increment로 할 수 있으나 Oracle과 Vertica에서는 테이블 속성으로 제공하지 않고 별도의 SEQUENCE 객체를 생성해서 이용해야
- 보통 기본키 (PRIMARY KEY) 값을 생성하기 위해 사용
- 행을 구분하기 위해 기본키를 두는데 이 기본키는 중복되지 않는 것이 이상적인데 중복된 값을 가질 수 있으므로 항상 유일한 값을 가지게 하기 위해 SEQUENCE 사용
- SEQUENCE를 생성하면 순차적으로 증가하는 컬럼을 자동적으로 생성 가능
- 메모리에 Cache되었을 때 SEQUENCE 값의 Access 효율이 증가함
- SEQUENCE는 테이블과는 독립적으로 저장 및 생성됨 → 하나의 SEQUENCE를 여러 테이블에서 사용 가능
2. Sequence 문법
START WITH n
INCREMENT BY n
MAXVALUE n | NOMAXVALUE
MINVALUE n | NOMINVALUE
CACHE n | NO CACHE
NOORDER | ORDER
NOCYCLE | CYCLE
- START WITH n
- INCREMENT BY n
- MAXVALUE n | NOMAXVALUE
- n = 최댓값 또는 NOMAXVALUE = 무한대 값
- MINVALUE n | NOMINVALUE
- n = 최솟값 또는 NOMINVALUE = 무한대 값
- CACHE n
- Sequence를 빨리 제공하기 위해 메모리에 캐쉬 하는 갯수 지정
- 시스템이 비정상적으로 종료하게 되면 캐쉬가 사라지게 되어 캐시된 값 다음부터 시작됨
- 기본값 = 20
- NOORDER | ORDER
- 병렬서버 사용 경우 요청 순서에 따라 정확하게 Sequence 생성하기 원할 때 ORDER로 지정
- 단일서버 경우 이 옵션과 관계 없이 정확히 요청 순서에 따라 Sequence가 생성됨
- 기본값 = NOORDER
- NOCYCLE | CYCLE
- CYCLE 지정 시 MAXVALUE에 도달했을 경우 다시 MINVALUE부터 시작
- 기본값 = NOCYCLE
02 Sequence 사용 예제
1. 테이블의 Sequence 생성
# 기본 - 1부터 1씩 증가하는 Sequence 생성
CREATE SEQUENCE REGISTER_INFO_INS_SEQ # REGISTER_INFO_INS_SEQ라는 시퀀스 생성
START WITH 1 # 시작 값 설정
INCREMENT BY 1; # 증가 값 설정
# 실무 예시
CREATE SEQUENCE REGISTER_INFO_INS_SEQ # REGISTER_INFO_INS_SEQ라는 시퀀스 생성
START WITH 1 # 시작 값 설정
INCREMENT BY 1 # 증가 값 설정
MINVALUE 1 # 최솟값 설정
MAXVALUE 999999999999999 # 최댓값 설정
NO CACHE CYCLE; # 캐시 없이 순환하게끔 설정
2. Sequence 사용
- NEXTVAL을 사용하여 SEQUENCE 값을 자동으로 입력 (next value)
1) INSERT INTO 시 사용
# NUM에 REGISTER_INFO_INS_SEQ.NEXTVAL 넣어서
# 1부터 1씩 자동으로 증가들어가는 값이 들어가게 함
INSERT INTO REGISTER_INFO_1 (NUM, NAME)
VALUES (REGISTER_INFO_INS_SEQ.NEXTVAL, '김민지');
VALUES (REGISTER_INFO_INS_SEQ.NEXTVAL, '나경은');
VALUES (REGISTER_INFO_INS_SEQ.NEXTVAL, '백희지');
# 결과
NUM | NAME
-----+-------
1 | 김민지
2 | 나경은
3 | 백희지
2) CREATE TABLE 시 사용
CREATE TABLE IF NOT EXISTS REGISTER_INFO_2
(
COMPONENT_HOST VARCHAR(256) NOT NULL,
COMPONENT_PORT VARCHAR(64) NOT NULL,
COMPONENT_NAME VARCHAR(256) NOT NULL,
COMPONENT_ID NUMBER(15) PRIMARY KEY NOT NULL DEFAULT NEXTVAL('REGISTER_INFO_INS_SEQ'),
CLUSTER_NAME VARCHAR(128) NOT NULL,
TZ_ID VARCHAR(128) NOT NULL DEFAULT '-',
LAST_UPDATE_TIME TIMESTAMP NOT NULL,
BUSINESS_AREA VARCHAR(256),
DISPLAY_DESC VARCHAR(256),
SYS_PURPOSE VARCHAR(256)
)
KSAFE :1;
3. 현재 Sequence가 어디까지 증가되어져 있는지 확인
- CURRVAL을 통해 현재 SEQUENCE 값 확인 (current value)
- 현재 DB 세션에서 .NEXTVAL 사용 후 .CURRVAL 사용 가능 (.NEXTVAL을 한 번도 사용하지 않은 상태에서 .CURRVAL 호출 시 오류 발)
- 시퀀스명.CURRVAL
SELECT REGISTER_INFO_INS_SEQ.CURRVAL FROM DUAL;
# 결과
-- CURRVAL : 현재 값
4. Sequence 수정
- START WITH 값을 제외하고 ALTER SEQUENCE로 수정 가능
ALTER SEQUENCE 시퀀스명
[INCREMENT BY n]
[MAXVALUE n | NOMAXVALUE]
[MINVALYE n | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE | NOCACHE];
1) 증가값 수정 및 처음부터 순환
# 증가값 = 3, 처음부터 순환하도록 변경
ALTER SEQUENCE REGISTER_INFO_INS_SEQ
INCREMENT BY 3 # 증가값 설정
CYCLE; # 처음부터 순환
2) 최대 증가값 수정
# 최대 증가값을 4까지로 제한
ALTER SEQUENCE REGISTER_INFO_INS_SEQ
MAXVALUE 4;
INSERT INTO REGISTER_INFO_1 (NUM, NAME)
VALUES (REGISTER_INFO_INS_SEQ.NEXTVAL, '안은비'); # 삽입 가능
VALUES (REGISTER_INFO_INS_SEQ.NEXTVAL, '김우빈'); # error 최대값 초과
5. Sequence 삭제
DROP SEQUENCE REGISTER_INFO_INS_SEQ;
6. Sequence 없는 상태에서 자동 증가값 구현
INSERT INTO REGISTER_INFO_1 (NUM, NAME)
VALUES ((SELECT MAX(NUM)+1 FROM REGISTER_INFO_1), '세종대왕');
- 최대값에 +1을 해서 최종의 결과값을 NUM의 값에 삽입하는 것
- SELECT MAX(NUM) FROM REGISTER_INFO; 는 많이 사용되는 형태로 기억해두면 용이함
주의
- Sequence는 컴퓨터 부팅 또는 다른 사람의 파일을 갖고 와 사용할 때 파일의 수정사항에 대해 내부에 가지고 있는 Sequence가 다름으로 Sequence의 번호가 random으로 나누어질 수 있음
- 그렇기 때문에 새롭게 모든 Sequence를 삭제하고 순차적으로 진행해야 됨
- 이러한 이유로 Sequence 없는 상태에서 자동 증가값 구현하는 사람들이 많으며 Sequence 번호로 일련번호를 넣는 것을 선호하지 않는 사람들이 많음 (그룹 함수 MAX를 사용하여 번호를 삽입하는 것을 많이들 선호함)
참조
- https://kerpect.tistory.com/17
- https://pongshowng.tistory.com/10
- https://offbyone.tistory.com/239
-
