[Oracle] 오라클 Sequence auto_increment 사용 및 정리 :: 마이자몽

새로운 사원 INSERT

새로운 사원이 입사하여 Database에 Insert하려고합니다. 순차적인 번호로 사원ID를 부여해줍니다. 이때 1006이라는 사원ID를 어떻게 받아와서 Insert 시킬까요?

MAX(사원ID) + 1 ?

MAX 함수를 이용해서 사원ID의 최대값을 받아서 1을 더해준 값을 Insert할 수 있습니다. 하지만 성능의 문제가 생깁니다. 1000정도의 숫자는 무리가 없지만 데이터의 양이 늘어날수록 부하는 커질 것 입니다.

 

INDEX 사용 ?

INDEX를 사용할수도 있습니다. index의 맨 마지막 블럭을 찾도록 하면 Root - Branch - Leaf 총 3단계에 걸쳐 마지막 번호를 받아올 수 있습니다. 퍼포먼스를 높일 수 있는 좋은 방법이지만, Sequence를 사용하면 더 빠르게 성능을 높일 수 있습니다.

 

 

Sequence

오라클의 Sequence 오브젝트는 번호 자동 생성기 입니다. Sequence를 사용하게되면 그냥 다음 번 값을 달라는 요청(PseudoColumn : NEXTVAL)으로 순차적으로 번호를 받을 수 있습니다.

 

Sequence 생성

GRANT CREATE SEQUENCE TO SCOTT;

ORACLE SCOTT 계정으로 만들겠습니다. 권한이 없다면 DBA 권한의 계정에서 Sequence를 만들 수 있는 권한을 부여해줍니다.

 

 

CREATE SEQUENCE 시퀀스명
생략가능[INCREMENT BY 숫자]
생략가능[START WITH 숫자]
생략가능[MAXVALUE 숫자 | NOMAXVALUE]
생략가능[MINVALUE 숫자 | NOMINVALUE]
생략가능[CYCLE | NOCYCLE]
생략가능[CACHE 숫자 | NOCYCLE]
생략가능[ORDER | NOORDER]

 

CREATE SEQUENCE SEQ_ROW;

 

INCREMENT BY : Sequence의 번호 생성 간격을 말합니다. 생략 시 1씩 증가합니다.

START WITH : 생성할 첫번째 번호입니다. 생략시 1부터 시작합니다.

MAXVALUE : 번호의 최대값을 지정합니다. 생략시 NOMAXVALUE를 기본값으로 10^27이 최대 입니다.

MINVALUE : 최소값을 지정합니다. 생략시 NOMINVALUE를 기본값으로 1이 최소입니다.

CYCLE : 최대값이나 최소값을 도달했을때 반복할지 여부를 지정합니다. 생략시 NOCYCLE이 기본값입니다.

CACHE : 공유메모리에 미리 생성할 번호의 개수를 지정합니다. 기본값은 20개입니다.

 

Sequence 확인

SELECT SEQ_ROW.NEXTVAL FROM DUAL;

PseudoColumn인 NEXTVAL을 이용해서 다음 값을 불러옵니다.

 

한번이상 NEXTVAL을 통해 Sequence가 시작됬다면 CURRVAL을 이용해서 현재 마지막 번호를 확인할 수 있습니다.

 

DataDictionary 확인

SELECT * FROM USER_SEQUENCES;

분명 한번만 NEXTVAL을 통해서 번호를 받았지만, DataDictionary에 있는 LAST_NUMBER값은 21로 되어 있습니다. 이는 CACHE_SIZE가 기본 20이기 때문입니다.

 

오라클 서버가 실행되면 SGA 공유메모리에 SEQUENCE의 CACHE_SIZE만큼 미리 번호를 생성합니다. 홈쇼핑이나 주식과 같이 많은 양의 Transaction이 발생되는 업무에서 한번에 많은 프로세스가 SEQUENCE를 접근시 빠른 속도로 대응하지 못하는 것을 방지하기 위해 공유메모리에 번호를 미리 생성해줍니다. CACHE의 SIZE는 Sequence생성 시 정할 수 있습니다.

 

 

Sequence 문제점

Sequence는 퍼포먼스를 높일수 있는 좋은 수단입니다. 하지만 한가지 문제점이 있습니다. 번호의 순서에 갭이 생길 수 있습니다. 갭이 생길수 있는 방법은 3가지가 있습니다.

 

 

1. Transaction rollback 되었을 때

 Sequence는 한번 생성되면 이전 번호로 돌아갈 수 있는 방법이 없습니다. 만약 1번 부터 시작하는 Sequence를 이용해서 Insert 작업을 10번 했는데 Rollback을 하게 되었을 경우 Sequence의 번호는 Rollback 되지 않고 그대로 다음 번호를 받아와 갭이 생깁니다.

 

2. 여러 테이블에서 Sequence 사용할 때

하나의 Sequence로 여러 테이블에서 사용한다면 번호 사이의 갭이 생깁니다. 하지만 이부분은 각각 테이블별로 따로 Sequence를 만들어서 사용해주면 해결할 수 있는 문제 입니다.

 

3. 오라클 서버가 다운되었을 때

오라클 서버 공유메모리에 CACHE SIZE 만큼의 Sequence 번호를 미리 생성한다고 했습니다. 휘발성 데이터인 메모리는 서버가 다운된다면 사라집니다. 한번 생성된 Sequence 번호는 다시 Rollback 되지 않기 때문에 오라클 서버가 다운된다면 공유메모리에 올라간 번호들은 날아가고 다음 Sequence의 순서부터 번호가 시작합니다.

 

 

Sequence 문제점 해결

2번의 문제는 해결이 가능하지만, 1번 3번의 문제는 방법이 없습니다. 그래서 Sequence의 경우, 순차적일 필요없이 그냥 고유의 아이디가 필요한 경우 사용하는 것을 권장합니다.