Wissenswertes zu Identity Columns

Generelles

Auf die Beschreibung von Identity Columns wird an dieser Stelle nicht weiter eingegangen; das Feature wurde u. a. an dieser Stelle schon vor längerem vorgestellt.

Auch Identity Columns arbeiten mit Sequences, nur dass das in diesem Fall im Hintergrund passiert und man sich um diese nicht gesondert kümmern muss, sondern die gewünschten Einstellungen beim Erstellen der Tabelle mitgibt.

Wo finde ich Informationen?

Neben den allseits bekannten USER_TABLES (has_identity) und USER_TAB_COLUMNS (identity_column) gibt es noch eine dritte Informationsquelle ausschließlich für Identity Columns: USER_TAB_IDENTITY_COLS. Hier findet man alle relevanten Informationen:

• Wurde die Identity Column mit ALWAYS oder BY DEFAULT angelegt?

• Wie heißt die zugehörige Sequence?

• Welche Einstellungen hat die Sequence?

CREATE TABLE tab_identity
(
   id      NUMBER GENERATED ALWAYS AS IDENTITY,  
   datum   DATE
)
/

SELECT table_name,
       sequence_name,
       generation_type,
       identity_options
  FROM USER_TAB_IDENTITY_COLS
/

TABLE_NAME     SEQUENCE_NAME  GENERATION_TYPE IDENTITY_OPTIONS                          
-------------- -------------- --------------- ------------------------------------------
TAB_IDENTITY   ISEQ$$_103811  ALWAYS          START WITH: 1, INCREMENT BY: 1, MAX_VALUE:
                                               9999999999999999999999999999, MIN_VALUE:
                                              1, CYCLE_FLAG: N, CACHE_SIZE: 20, ORDER_FL
                                              AG: N, SCALE_FLAG: N, EXTEND_FLAG: N, SESS
                                              ION_FLAG: N, KEEP_VALUE: N  

Performance-Aspekte

Identity Columns bieten gerade bei großer Last durch Inserts einen deutlichen Performance-Vorteil gegenüber Triggern.

Hier ein Beispiel mit identischen Einstellungen der Sequence (Default, also CACHE 20):

CREATE TABLE tab_sequence
(
   id      NUMBER NOT NULL,
   datum   DATE
)
/

CREATE SEQUENCE test_seq
/

CREATE TRIGGER trg_bi_tab_sequence
   BEFORE INSERT
   ON tab_sequence
   FOR EACH ROW
BEGIN
   :NEW.id := test_seq.NEXTVAL;
END trg_bi_tab_sequence;
/

SET SERVEROUTPUT ON

DECLARE
   TYPE t_dat IS TABLE OF DATE;
   v_dat     t_dat;
   v_start   PLS_INTEGER;
   v_end     PLS_INTEGER;
BEGIN
       SELECT TRUNC (SYSDATE) - LEVEL
         BULK COLLECT INTO v_dat
         FROM DUAL
   CONNECT BY LEVEL <= 100000;

   v_start := DBMS_UTILITY.get_time;

   FORALL i IN v_dat.FIRST .. v_dat.LAST
      INSERT INTO tab_identity (datum)
           VALUES (v_dat (i));

   v_end := DBMS_UTILITY.get_time;
   DBMS_OUTPUT.put_line ('Identity: ' || (v_end - v_start));
   v_start := DBMS_UTILITY.get_time;
   FORALL i IN v_dat.FIRST .. v_dat.LAST
      INSERT INTO tab_sequence (datum)
           VALUES (v_dat (i));

   v_end := DBMS_UTILITY.get_time;
   DBMS_OUTPUT.put_line ('Trigger: ' || (v_end - v_start));
END;
/

Identity: 64
Trigger: 187

Wie man sieht, war die Variante mit der Identity Column in diesem Beispiel etwa dreimal so schnell.

Bei der Konstellation mit vielen Inserts innerhalb kurzer Zeit würde man natürlich für CACHE einen entsprechend höheren Wert wählen.

Kopieren von Tabelleninhalten

Es kommt vor, dass der vollständige Tabelleninhalt (inclusive der unveränderten ID-Spalte) manuell übertragen werden muss. Danach soll aber im Zielsystem (das hier durch eine weitere Tabelle anderen Namens repräsentiert wird) ganz normal weitergearbeitet werden können, ohne dass es zu Konflikten kommt.

Auch das ist mit Identity Columns problemlos möglich. Der Trick dabei:  Man erstellt die Tabelle zunächst mit BY DEFAULT (ober ändert die Spalte vorübergehend auf BY DEFAULT). Nach dem Kopieren wechselt man auf ALWAYS und gibt dabei an, dass der aktuell höchste Wert als neuer Startwert verwendet werden soll:

CREATE TABLE tab_identity_copy
(
   id      NUMBER GENERATED BY DEFAULT AS IDENTITY,
   datum   DATE
)
/

TABLE_NAME     SEQUENCE_NAME  GENERATION_TYPE IDENTITY_OPTIONS                          
-------------- -------------- --------------- ------------------------------------------
TAB_IDENTITY   ISEQ$$_103811  ALWAYS          START WITH: 1, INCREMENT BY: 1, MAX_VALUE:
                                               9999999999999999999999999999, MIN_VALUE:
                                              1, CYCLE_FLAG: N, CACHE_SIZE: 20, ORDER_FL
                                              AG: N, SCALE_FLAG: N, EXTEND_FLAG: N, SESS
                                              ION_FLAG: N, KEEP_VALUE: N    
TAB_IDENTITY_C ISEQ$$_103816  BY DEFAULT      START WITH: 1, INCREMENT BY: 1, MAX_VALUE:
                                              9999999999999999999999999999, MIN_VALUE:
                                              1, CYCLE_FLAG: N, CACHE_SIZE: 20, ORDER_FL
                                              AG: N, SCALE_FLAG: N, EXTEND_FLAG: N, SESS
                                              ION_FLAG: N, KEEP_VALUE: N

INSERT INTO TAB_IDENTITY_COPY
SELECT ID,  DATUM FROM TAB_IDENTITY
/

SELECT sequence_name, cache_size, last_number
  FROM user_sequences
 WHERE sequence_name IN ('ISEQ$$_103811', 'TEST_SEQ', 'ISEQ$$_103816')
/

SEQUENCE_NAME  CACHE_SIZE LAST_NUMBER
-------------- ---------- -----------
ISEQ$$_103811          20      100001
ISEQ$$_103816          20           1
TEST_SEQ               20      100001

ALTER TABLE TAB_IDENTITY_COPY
   MODIFY (id NUMBER GENERATED ALWAYS AS IDENTITY (START WITH LIMIT VALUE))
/

SELECT sequence_name, cache_size, last_number
  FROM user_sequences
 WHERE sequence_name IN ('ISEQ$$_103811', 'TEST_SEQ', 'ISEQ$$_103816')
/

SEQUENCE_NAME  CACHE_SIZE LAST_NUMBER
-------------- ---------- -----------
ISEQ$$_103811          20      100001
ISEQ$$_103816          20      100001
TEST_SEQ               20      100001

select max(id) from    TAB_IDENTITY_COPY
/

   MAX(ID)
----------
    100000

INSERT INTO TAB_IDENTITY_COPY (datum)
     VALUES (TRUNC (SYSDATE) + 1)  
/    

SELECT MAX (id) FROM TAB_IDENTITY_COPY
/

   MAX(ID)
----------
    100001

Man sieht, dass nahtlos weitergezählt wird, ohne dass ein weiterer manueller Eingriff nötig wäre.

Sequence-Verwaltung

Was passiert mit der Sequence, wenn eine Identity-Spalte verschwindet? Ganz einfach: sie wird automatisch mit gelöscht.

Das gilt in allen drei Fällen:

1. Umwandlung der Identity Column in eine normale Spalte
2. Löschen der Identity Column
3. Löschen der gesamten Tabelle

ALTER TABLE TAB_IDENTITY_COPY
MODIFY (id DROP IDENTITY);
/
-- oder
ALTER TABLE TAB_IDENTITY_COPY
DROP COLUMN id
/

DROP TABLE TAB_IDENTITY PURGE
/

SELECT sequence_name, cache_size, last_number
  FROM user_sequences
 WHERE sequence_name IN ('ISEQ$$_103811', 'TEST_SEQ', 'ISEQ$$_103816')
/

SEQUENCE_NAME  CACHE_SIZE LAST_NUMBER
-------------- ---------- -----------
TEST_SEQ               20      100001

Im dritten Fall verschwindet die Sequence natürlich nur dann, wenn die Tabelle wirklich endgültig gelöscht wurde (PURGE). Davor bleibt die Sequence erhalten, die Tabelle landet ohne PURGE im Recylebin und erscheint auch in USER_TAB_IDENTITY_COLS.

Das heißt, man braucht sich zu keinem Zeitpunkt explizit um die Sequence zu kümmern; sie wird automatisch angelegt und gelöscht und im Bedarfsfall auch angepasst (START WITH LIMIT VALUE).

Fazit

Identity Columns erleichtern die Arbeit und bieten darüber hinaus auch noch Performance-Vorteile.  Ein Umstieg lohnt sich also.