[Oracle] 시퀀스(Sequence)란? (feat. auto_increment)

1. 시퀀스(Sequence)란?

ID와 같이 단순 1씩 증가하는 값을 저장할 때 MySQL에서는 auto_increment 옵션을 사용할 수 있지만, Oracle에는 아쉽게도 이 옵션이 존재하지 않는다. 이럴때 대신 사용할 수 있는 것이 시퀀스다. 시퀀스를 통해 특정 값에서 시작하여 일정한 값으로 증가 또는 감소하는 값을 사용할 수 있다. 

1-1. 시퀀스 생성 방법

CREATE SEQUENCE 스키마명{사용자명}.시퀀스명
		INCREMENT BY {a}		-- a씩 증가 (a가 음수라면 감소)
       START WITH {b} 		-- b에서부터 시작
       MINVALUE {c}			-- 최솟값 c (단, c는 b보다 클 수 없음)
       MAXVALUE {d}			-- 최댓값 d (단, d는 b보다 작을 수 없음)
       [CYCLE|NOCYCLE]		-- MAXVALUE가 됐을 때 다시 초깃값부터 반복할지 안할지
       [CACHE {e}|NOCACHE]	-- 캐시 사용 여부
       [ORDER|NOORDER]		-- 범위 내 정렬된 순서로 시퀀스 값을 얻을지 여부

아무런 옵션 없이 시퀀스명만 입력하여 생성할 경우 각 옵션의 기본값은 다음과 같다.

• INCREMENT BY : 1
• START WITH : 1
• MINVALUE : 1
• MAXVALUE : 9999999999999999999999999999
• [CYCLE|NOCYCLE] : NOCYCLE
• [CACHE|NOCACHE] : CACHE 20
• [ORDER|NOORDER] : NOORDER

 

1-2. 옵션 세부사항

1-2-1. INCREMENT BY

양수로 설정하면 값이 점점 증가하고, 음수로 설정하면 값이 점점 감소

1-2-2. START WITH

시퀀스의 초깃값으로 기본값은 1이다. 만약 START WITH을 생략하고 MINVALUE는 적는다면 초깃값은 MINVALUE가 된다.

1-2-3. MINVALUE

시퀀스의 최솟값으로 기본값은 1이다. 이는 초깃값과 최댓값보다 클 수 없다.

1-2-4. MAXVALUE

시퀀스의 최댓값으로 기본값은 9999999999999999999999999999이다. 이는 초깃값과 최솟값보다 작을 수 없다.

1-2-5. [CYCLE|NOCYCLE]

CYCLE은 시퀀스의 값이 최댓값에 도달한 경우 다시 초깃값부터 반복되고 NOCYCLE은 반복되지 않는다. 그러나 시퀀스는 보통 자동 증가하는 ID 값에 사용되고, ID는 중복되면 안되기에 CYCLE 옵션은 거의 사용되지 않는다. 기본값 역시 NOCYCLE이다.

[CYCLE 예제]

CREATE SEQUENCE test_sequence
       INCREMENT BY 1
       START WITH 1
       MAXVALUE 3
       CYCLE
       NOCACHE;

1 → 2 → 3 → 1로 반복됨

[NOCYCLE 예제]

CREATE SEQUENCE test_sequence
       INCREMENT BY 1
       START WITH 1
       MAXVALUE 3
       NOCYCLE
       NOCACHE;

NOCYCLE은 최댓값에 도달한 이후에는 값을 얻을 수 없음

⭐1-2-6. [CACHE|NOCACHE]

캐시는 시퀀스에서 가장 중요한 개념이라고 할 수 있다. 캐시란 성능 최적화를 위해 시퀀스 번호를 메모리에 미리 할당하는 기능이다. 기본값은 20이다. 시퀀스는 값을 얻기 위해 디스크에 접근하는데, 값을 얻을 때마다 디스크에 접근하는 것은 성능 저하의 원인이 된다(디스크 입출력의 비용이 크기 때문). 이를 위해 캐시를 사용할 수 있다. 캐시는 디스크에서 미리 일정 개수만큼의 값을 메모리에 로딩한다. 그러면 값을 얻기 위해 매번 디스크에 접근할 필요가 없으므로 성능을 최적화할 수 있다.

 

Q. 그렇다면 매번 캐시를 사용해야 하겠네?

