Oracle 오라클 자동증가 (AUTO_INCREMENT) 방법 정리

오라클 자동증가

데이터베이스에서 자동증가 기능은 주로 고유 식별자(ID)를 생성할 때 사용됩니다. 예를 들어, 사용자 테이블이 있다고 가정할 때 각 사용자는 고유한 ID를 가져야 합니다. 이 ID는 테이블 내에서 고유해야 하며, 새로운 레코드가 추가될 때마다 자동으로 증가해야 합니다. 오라클에서는 이러한 기능을 구현하기 위해 여러 방법을 사용할 수 있습니다.

시퀀스와 트리거를 이용한 방법

시퀀스와 트리거를 이용한 자동증가 구현 방법은 오라클 데이터베이스에서 널리 사용되는 방식입니다. 이 방법은 두 가지 주요 구성 요소, 즉 ‘시퀀스(Sequence)’와 ‘트리거(Trigger)’를 사용하여 작동합니다.

시퀀스(Sequence)

CREATE SEQUENCE user_seq
    START WITH 1       -- 시작 번호
    INCREMENT BY 1     -- 증가 폭
    NOCACHE;           -- 캐시 사용 여부 (여기서는 캐시 사용 안 함)

이 예시에서 user_seq라는 이름의 시퀀스를 생성하고 있습니다. 이 시퀀스는 1에서 시작해 각 요청마다 1씩 증가합니다.

트리거(Trigger)

트리거는 데이터베이스에 특정 조건이나 이벤트가 발생했을 때 자동으로 실행되는 프로시저입니다. 시퀀스를 통해 생성된 값을 테이블의 특정 컬럼에 자동으로 삽입하기 위해 사용합니다.

CREATE OR REPLACE TRIGGER user_before_insert
BEFORE INSERT ON users            -- 'users' 테이블에 삽입하기 전에 실행
FOR EACH ROW                      -- 각 행에 대해 실행
BEGIN
    SELECT user_seq.NEXTVAL       -- 시퀀스에서 다음 값을 가져옴
    INTO   :new.id                -- 새 행의 'id' 컬럼에 삽입
    FROM   dual;
END;

이 예시에서는 users 테이블에 새로운 행이 삽입되기 전에 트리거가 실행됩니다. 트리거는 user_seq 시퀀스를 통해 다음 값을 가져와서 새 행의 id 컬럼에 자동으로 삽입합니다.

작동 원리 및 고려사항

1. 작동 원리: 데이터 삽입 요청이 있을 때마다 트리거가 작동하고, 트리거는 시퀀스를 통해 새로운 값을 가져와서 삽입합니다. 이 과정을 통해 테이블의 각 행은 고유한 ID 값을 갖게 됩니다.
2. 성능 고려사항: 시퀀스의 NOCACHE 옵션은 성능에 영향을 줄 수 있습니다. 캐시를 사용하면 시퀀스 값들을 미리 메모리에 저장해 놓아 성능을 향상시킬 수 있습니다. 하지만, 시스템이 다운되면 캐시된 값은 손실될 수 있습니다. 따라서, 적절한 캐시 크기를 설정하는 것이 중요합니다.
3. 트리거 사용 주의사항: 트리거는 데이터베이스의 성능에 영향을 줄 수 있으므로, 필요한 경우에만 사용하는 것이 좋습니다. 또한, 복잡한 로직을 트리거 내에 넣는 것은 피해야 합니다.

MAX + 1 방법

오라클에서 자동증가를 구현하는 또 다른 방법은 기존 테이블에서 최대값(max)을 찾고 이에 1을 더하는 방식입니다. 이 방법은 시퀀스나 트리거를 사용하지 않고, SQL 쿼리 내에서 직접 최대값을 계산하여 새로운 고유값을 생성합니다. 하지만 이 방법은 동시성 및 무결성 관리 측면에서 주의가 필요합니다.

작동 원리

1. 최대값 찾기: 먼저 특정 테이블의 대상 컬럼(예: ID)에서 현재 저장된 최대값을 찾습니다.
2. 값 증가: 찾은 최대값에 1을 더하여 새로운 고유값을 생성합니다.
3. 데이터 삽입: 이 새로운 값을 삽입할 때 사용합니다.

SQL 쿼리 예시

예를 들어, ‘users’ 테이블의 ‘id’ 컬럼에 자동증가 기능을 적용한다고 가정해봅시다.

INSERT INTO users (id, username, ...)
VALUES ((SELECT NVL(MAX(id), 0) + 1 FROM users), 'new_user', ...);

이 쿼리는 다음 단계로 구성됩니다.

• SELECT MAX(id) FROM users는 ‘users’ 테이블에서 현재 가장 큰 ‘id’ 값을 찾습니다.
• NVL 함수는 만약 테이블이 비어있어서 ‘MAX(id)’가 NULL일 경우, 대신 0을 반환합니다.
• 찾은 최대값에 1을 더해 새로운 ID 값을 생성합니다.
• 마지막으로, 이 값을 ‘users’ 테이블에 새로운 레코드를 삽입할 때 사용합니다.

주의사항

이 방법은 간단하고 직관적이지만, 다음과 같은 몇 가지 중요한 단점과 주의사항이 있습니다.

1. 동시성 문제: 여러 사용자가 동시에 데이터를 삽입하려고 할 때 동일한 ID 값이 생성될 수 있습니다. 이는 데이터의 무결성을 해칠 수 있습니다.
2. 성능 문제: ‘MAX(id)’를 계산하는 데 시간이 걸리며, 특히 대용량 데이터가 있는 경우 성능에 영향을 줄 수 있습니다.
3. 트랜잭션 관리: 이 방식을 안전하게 사용하기 위해서는 적절한 트랜잭션 관리와 락(lock) 메커니즘이 필요합니다.

