13. Juli 2009

Sequence oder SYS_GUID: Alternativen zum Befüllen einer Primärschlüsselspalte

English title: SYS_GUID or Sequences - alternatives for populating a PK column

Letzte Woche sprach ich auf dem Thementag der MT AG zum Thema APEX mit Niels De Bruijn über "SYS_GUID" und die Möglichkeit, diese zum Befüllen einer Primärschlüsselspalte zu verwenden. Niels hatte diese in den Hinweisen zur APEX Developer Competition ausgegraben ...
Last week I had a discussion about using the SYS_GUID function for populating primary key columns. Niels found a recommendation for using it in the guidelines to the APEX developer competition ...
Die Funktion SYS_GUID (Doku) generiert eine global eindeutige ID, die als RAW(16) zurückgegeben wird.
The SYS_GUID function (Documentation) generates a global unique ID. It returns a RAW(16) value.
SQL> select sys_guid() from dual

SYS_GUID()
--------------------------------
6E6E781B05245C36E040A8C08C021165
In den Hinweisen zur Developer Competition wird empfohlen, SYS_GUID zum Generieren der Werte für den Primärschlüssel zu nutzen; also anstelle einer Sequence. Und das ist auch wirklich einfach; denn im Gegensatz zur Sequence braucht man neben der Tabelle keine zusätzlichen Objekte mehr.
Using SYS_GUID is, compared to sequences, quite simple: Beside the table you don't need any further database object.
SQL> create table test (id_col raw(16) default sys_guid() primary key, test_col number);

Table created.

SQL> insert into test (test_col) values (1);
SQL> insert into test (test_col) values (2);
SQL> insert into test (test_col) values (3);

SQL> select * from test;

