[DB] DB Sequence 생성과 삭제 완벽 가이드

Oracle DB Sequence 완벽 가이드: 생성부터 삭제까지

안녕하세요.

이번 포스팅은 Oracle 데이터베이스에서 일련번호를 자동으로 생성해주는 Sequence 객체에 대해 상세히 알아보겠습니다. Sequence는 고유한 값이 필요한 기본 키나 순차적인 번호가 필요할 때 매우 유용한 데이터베이스 객체입니다. 이 글에서는 Sequence의 생성부터 삭제까지 모든 과정을 자세히 설명하겠습니다.

---

목차

1. Sequence란?
2. Sequence 생성하기
3. Sequence 사용하기
4. Sequence 수정하기
5. Sequence 삭제하기
6. Sequence 정보 조회하기
7. Sequence 활용 사례
8. Sequence 성능 최적화
9. 다른 DBMS의 Sequence
10. 자주 묻는 질문

 

#1. Sequence란?

Sequence는 일련의 숫자를 자동으로 생성해주는 데이터베이스 객체입니다. 주로 기본 키(Primary Key)나 고유 식별자를 생성하는데 사용됩니다.

Sequence의 주요 특징:

• 유일한 숫자 값을 자동 생성
• 공유 가능한 객체로 여러 테이블에서 사용 가능
• 독립적인 객체로 테이블과 별도로 관리
• 메모리에 캐싱되어 빠른 접근 가능
• 순차적 또는 특정 간격으로 증가/감소 가능

 

#2. Sequence 생성하기

Sequence를 생성하는 기본 구문은 다음과 같습니다:

CREATE SEQUENCE 시퀀스_이름 [INCREMENT BY n] [START WITH n] [MAXVALUE n | NOMAXVALUE] [MINVALUE n | NOMINVALUE] [CYCLE | NOCYCLE] [CACHE n | NOCACHE] [ORDER | NOORDER];

각 옵션의 의미는 다음과 같습니다:

• INCREMENT BY n: 시퀀스 증가/감소 값 (기본값 1)
• START WITH n: 시퀀스 시작 값 (기본값 1)
• MAXVALUE n: 최대값 지정
• NOMAXVALUE: 최대값 없음 (기본값, 오름차순일 경우 10^27)
• MINVALUE n: 최소값 지정
• NOMINVALUE: 최소값 없음 (기본값, 오름차순일 경우 1)
• CYCLE: 최대/최소값 도달 시 다시 시작
• NOCYCLE: 최대/최소값 도달 시 에러 발생 (기본값)
• CACHE n: 메모리에 시퀀스 값을 미리 할당 (기본값 20)
• NOCACHE: 메모리에 캐싱하지 않음
• ORDER: 요청 순서대로 값 생성 보장
• NOORDER: 요청 순서 보장하지 않음 (기본값)

2.1 기본 Sequence 생성

가장 간단한 형태의 Sequence 생성 예제입니다:

CREATE SEQUENCE employee_seq;

이 명령은 1부터 시작하여 1씩 증가하는 기본 시퀀스를 생성합니다.

2.2 다양한 옵션을 사용한 Sequence 생성

CREATE SEQUENCE order_seq     INCREMENT BY 10     START WITH 1000     MAXVALUE 9999999     CACHE 50     NOCYCLE;

이 시퀀스는 1000부터 시작하여 10씩 증가하며, 최대값은 9,999,999입니다. 또한 성능 향상을 위해 50개의 값을 메모리에 캐싱합니다.

2.3 감소하는 Sequence 생성

CREATE SEQUENCE countdown_seq     INCREMENT BY -1     START WITH 100     MINVALUE 1     MAXVALUE 100     CYCLE     NOCACHE;

이 시퀀스는 100부터 시작하여 1씩 감소하며, 1에 도달하면 다시 100부터 반복합니다.

 

#3. Sequence 사용하기

Sequence에서 값을 가져오는 방법은 두 가지가 있습니다:

1. NEXTVAL: 다음 시퀀스 값을 반환하고 시퀀스를 증가시킵니다.
2. CURRVAL: 현재 세션에서 마지막으로 생성된 시퀀스 값을 반환합니다.

3.1 NEXTVAL 사용

-- 새로운 시퀀스 값 가져오기 SELECT employee_seq.NEXTVAL FROM dual; -- 테이블에 삽입 시 사용 INSERT INTO employees (employee_id, first_name, last_name) VALUES (employee_seq.NEXTVAL, 'John', 'Doe');

3.2 CURRVAL 사용

-- 현재 시퀀스 값 조회 (같은 세션에서 NEXTVAL을 한 번 이상 호출한 후에만 사용 가능) SELECT employee_seq.CURRVAL FROM dual; -- 조회 후 다른 테이블에 같은 값 사용 INSERT INTO employee_log (log_id, employee_id, action) VALUES (log_seq.NEXTVAL, employee_seq.CURRVAL, 'INSERT');