IDENTITY 컬럼 사용

오라클 데이터베이스에서 자동증가 기능을 구현하는 또 다른 방법은 IDENTITY 컬럼을 사용하는 것입니다. 이 방법은 오라클 12c 버전부터 사용 가능하며, 테이블을 생성할 때 특정 컬럼에 자동증가 속성을 부여합니다. IDENTITY 컬럼을 사용하면 별도의 시퀀스나 트리거를 생성할 필요 없이 간편하게 자동증가 기능을 구현할 수 있습니다.

IDENTITY 컬럼의 정의

IDENTITY 컬럼은 내부적으로 시퀀스를 사용하여 값을 생성하지만, 사용자는 이 과정을 신경 쓸 필요가 없습니다. 테이블 정의 시에 해당 컬럼을 IDENTITY로 지정함으로써, 행이 추가될 때마다 오라클이 자동으로 값을 생성하고 할당합니다.

IDENTITY 컬럼의 생성 방법

IDENTITY 컬럼을 만들 때, 다음과 같은 구문을 사용합니다.

CREATE TABLE table_name (
    column_name NUMBER GENERATED [BY DEFAULT | ALWAYS] AS IDENTITY,
    ...
);

IDENTITY 컬럼의 옵션

오라클의 IDENTITY 컬럼에는 주로 세 가지 옵션, GENERATED ALWAYS, GENERATED BY DEFAULT, 그리고 GENERATED BY DEFAULT ON NULL이 사용됩니다. 이들 각각의 옵션은 IDENTITY 컬럼의 동작 방식을 조절합니다.

1. GENERATED ALWAYS AS IDENTITY: 이 옵션을 사용하면 오라클은 항상 고유한 값을 자동으로 생성합니다. 사용자가 명시적으로 값을 지정하려고 하거나 NULL 값을 삽입하려고 할 때 에러가 발생합니다. 이는 기본 설정 옵션으로, 이 옵션을 사용하면 컬럼에 대한 사용자의 입력을 받지 않고 오라클 시스템이 자동으로 값을 할당합니다.
2. GENERATED BY DEFAULT AS IDENTITY: 이 옵션은 사용자가 컬럼에 대해 값을 명시적으로 제공하지 않을 때만 오라클이 자동으로 값을 생성합니다. 사용자가 컬럼에 대한 값을 지정하면 해당 값을 사용하고, 값이 제공되지 않으면 시스템이 값을 생성합니다. 하지만 NULL 값을 삽입하려고 하면 에러가 발생합니다.
3. GENERATED BY DEFAULT ON NULL AS IDENTITY: 이 옵션은 BY DEFAULT 옵션과 유사하지만, 차이점은 NULL 값을 컬럼에 삽입할 수 있으며, 이 경우 시스템이 자동으로 값을 생성한다는 것입니다. 사용자가 값을 명시적으로 제공하거나, 값을 제공하지 않거나, NULL 값을 제공하면, 오라클이 자동으로 고유한 값을 생성합니다.

IDENTITY 컬럼을 정의할 때 여러 옵션을 설정할 수 있습니다.

• START WITH: 시작 값 지정. 기본값은 1입니다.
• INCREMENT BY: 증가량 지정. 기본값은 1입니다.
• MINVALUE, MAXVALUE: 값의 최소값 및 최대값을 지정할 수 있습니다.
• CACHE/NOCACHE: 성능 최적화를 위해 특정 수의 값들을 미리 생성하고 캐시에 저장합니다. NOCACHE는 이 기능을 비활성화합니다.
• CYCLE/NOCYCLE: 최대값에 도달한 후 다시 최소값으로 돌아갈지 여부를 결정합니다.

IDENTITY 컬럼 사용 예시

IDENTITY 컬럼을 사용한 테이블 생성 및 데이터 삽입 예시는 다음과 같습니다.

CREATE TABLE users (
    user_id NUMBER GENERATED BY DEFAULT AS IDENTITY
    START WITH 1000 INCREMENT BY 1,
    username VARCHAR2(100)
);

INSERT INTO users (username) VALUES ('user1');

이 예시에서는 users 테이블의 user_id 컬럼이 자동증가 컬럼으로 정의되어 있습니다. INSERT 명령을 사용하여 데이터를 삽입할 때, user_id 값은 자동으로 생성됩니다.

주의사항

• IDENTITY 컬럼은 내부적으로 시퀀스를 사용합니다. 따라서, 시퀀스와 관련된 제약조건이나 성능 고려사항이 적용될 수 있습니다.
• GENERATED ALWAYS를 사용하는 경우 사용자는 해당 컬럼에 대해 명시적으로 값을 지정할 수 없습니다.

결론

오라클 데이터베이스에서 자동증가 기능을 구현하는 방법은 상황에 따라 다르게 선택될 수 있습니다. 효율성, 사용 편의성, 데이터 무결성 유지 등을 고려하여 가장 적합한 방법을 선택하는 것이 중요합니다. 예를 들어, 간단하고 효율적인 방법을 원한다면 IDENTITY 컬럼 사용이 적합할 수 있으며, 더 많은 제어가 필요하거나 구버전 오라클을 사용하는 경우에는 시퀀스와 트리거를 사용할 수 있습니다. MAX + 1 방법은 특정 상황에서 사용할 수 있지만, 위에서 언급한 동시성 및 성능 문제를 고려해야 합니다.