22. Juli 2009

Views und DML-Operationen (Updatable Views): Was geht - was geht nicht ...?

English title: Some notes on Oracle Views and DML operations

In diesem Blog-Posting möchte ich mich ein wenig über das Update-Verhalten von Views auslassen - hier bietet die Oracle-Datenbank einige Möglichkeiten an. Zunächst ist eine View in Oracle prizipiell updatefähig. Das kann man auch sehr einfach testen ...
In this blog posting I'd like to write a little bit about views and DML operations on them. The features the Oracle database offers, are not known widely. First of all: A view in the Oracle database is basically updatable. This can be easily tested ...
EMPNO ENAME      JOB         MGR HIREDATE   SAL  COMM DEPTNO
----- ---------- --------- ----- -------- ----- ----- ------
 4711 CZARSKI    CLERK         ?        ?    90     ?     20
Das INSERT-Kommando geht durch - das INSERT wird auf die Tabelle EMP übertragen. Wenn Ihr das verhindern möchtet, könnt Ihr dies mit der READ ONLY-Klausel erreichen ...
The SQL INSERT command is being executed successfully - the DML operation is passed to the underlying table EMP. If you don't like this you might add the READ ONLY clause ...
Eine DML-Operation auf einer solchen View führt dann zu einer Fehlermeldung.
Now you get an error message when you try to perform DML on this view.
ERROR at line 1:
ORA-42399: cannot perform a DML operation on a read-only view
Die obige View selektiert alle Zeilen mit der DEPTNO = 20. Was passiert eigentlich, wenn man versucht, einen Eintrag mit der DEPTNO = 30 einzufügen ...?
The above view selects all rows having DEPTNO = 20. What about inserting a row with DEPTNO = 30 ...?
No rows selected.
Das DML ging ebenfalls durch - wobei das in diesem Falle ziemlicher Blödsinn ist, denn aus Sicht der Anwendung ist der INSERT im Nirwana verschwunden. Dies lässt sich mit der CHECK OPTION verhindern ...
This works - but it doesn't make much sense: The inserted row is not visible when selecting the view since it "violates" the WHERE clause. Such issues can be prevented with the CHECK OPTION clause.
Das führt wiederum zu einer Fehlermeldung ...
Now you -again- get an error message ...
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
So weit - so gut. Bis hierhin waren die Beispiele auch recht einfach. Doch wie sieht das aus, wenn die Views komplizierter werden ...?
So far - so good. Up to now the examples were quite simple. But what about some more complex views ...? Here's another example having also some aggregate functions.
Hier werdet Ihr eine Fehlermeldung bekommen - denn Oracle kann den INSERT in diese View nicht alleine ausführen. Man muss hier die Fälle der theoretisch updatefähigen und theoretisch nicht updatefähigen View unterscheiden. Das obige Beispiel ist auch theoretisch gar nicht updatefähig, da Aggregatsfunktionen verwendet werden. Wie soll die Datenbank auch die Gehaltssumme von 20000 auf mehrere Zeilen in der Tabelle EMP verteilen?
You'll get an error message when you try a DML operation on this view. To explain the behaviour it's useful to difference between theoretical updatable and theoretical not updatable views. This last example is not updatable at all - the database cannot know how to distribute the salary sum of (here: 20000) to individual employee rows.
Es gibt aber auch theoretisch updatefähige Views, auf die Oracle das DML dennoch nicht alleine ausführen kann. Das ist insbesondere der Fall, wenn komplexere Joins durchgeführt werden. Die Data Dictionary View USER|ALL|DBA_UPDATABLE_COLUMNS gibt euch Auskunft, ob Oracle DML-Anweisungen auf eine View verarbeiten kann ... zunächst prüfen wir dies für das Eingangsbeispiel ...
There are view which are theoretical updatable, but for which Oracle cannot handle DML operations. Typical cases are views with complex joins (more than one additional table). The data dictionary provides information about updatable columns in the USER|ALL|DBA_UPDATABLE_COLUMNS view. First we check this out for the very first example in this blog posting ...
.
select table_name, column_name, insertable, updatable, deletable 
from user_updatable_columns 
where table_name='EMP_RESEARCH';

