Oracle中Sequence与Identity列对比：哪种自增实现方案更优？

Great question! Choosing between sequence+trigger and identity columns for auto-increment in Oracle depends on your database version, maintenance needs, and specific use cases. Let's break down both approaches clearly:

This is the classic way to implement auto-increment before Oracle 12c. Here's how it works, plus its pros and cons:

How to set it up #

First, create a sequence, then a table, then a trigger to populate the column on insert:

-- Create sequence
CREATE SEQUENCE emp_seq
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

-- Create table
CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(100) NOT NULL
);

-- Create trigger to auto-populate emp_id
CREATE OR REPLACE TRIGGER emp_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    -- Only set the value if it's not manually provided
    IF :NEW.emp_id IS NULL THEN
        SELECT emp_seq.NEXTVAL INTO :NEW.emp_id FROM DUAL;
    END IF;
END;
/

Pros #

• Backward compatibility: Works with all Oracle versions before 12c, which is critical if you're maintaining legacy systems.
• Maximum flexibility: You can customize the logic entirely—like only auto-incrementing under specific conditions, sharing a single sequence across multiple tables, or adding extra validation before setting the value.
• Control over sequence behavior: You can tweak sequence settings (cache size, increment value, cycle behavior) to match your performance needs.

Cons #

• Extra maintenance: You have to manage three separate database objects (sequence, table, trigger). If you rename the table or column, you need to update the trigger too—easy to miss and cause bugs.
• Performance overhead: Triggers add a small but measurable cost per insert, especially with bulk operations (like INSERT ... SELECT). Each row fires the trigger individually, which slows down large inserts.
• ORM integration headaches: Most ORMs don't automatically recognize this setup as an auto-increment column. You'll need extra configuration to tell the framework how to fetch the generated value after insert.

Oracle 12c introduced native identity columns, which are designed to simplify auto-increment without extra objects.

How to set it up #

You define the identity column directly in the CREATE TABLE statement:

CREATE TABLE employees (
    emp_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    emp_name VARCHAR2(100) NOT NULL
);

There are two key modifiers here:

• GENERATED ALWAYS: Prevents manual insertion of values into the column—Oracle will always use the internal sequence.
• GENERATED BY DEFAULT: Allows manual insertion of values if you specify them; Oracle uses the sequence only when the column is omitted.

Pros #

• Zero extra code: No need to create separate sequences or triggers—everything is handled natively by Oracle. This reduces clutter and the chance of human error.
• Better performance: Native implementation is more efficient than trigger-based setups, especially for bulk inserts. Oracle optimizes identity column operations under the hood.
• Seamless ORM integration: Modern ORMs (like Hibernate, MyBatis) automatically detect identity columns and handle value retrieval after insert without extra config.
• Simpler maintenance: Since it's a single table definition, changes to the column are straightforward—no need to update dependent triggers or sequences.

Cons #

• Version lock-in: Only works on Oracle 12c and later. If you need to support older versions, this isn't an option.
• Less flexibility: You can't easily share a single identity sequence across multiple tables, and customizing the auto-increment logic (like conditional triggers) requires adding a trigger anyway, which defeats the purpose of using identity columns.

• Use Identity Columns if: You're running Oracle 12c or newer, need a low-maintenance, high-performance solution, and don't require complex custom logic. This is the modern, recommended approach for most use cases.
• Use Sequence + Trigger if: You need to support pre-12c Oracle versions, or you have specific requirements like shared sequences, conditional auto-increment, or custom validation during insert.

内容的提问来源于stack exchange，提问作者Abdullah Aftab