ID_COL                             TEST_COL
-------------------------------- ----------
6E937E5B3CCB80F7E040007F0100411A          1
6E937E5B3CCC80F7E040007F0100411A          2
6E937E5B3CCD80F7E040007F0100411A          3
Das sieht schonmal viel einfacher aus als die Arbeit mit einer Sequence, wo man zusätzlich eben das Sequence-Objekt und den Trigger zum Befüllen der ID-Spalte erstellen muss. Ein weiterer Vorteil wäre, dass die GUID eben global eindeutig ist: Werden Daten also aus unterschiedlichen Datenbanken zusammengemischt, gibt es keine Konflikte. Wer nicht so gerne mit RAW-Spalten arbeitet, kann die Tabelle auch so anlegen, dass eine NUMBER-Spalte entsteht.
This looks much simpler than "classic" sequences where you need the additional sequence object and a trigger for populating the column (sequences cannot be used in the default clause of a column). Another advantage is the global uniqueness of the GUID. When the data gets merged with data from other databases there will be no conflicts. If you don't like working with RAW columns you can also convert them to a NUMBER column.
SQL> create table test (id_col number default to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') primary key, test_col number);

Table created.

SQL> insert into test (test_col) values (1);
SQL> insert into test (test_col) values (2);
SQL> insert into test (test_col) values (3);

SQL> select * from test

                                     ID_COL   TEST_COL
------------------------------------------- ----------
    146980909986744216487794412995248931098          1
    146980909986745425413614027624423637274          2
    146980909986746634339433642253598343450          3
GUIDs haben natürlich auch Nachteile ...
  • Höherer Speicherplatzbedarf auf der Platte (und im Buffer Cache(!))
  • Weniger Kontrolle über die generierten Werte - eine Sequence erlaubt das Abrufen des zuletzt generierten Wertes mit CURRVAL sowie das Einstellen oder Zurücksetzen des Zählers.
  • Schwierigeres Debugging - die Arbeit mit den "natürlichen" Zahlen einer Sequence dürfte leichter fallen als die mit den langen GUID-Werten.
But there are also some disadvantages ...
  • GUID need more size on disk (and within the buffer cache(!))
  • Developers have less control on the generated values - a sequence allows to reset or adjust the current value. The last generated value can be retrieved with the CURRVAL function.
  • Debugging is more difficult - working with "natural" sequence numbers is easier than with the large GUID values.
Fangen wir mit dem Speicherplatz an. Dazu erzeuge ich drei Tabellen; eine "klassische" mit einer NUMBER-Spalte und einer Sequence; eine weitere mit einer RAW(16)-Spalte für die GUIDs und eine dritte, in der die GUIDs mit TO_NUMBER wiederum in eine (große) Zahl umgewandelt werden.
Alle drei Tabellen werden mit 100.000 Zeilen gefüllt und dann wird die Größe der Segmente mit dem in diesem Posting (Achtung: läuft nur auf 11g!) vorgestellten Skript ermittelt.
To compare the disk space consumption we're going to create three tables: The first will be populated with values from a "classic" sequence - the second will contain GUIDs as RAW(16) and the last one will have the GUIDs converted to a NUMBER datatype. After creating and populating the table we'll determine the size of the table and index segments with the script I posted some months ago. Be aware that this script is written for 11g.
Da kommt das das hier heraus:
And here are the results:
SEGMENT_NAME                     SEGMENT_TYPE                     SEG_SIZE
-------------------------------- -------------------------------- ---------------
GUID_SEQ                         TABLE                                        2MB
SYS_C0048806                     INDEX                                        2MB
--
GUID_RAW                         TABLE                                        3MB
SYS_C0048807                     INDEX                                        3MB
--
GUID_NUMBER                      TABLE                                        4MB
SYS_C0048808                     INDEX                                        4MB
Man sieht, dass die größeren GUID-Werte schon etwas mehr Speicherplatz brauchen. Wenn dies hier die Werte für 100.000 Zeilen sind, könnt Ihr euch den Mehrbedarf anhand der Anzahl der Tabellen und der darin befindlichen Zeilen leicht ausrechnen. Mit der VSIZE-Funktion könnt Ihr euch auch die Minimal- und Maximalgrößen der jeweiligen Werte (in Byte) ausgeben lassen. Die Klassische Tabelle mit per Sequence generierten Werten kommt dann auf 2 bis 4 Byte pro Wert - die GUID braucht jedesmal 16 (bei Verwendung von RAW) bzw. bis zu 21 (bei Verwendung von TO_NUMBER) Byte. Und bedenkt immer, dass die Indizes auch größer werden.
It's obvious that the larger GUID values consume more space on disk. These are the numbers for 100.000 values. You can now easily calculate the size for your amount of tables and rows. Using the VSIZE function you can also determine the size of an indivual value in bytes. You then see that the "classic" sequence values (between 1 and 100.000) have a size from two up to four bytes; the GUID values need 16 bytes each when using the RAW datatype and up to 21 bytes when converting them to a NUMBER column.
Ob das Speicherplatzargument nun wirklich ein Argument ist, muss jeder selbst beurteilen. Plattenplatz kostet zwar immer weniger; aber das ist nicht alles: Wenn die Tabellen größer werden, passen auch weniger in den Buffer Cache. Und für den Hauptspeicher gilt eben nicht dasselbe wir für den Plattenplatz. Spätestens im DWH-Umfeld mit entsprechend großen Datenbeständen spielt das Speicherplatzargument durchaus eine sehr wichtige Rolle.
At the first glance this seems to be a less important point. But keep in mind that this also affects the buffer cache usage. The bigger the tables get, the less fit into the buffer cache - and increasing the physical memory of the database machine is not that easy as increasing disk space. And when you think about DWH scenarios with really huge tables ... then these numbers will matter.
Alles in allem sind die GUIDs meiner Meinung nach eine interessante Alternative zu Sequences. Der Setup ist leichter; es werden weniger Datenbankobjekte benötigt. Wenn man schon von vorneherein absehen kann, dass die Daten mal mit denen aus anderen Datenbanken zusammengemischt werden sollen, sind GUIDs eine gute Wahl. Wenn man dagegen (bspw. im DWH-Umfeld) mit (extrem) großen Datenmengen zu tun hat und die Speicherplatznachteile richtig ins Gewicht fallen, bleiben Sequences wohl die bessere Variante. Gut auf jeden Fall, wenn man beides kennt.
In summary, GUIDs are an interesting alternative for populating primary key values. The advantages are their easy setup and global uniqueness, which is particularly useful for merging tables from different database instances. The disadvantage is the large size which leads to more disk and buffer cache consumption. For the Oracle developer ... it's always good to know about both ...

Kommentare:

Othmar Lippuner hat gesagt…

Ich bin kein Freund von Surrogatekeys und damit auch nicht von GUIDs.
Ein Primarykey bezeichnet ein Objekt eine Entität und diese Objektbezeichner haben auch ausserhalb der Datenbank für die Fachmitarbeiter eine Bedeutung.
Die Leute müssen buchen, kontieren, Beleg anzeigen etc. Zudem ist in einem Datenmodell die Primärschlüsselbeziehungen in aller Regel aus mehreren Spalten zusammengesetzt.
Weil es keinen Sinn macht hier aus "technischen Gründen" einen Surrogatekey einzuführen, macht die Verwendung von GUIDs in aller Regel keinen Sinn.
Interessant sind hierzu auch diese Stellungennahmen der anerkannten Oracle-Asse in Forumsdiskussion zum Thema, welche etwas verkappt aber dennoch eindeutig ausfallen.

Meine Devisen lehnt an SAP R/3 an: Bei Stammdatenbezeichner sollten die Objektenbezeichner extern vergeben werden. Bei Transaktionen (Belegnummern) macht es sinn fortlaufende Belegnummern zu verwenden, wobei unterschiedliche Belegarten mit unterschiedlichen Nummernkreisen sichtbar und wieder verständlich (human readable) getrennt werden.

Anonym hat gesagt…

Ein ganz großer Vorteil für mich ist die Möglichkeit, bei einem INSERT SELECT, direkt auch die PK-Spalte zu befüllen. Hier hat man bei sequences auch ganz viel zusätzlichen "monkey"-code.

Carsten Czarski hat gesagt…

Hallo,

hier schafft Oracle12c ja auch Abhilfe - denn ab 12.1 ist es möglich, das "sequence.nextval" als DEFAULT-Wert einer Tabellenspalte zuzuweisen ...

Beste Grüße

-Carsten

Beliebte Postings