TABLE_NAME                     COLUMN_NAME                    INS UPD DEL
------------------------------ ------------------------------ --- --- ---
EMP_RESEARCH                   EMPNO                          YES YES YES
EMP_RESEARCH                   ENAME                          YES YES YES
EMP_RESEARCH                   JOB                            YES YES YES
EMP_RESEARCH                   SAL                            YES YES YES
EMP_RESEARCH                   DEPTNO                         YES YES YES
Die gleiche Abfrage für das letzte Beispiel mit den Aggregatsfunktionen ...
The same query for the last view having the aggregate functions ...
TABLE_NAME                     COLUMN_NAME                    INS UPD DEL
------------------------------ ------------------------------ --- --- ---
EMP_AGG                        DEPTNO                         NO  NO  NO
EMP_AGG                        DNAME                          NO  NO  NO
EMP_AGG                        SUM_SAL                        NO  NO  NO
EMP_AGG                        AVG_SAL                        NO  NO  NO
EMP_AGG                        HEADCOUNT                      NO  NO  NO
Ein solches NO ist jedoch noch nicht das letzte Wort: Mit einem INSTEAD-OF Trigger könnt Ihr jede beliebige View updatefähig machen - im Trigger wird der Datenbank gesagt, was sie im Update-Fall zu tun hat. Ein solcher Trigger könnte so aussehen ...
Such a NO is indeed not the last word. Coding an INSTEAD-OF Trigger makes every view updatable: The trigger code tells the database what to do with a DML operation on the view. Such a trigger might look as follows ...
INSTEAD-OF Trigger können nur für Views erzeugt werden, werden aber wie ein gewöhnlicher Tabellen-Trigger geschrieben. Im Trigger-Body fängt man die DML-Operation quasi ab und implementiert das, was eigentlich getan werden soll. Schauen wir nochmal in die View USER_UPDATABLE_COLUMNS ...
INSTEAD-OF triggers cannot be created for tables - they're only allowed for views. The trigger "catches" the original DML operation and executes the code in its body instead. The dictionary reflects the existence of the INSTEAD-OF trigger as the following output indicates ...
TABLE_NAME                     COLUMN_NAME                    INS UPD DEL
------------------------------ ------------------------------ --- --- ---
EMP_AGG                        DEPTNO                         YES YES YES
EMP_AGG                        DNAME                          YES YES YES
EMP_AGG                        SUM_SAL                        NO  NO  NO
EMP_AGG                        AVG_SAL                        NO  NO  NO
EMP_AGG                        HEADCOUNT                      NO  NO  NO
Und probieren wir es aus ...
Now we can try it ...
insert into emp_agg values (50, 'MYDEPT', 20000, 200, 90)
/

select * from dept
/

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 MYDEPT
So kann man mit Views wirklich vollständige Schnittstellen für Tabellen schaffen. Eine bestimmte Datensicht kann per View bereitgestellt und DML-Anweisungen auf diese Sicht mit INSTEAD-OF Triggern entsprechend verarbeitet werden. Ein sehr schönes Anwendungsbeispiel ist der Oracle Workspace Manager, der jüngst in der APEX Community beschrieben wurde. Der Workspace Manager erlaubt das Implemetieren langlaufender Transaktionen und unterschiedlicher Versionen von Tabellendaten.
Putting it all together we can say that views are very powerful in the Oracle database. Using INSTEAD-OF triggers views can be used as specific interfaces for every kind of dataset. DML operations can be "catched" by INSTEAD-OF triggers. A nice application of this concept is Oracle Workspace manager which allows to implement long running transactions and different versions of table data.

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 ...

Beliebte Postings