중요: CURRVAL을 사용하기 전에 같은 세션에서 해당 시퀀스의 NEXTVAL을 최소 한 번 호출해야 합니다.

3.3 Sequence 사용 가능한 위치

Sequence는 다음 위치에서 사용할 수 있습니다:

• SELECT 문의 SELECT 목록
• INSERT 문의 VALUES 절
• UPDATE 문의 SET 절
• 서브쿼리의 SELECT 목록

하지만 다음 위치에서는 사용할 수 없습니다:

• VIEW의 SELECT 문
• DISTINCT가 있는 SELECT 문
• GROUP BY, HAVING, ORDER BY 절
• 서브쿼리의 SELECT 문
• CHECK 제약 조건의 DEFAULT 값

 

#4. Sequence 수정하기

이미 생성된 Sequence의 설정을 변경해야 할 때는 ALTER SEQUENCE 명령을 사용합니다:

ALTER SEQUENCE 시퀀스_이름 [INCREMENT BY n] [MAXVALUE n | NOMAXVALUE] [MINVALUE n | NOMINVALUE] [CYCLE | NOCYCLE] [CACHE n | NOCACHE] [ORDER | NOORDER];

주의할 점은 START WITH 값은 ALTER 명령으로 변경할 수 없다는 것입니다. 시작 값을 변경하려면 시퀀스를 삭제하고 다시 생성해야 합니다.

4.1 Sequence 증분 값 변경

ALTER SEQUENCE employee_seq INCREMENT BY 5;

4.2 최대값 및 캐시 설정 변경

ALTER SEQUENCE order_seq MAXVALUE 20000000 CACHE 100;

4.3 CYCLE 옵션 추가

ALTER SEQUENCE countdown_seq CYCLE;

 

#5. Sequence 삭제하기

Sequence가 더 이상 필요하지 않을 때는 DROP SEQUENCE 명령으로 삭제할 수 있습니다:

DROP SEQUENCE 시퀀스_이름;

5.1 기본 삭제 예제

DROP SEQUENCE employee_seq;

5.2 조건부 삭제

시퀀스가 존재할 경우에만 삭제하는 방법입니다(Oracle 10g 이상):

DROP SEQUENCE IF EXISTS temp_seq;

12c 이전 버전에서는 PL/SQL 블록을 사용해야 합니다:

BEGIN    EXECUTE IMMEDIATE 'DROP SEQUENCE temp_seq'; EXCEPTION    WHEN OTHERS THEN       IF SQLCODE != -2289 THEN          RAISE;       END IF; END;

 

#6. Sequence 정보 조회하기

데이터베이스에 생성된 시퀀스 정보를 조회하려면 데이터 딕셔너리 뷰를 사용합니다:

6.1 모든 시퀀스 조회

-- 현재 사용자의 모든 시퀀스 목록 SELECT sequence_name, min_value, max_value, increment_by, last_number, cache_size, cycle_flag FROM user_sequences; -- 모든 시퀀스 목록 (DBA 권한 필요) SELECT sequence_owner, sequence_name, min_value, max_value, increment_by, last_number, cache_size, cycle_flag FROM dba_sequences;

6.2 특정 시퀀스 정보 조회

SELECT sequence_name, min_value, max_value, increment_by, last_number, cache_size, cycle_flag FROM user_sequences WHERE sequence_name = 'EMPLOYEE_SEQ';

 

#7. Sequence 활용 사례

7.1 테이블 기본 키 자동 생성

-- 테이블 생성 CREATE TABLE customers (     customer_id NUMBER PRIMARY KEY,     customer_name VARCHAR2(100) NOT NULL,     email VARCHAR2(100) ); -- 시퀀스 생성 CREATE SEQUENCE customer_seq START WITH 1000 INCREMENT BY 1 NOCACHE NOCYCLE; -- 데이터 삽입 INSERT INTO customers (customer_id, customer_name, email) VALUES (customer_seq.NEXTVAL, 'Alice Johnson', 'alice@example.com'); INSERT INTO customers (customer_id, customer_name, email) VALUES (customer_seq.NEXTVAL, 'Bob Smith', 'bob@example.com');

7.2 트리거와 함께 사용하기

-- 시퀀스 생성 CREATE SEQUENCE invoice_seq START WITH 10001 INCREMENT BY 1; -- 트리거 생성 CREATE OR REPLACE TRIGGER trg_invoice_id BEFORE INSERT ON invoices FOR EACH ROW BEGIN     IF :NEW.invoice_id IS NULL THEN         :NEW.invoice_id := invoice_seq.NEXTVAL;     END IF; END; -- 이제 아래와 같이 invoice_id를 명시하지 않아도 자동 생성됩니다 INSERT INTO invoices (customer_id, amount, invoice_date) VALUES (1001, 599.99, SYSDATE);