물론 캐시를 사용하면 성능이 좋아지는 것은 맞지만 캐시는 자칫 치명적인 논리적 오류를 야기할 수 있다. DB 인스턴스가 종료되면 메모리에 로딩된 캐시 값은 휘발한다. 또한 오랫동안 시퀀스를 사용하지 않은 경우에도 캐시 값은 휘발한다. 다시 DB에 연결하여 시퀀스 값을 얻으면 휘발된 캐시 값 이후부터의 값이 얻어진다. 이를 설명하면 다음과 같다. 

• 초깃값과 현재 시퀀스값이 1이고, 캐시 크기가 20임
• 캐시 크기가 20이므로 1~20까지의 값이 메모리에 로딩되어 있음
• 5까지의 시퀀스 값을 잘 얻었는데 갑작스러운 오류로 DB 인스턴스가 종료됨
• 다시 DB에 접속하여 시퀀스 값을 얻으면 21이 반환됨

이같은 치명적인 이유로 인해 시퀀스는 상황을 적절히 판단하여 잘 사용하는 것이 중요하다. 만약 무조건 성능이 우선이라면 캐시 크기를 크게, 중간에 값이 빵꾸나지 않는 것이 중요하다면 NOCACHE 또는 CACHE 1, 시퀀스 손실 걱정이 없고 고성능이 필요하다면 캐시를 엄청 크게 하는 것처럼 말이다. 시퀀스를 대신하는 방법으로 다음과 같은 쿼리를 사용할 수도 있다.

INSERT INTO my_table(id, name)
VALUES (
		(SELECT NVL(MAX(id),0) + 1 FROM my_table),
        '홍길동'
        );

1-2-7. [ORDER|NOORDER]

ORDER은 시퀀스 번호를 항상 순차적으로 할당하는 것으로, 병렬 환경에서 번호 순서가 중요한 경우에 사용한다. NOORDER은 순서 보장은 되지 않지만 ORDER보다는 속도가 더 빠르다. 속도가 중요하고 순서가 크게 중요하지 않은 일반적 경우에 사용하고, 그렇기에 NOORDER이 기본값이다. 시퀀스 값의 순서가 의미를 가지는 특수한 경우(ex. 세금계산서 번호, 회계 전표 번호)를 제외하고는 기본값인 NOORDER를 사용하면 된다.

 

💡ORDER의 참고 사항

• ORDER는 CACHE가 활성화되어 있을 때만 의미가 있다. NOCACHE인 경우에는 어차피 디스크에서 값을 가져오는 것이므로 순차적인 값이 할당되기 때문이다.
• ORDER는 RAC(Real Application Clusters) 같은 병렬 환경에서만 효과가 있다. 단일 인스턴스 환경에서는 큰 의미가 없을 수 있다.

RAC에 대해선 아래 포스팅 참고!

[Oracle] RAC(Real Application Cluster)와 HA(High Availability)

 

1-3. 시퀀스 사용 방법

1-3-1. NEXTVAL

{시퀀스명}.NEXTVAL

NEXTVAL을 통해 시퀀스의 다음 값을 얻을 수 있다. 호출할 때마다 INCREMENT BY에 설정된 값만큼 증감된 값이 할당된다.

1-3-2. CURRVAL

{시퀀스명}.CURRVAL

CURRVAL을 통해 현재 시퀀스의 값을 얻을 수 있다. NEXTVAL과는 달리 몇번이고 호출해도 동일한 값이 할당된다.

 

1-4. 시퀀스 주의사항

1-4-1. 시퀀스 값 변경

생성된 시퀀스의 값은 절대 변경할 수 없다. 그냥 없다.

1-4-2. 음수 시퀀스

거의 사용하진 않겠지만 값이 점점 감소하는 시퀀스를 선언할 땐 MINVALUE와 MAXVALUE를 잘 구분해서 선언해야 한다. 음수 시퀀스(INCREMENT BY -1)를 선언할 때 옵션의 기본값들은 다음과 같다.

• START WITH : -1
• MINVALUE : -999999999999999999999999999
• MAXVALUE : -1
• [CYCLE|NOCYCLE] : NOCYCLE
• [CACHE|NOCACHE] : CACHE 20
• [ORDER|NOORDER] : NOORDER