7.3 주문 번호 형식 생성하기

-- 시퀀스 생성 CREATE SEQUENCE order_num_seq START WITH 1 INCREMENT BY 1; -- 주문 번호 생성 함수 CREATE OR REPLACE FUNCTION generate_order_number RETURN VARCHAR2 IS     v_order_num VARCHAR2(20); BEGIN     v_order_num := 'ORD-' || TO_CHAR(SYSDATE, 'YYYYMMDD') || '-' ||                     LPAD(order_num_seq.NEXTVAL, 5, '0');     RETURN v_order_num; END; -- 함수 사용 예 INSERT INTO orders (order_id, customer_id, order_date) VALUES (generate_order_number(), 1001, SYSDATE); -- 예: ORD-20250330-00001

 

#8. Sequence 성능 최적화

8.1 캐시 설정 최적화

시퀀스 성능을 최적화하는 가장 효과적인 방법은 적절한 캐시 크기를 설정하는 것입니다:

-- 트랜잭션이 많은 테이블을 위한 대용량 캐시 설정 ALTER SEQUENCE high_volume_seq CACHE 1000;

캐시 크기를 결정할 때 고려할 사항:

• 트랜잭션 볼륨(초당 INSERT 수)
• 인스턴스 재시작 시 손실 가능한 값의 수
• 메모리 사용량

8.2 NOORDER 옵션 사용

분산 시스템이나 RAC 환경이 아니라면 성능 향상을 위해 NOORDER 옵션을 사용하세요:

CREATE SEQUENCE perf_seq CACHE 100 NOORDER;

8.3 병렬 처리 최적화

대량 데이터 로드 시, 여러 세션에서 효율적으로 시퀀스를 사용하려면:

-- 대량 로드를 위한 시퀀스 설정 CREATE SEQUENCE bulk_load_seq INCREMENT BY 1000 CACHE 10000 NOORDER; -- 각 세션별로 다른 시작점 설정 -- 세션 1 ALTER SEQUENCE bulk_load_seq INCREMENT BY 1; SELECT bulk_load_seq.NEXTVAL FROM dual;  -- 1 반환 -- 세션 2 ALTER SEQUENCE  bulk_load_seq INCREMENT BY 1; SELECT bulk_load_seq.NEXTVAL + 1 FROM dual;  -- 2 반환 -- 세션 3 ALTER SEQUENCE  bulk_load_seq INCREMENT BY 1; SELECT  bulk_load_seq.NEXTVAL + 2 FROM dual;  -- 3 반환

 

#9. 다른 DBMS의 Sequence

9.1 MySQL에서의 Sequence 대안

MySQL 8.0 이전 버전에서는 Sequence 객체가 없었습니다. 대신 AUTO_INCREMENT 속성을 사용합니다:

-- MySQL에서 AUTO_INCREMENT 사용 CREATE TABLE customers (     customer_id INT AUTO_INCREMENT PRIMARY KEY,     customer_name VARCHAR(100) NOT NULL,     email VARCHAR(100) ); -- 시작값 설정 ALTER TABLE customers AUTO_INCREMENT = 1000;

MySQL 8.0부터는 시퀀스 객체 지원:

-- MySQL 8.0+ 시퀀스 생성 CREATE SEQUENCE customer_seq START WITH 1000 INCREMENT BY 1; -- 사용 방법 SELECT NEXT VALUE FOR customer_seq;

9.2 SQL Server에서의 Sequence

SQL Server 2012부터 Sequence 객체를 지원합니다:

-- SQL Server 시퀀스 생성 CREATE SEQUENCE customer_seq START WITH 1000 INCREMENT BY 1 MINVALUE 1000 MAXVALUE 9999999 CYCLE CACHE 100; -- 사용 방법 SELECT NEXT VALUE FOR customer_seq;

9.3 PostgreSQL에서의 Sequence

PostgreSQL은 오래 전부터 시퀀스를 지원했습니다:

-- PostgreSQL 시퀀스 생성 CREATE SEQUENCE customer_seq INCREMENT BY 1 START WITH 1000 NO MAXVALUE NO CYCLE CACHE 20; -- 사용 방법 SELECT nextval('customer_seq'); SELECT currval('customer_seq');

 

#10. 자주 묻는 질문

Q1: 시퀀스 값이 갑자기 건너뛰는 이유는 무엇인가요?

A: 시퀀스 값이 건너뛰는 주요 원인은 다음과 같습니다:

• 시퀀스 캐싱: 캐시된 값이 사용되지 않고 인스턴스가 재시작된 경우
• 롤백: 트랜잭션이 롤백되어도 시퀀스 값은 롤백되지 않음
• 세션 종료: 세션이 비정상 종료되면 할당된 시퀀스 값이 손실됨

Q2: CURRVAL을 사용할 때 "ORA-08002: sequence XXXX.YYYY.nextval must be used before this pseudo-column" 오류가 발생합니다.

A: CURRVAL을 사용하기 전에 반드시 같은 세션에서 해당 시퀀스의 NEXTVAL을 한 번 이상 호출해야 합니다. 새 세션이 시작되면 NEXTVAL을 다시 호출해야 CURRVAL을 사용할 수 있습니다.

Q3: 시퀀스를 특정 값으로 재설정하는 방법은 무엇인가요?

A: Oracle에서는 시퀀스의 현재 값을 직접 변경할 수 없습니다. 대신 다음과 같은 방법을 사용할 수 있습니다:

-- 시퀀스 증분 값을 원하는 값과의 차이로 변경 DECLARE     v_next_val NUMBER;     v_desired_val NUMBER := 5000;  -- 원하는 값 BEGIN     SELECT seq_name.NEXTVAL INTO v_next_val FROM dual;     EXECUTE IMMEDIATE 'ALTER SEQUENCE seq_name INCREMENT BY ' ||                        (v_desired_val - v_next_val);     SELECT seq_name.NEXTVAL INTO v_next_val FROM dual;     EXECUTE IMMEDIATE 'ALTER SEQUENCE seq_name INCREMENT BY 1'; END;

Q4: 시퀀스 값에 갭(gap)이 생기지 않게 하려면 어떻게 해야 하나요?

A: 시퀀스는 기본적으로 갭이 생길 수 있도록 설계되었으며, 이는 성능을 위한 것입니다. 갭 없는 시퀀스가 필요하다면 다음 방법을 고려하세요:

• NOCACHE 옵션 사용 (성능 저하 감수)
• 테이블을 사용한 자체 시퀀스 구현
• 트랜잭션 테이블 사용

-- 갭 없는 시퀀스를 위한 테이블 방식 CREATE TABLE gapless_sequence (     sequence_name VARCHAR2(30) PRIMARY KEY,     current_value NUMBER NOT NULL ); INSERT INTO gapless_sequence VALUES ('MY_SEQUENCE', 0); -- 다음 값 얻기 (PL/SQL 함수) CREATE OR REPLACE FUNCTION get_next_gapless_value(p_seq_name VARCHAR2) RETURN NUMBER IS     v_value NUMBER; BEGIN     UPDATE gapless_sequence     SET current_value = current_value + 1     WHERE sequence_name = p_seq_name     RETURNING current_value INTO v_value;     RETURN v_value; END;

Q5: 기존 테이블의 데이터를 기반으로 시퀀스 시작 값을 설정하려면 어떻게 해야 하나요?

A: 테이블의 최대값 + 1로 시퀀스를 생성하려면:

DECLARE     v_max_id NUMBER; BEGIN     -- 최대 ID 값 조회     SELECT NVL(MAX(employee_id), 0) INTO v_max_id FROM employees;     -- 시퀀스 삭제 (있을 경우)     BEGIN         EXECUTE IMMEDIATE 'DROP SEQUENCE emp_id_seq';     EXCEPTION         WHEN OTHERS THEN NULL;     END;     -- 최대값 + 1부터 시작하는 시퀀스 생성     EXECUTE IMMEDIATE 'CREATE SEQUENCE emp_id_seq                         START WITH ' || (v_max_id + 1) ||                       ' INCREMENT BY 1 NOCACHE NOCYCLE'; END;

---

결론

이 글에서는 Oracle 데이터베이스의 Sequence 객체에 대해 생성부터 삭제, 그리고 다양한 활용 방법까지 자세히 살펴보았습니다. Sequence는 기본 키나 고유 식별자를 생성하는 데 매우 유용한 도구로, 올바르게 설계하고 최적화하면 데이터베이스 애플리케이션의 성능과 무결성을 향상시킬 수 있습니다.

특히 캐시 설정, 증분 값 조정, 그리고 다양한 옵션을 통해 다양한 비즈니스 요구사항에 맞게 Sequence를 구성할 수 있습니다. 또한 트리거나 기본값과 함께 사용하면 데이터 입력 작업을 더욱 간소화할 수 있습니다.

다른 DBMS에서도 비슷한 기능을 제공하므로, 이 지식은 Oracle뿐만 아니라 다양한 데이터베이스 환경에서도 활용할 수 있습니다.

긴 글 읽어주셔서 감사합니다.

끝.