17. März 2009

Trigger der anderen Art: Database Change Notification

English title: The other kind of 'trigger': Database Change Notification

Seit Oracle10g gibt es neben dem Trigger eine weiter Art und Weise, auf Änderungen in der Datenbank zu reagieren: Database Change Notification. In Oracle11g wurde der Name dann (weil die Funktionalität erweitert wurde) auf Continuous Query Notification geändert.
In Oracle10g Database Change Notification was introduced. This is (beyond triggers) another interesting technology to react on changes to database objects. The functionality was further developed in Oracle11g and the name was also changed to Continuous Query Notification.
Eine Change Notification kann auf ein DDL- oder DML-Ereignis eingerichtet werden (wie bei einem Trigger). Ein wesentlicher Unterschied ist allerdings, dass ein DML-Ereignis erst nach dem Commit erkannt und verarbeitet wird - das ist anders als bei einem Trigger, der sofort nach dem DML-Kommando feuert. Damit ist die Database bzw. Continuous Change Notification auch geeignet, um (basierend auf einem DML-Ereignis) eine Aktion außerhalb des Transaktionskontext der Datenbank durchzuführen, bspw. eine Mail zu versenden ...
A change notification can be configured to fire on DML as well as on DDL events. An important difference to triggers is indeed that the change notification fires after the COMMIT and not just after the statement. Therefore the change notification technology is also suitable to notify systems outside the database about changes to database object. An example for this is sending emails which you should never do with an ordinary trigger. The trigger fires just after the affected row or after the statement; it does not wait for the COMMIT. So when a ROLLBACK is issued instead the email sent is based on a phantom. But you can use the Change Notification technology since it fires DML events after the COMMIT.
Der andere wesentliche Unterschied ist die Art und Weise, wie die Change Notification eingerichtet wird. Auch hier gibt es ein paar fundamentale Unterschiede zu Triggern. Als Beispiel soll eine Change Notification wie folgt eingerichtet werden: Sobald die Ergebnismenge einer Abfrage auf die Tabellen EMP und DEPT sich ändern würde, sollen die betroffenen Tabellen und ROWIDs in der Tabelle TABLE_LOG geloggt werden. Bevor wir beginnen, müssen wir allerdings sicherstellen, dass der Datenbankuser, mit dem wir arbeiten, alle nötigen Privilegien hat.
The other big difference in comparison to triggers is the kind of setup for a Change Notification. I'll try an example: I want to have an event fired when the result set of a specific query (based on the tables EMP and DEPT) would change due to a DML action. Then the affected table name and rowid's shall be logged in the new table TABLE_LOG. Before starting we'll make sure that the database user we're working with has the correct privileges.
SQL> grant change notification to [schema];
SQL> grant execute on DBMS_CQ_NOTIFICATION to [schema];
Außerdem muss der Init-Parameter job_queue_processes auf einem Wert größer als Null (0) stehen
Furthermore the init-parameter job_queue_processes must have a value greater than zero.
SQL> alter system set job_queue_processes=10 scope=both;
Dann beginnen wir mit dem Erstellen der Logging-Tabelle.
Then I start with creating the logging table.
create table table_log (
  tabelle   varchar2(30),
  row_id    rowid
)
/
Nun wird die PL/SQL-Prozedur erstellt, die beim Auftreten des Ereignisses ausgeführt wird. Wichtig ist die Signatur der Prozedur - die muss aussehen wie hier vorgestellt.
After that I create a PL/SQL procedure which should be executed when the event is being fired. The signature is important - it has to look exactly like here.
CREATE OR REPLACE PROCEDURE mein_callback (
  ntfnds IN CQ_NOTIFICATION$_DESCRIPTOR
) IS
  v_regid           NUMBER;
  v_event_type      NUMBER;
  v_operation_type  NUMBER;
  v_numtables       NUMBER          := 0;
  v_tbname          VARCHAR2(60);
  v_numrows         NUMBER;
  v_row_id          VARCHAR2(2000);
  v_numqueries      NUMBER          := 0;
BEGIN
 v_event_type := ntfnds.event_type;
 IF (v_event_type = DBMS_CQ_NOTIFICATION.EVENT_QUERYCHANGE) THEN
  -- determine affected registered queries 
  v_numqueries := ntfnds.query_desc_array.count;
  FOR i in 1..v_numqueries LOOP
   -- determine affected tables
   v_numtables := ntfnds.QUERY_DESC_ARRAY(i).table_desc_array.count;
   FOR j IN 1..v_numtables LOOP
    -- get table name
    v_tbname := ntfnds.QUERY_DESC_ARRAY(i).table_desc_array(j).table_name;
    v_operation_type := ntfnds.QUERY_DESC_ARRAY(i).table_desc_array(j).Opflags;
    IF (bitand(v_operation_type, DBMS_CQ_NOTIFICATION.ALL_ROWS) = 0) THEN
     v_numrows := ntfnds.query_desc_array(i).table_desc_array(j).numrows;
    ELSE
     v_numrows :=0;   -- ROWID INFO NOT AVAILABLE 
    END IF;
 
    FOR k IN 1..v_numrows LOOP
     v_row_id := ntfnds.query_desc_array(i).table_desc_array(j).row_desc_array(k).row_id;
     INSERT INTO table_log (tabelle, row_id) values (v_tbname, v_row_id);
    END LOOP;  
   END LOOP;     
  END LOOP;        
 END IF;
END;
/
Diese Prozedur wird im nächsten Schritt in der Datenbank zusammen mit einer Abfrage registriert. Immer wenn eine Änderung an Datenbankobjekten (Tabellen) die Ergebnisse der Abfrage ändern würde, wird die Prozedur aufgerufen. Wie man sehen kann, nimmt sie genau einen Parameter entgegen - und zwar vom Typ CQ_NOTIFICATION$_DESCRIPTOR. Dies ist ein Objekttyp; wenn man sich den genauer ansieht ...
In the next step I'll register this procedure with a SQL query. Each time the query result set changes this procedure should be called. Notice the single parameter ntfnds which acts as a context object. It is of type CQ_NOTIFICATION$_DESCRIPTOR which looks like this:
SQL> desc  CQ_NOTIFICATION$_DESCRIPTOR
 Name                                      Null?    Typ
 ----------------------------------------- -------- ---------------------------
 REGISTRATION_ID                                    NUMBER
 TRANSACTION_ID                                     RAW(8)
 DBNAME                                             VARCHAR2(30)
 EVENT_TYPE                                         NUMBER
 NUMTABLES                                          NUMBER
 TABLE_DESC_ARRAY                                   SYS.CHNF$_TDESC_ARRAY
 QUERY_DESC_ARRAY                                   SYS.CHNF$_QDESC_ARRAY
... sieht mann, dass die Information über das Ereignis, welche zum Aufruf der Prozedur geführt hat, komplett da drin steckt. Der obige Code liest das Objekt aus, stellt fest, welche Tabellen und welche Zeilen in den Tabellen betroffen sind und trägt sie in die Tabelle TABLE_LOG ein. Was in dieser Prozedur drinsteckt, ist völlig dem Entwickler (also euch) überlassen - da die Change Notification euch nur über bereits "Committete" Änderungen unterrichtet, könnt Ihr auch Mails versenden oder (in größeren Systemen) andere Komponenten benachrichtigen. So ließe sich auch in der Mittelschicht verwalteter Cache für Datenbankinhalte über Änderungen benachrichtigen.
All information about the event which fired a procedure call is embedded within this object. The code therefore traverses the object, extracts the affected table names and row ids and logs them in the TABLE_LOG table. It's completely up to the developer what's in this procedure. You can rely that a DML event which fires the procedure is already committed - so you can perform calls outside the transaction context of the database. Examples are sending Emails or notifications to mid-tier-compontents (perhaps to maintain a midtier cache).
So - und nun registrieren wir die Prozedur. Sie soll stets aufgerufen werden, wenn die Ergebnismenge für folgende Query sich ändert.
Ok - now I'll register the procedure - it should be called each time the result set of this query changes.
select 
  e.empno,
  e.ename,
  e.sal,
  d.dname,
  d.loc
from emp e, dept d 
where e.deptno = d.deptno and e.sal > 2000;
Und das geht wie folgt ...
And this is done as follows ...
DECLARE
  v_reginfo  cq_notification$_reg_info;
  v_regid    NUMBER;
  v_cursor   SYS_REFCURSOR;
BEGIN
  -- Construct registration info object
  v_reginfo := CQ_NOTIFICATION$_REG_INFO (
    '[Schema].MEIN_CALLBACK',                 -- PL/SQL notification handler
    DBMS_CQ_NOTIFICATION.QOS_QUERY           -- notification type QRCN
    + DBMS_CQ_NOTIFICATION.QOS_ROWIDS,       -- include rowids of changed objects
    0,                                       -- registration persists until unregistered
    0,                                       -- notify on all operations
    0                                        -- notify immediately
  );
  
  -- Start registration process 
  v_regid := DBMS_CQ_NOTIFICATION.NEW_REG_START(
    regds => v_reginfo
  );

  -- Execute queries ...
  OPEN v_cursor FOR 
   select 
     e.empno,
     e.ename,
     e.sal,
     d.dname,
     d.loc
   from emp e, dept d 
   where e.deptno = d.deptno and e.sal > 2000;
  CLOSE v_cursor;

  -- Finish registration process
  DBMS_CQ_NOTIFICATION.REG_END;
END;
/
Nun können wir ausprobieren ... Zunächst sehen wir uns an, was wir denn ändern müssen, um überhaupt eine Benachrichtigung auszulösen ...
Let's test: First I have a look at the current result set of the query ...
     EMPNO ENAME             SAL DNAME          LOC
---------- ---------- ---------- -------------- -------------
      7566 JONES            2975 RESEARCH       DALLAS
      7698 BLAKE            2850 SALES          CHICAGO
      7782 CLARK            2450 ACCOUNTING     NEW YORK
      7788 SCOTT            3000 RESEARCH       DALLAS
      7839 KING             5000 ACCOUNTING     NEW YORK
      7902 FORD             3000 RESEARCH       DALLAS
Also ändern wir mal ... die Lokation des Departments RESEARCH in Trier um.
And then I do an update which affects the result set.
SQL> update dept set loc = 'TRIER' where dname = 'RESEARCH';

1 row updated.
Nachsehen in der Tabelle TABLE_LOG ...
Did the event fire the procedure ...?
SQL> select * from table_log;

No rows selected.
Ach ja: Die Change Notification feuert ja erst beim COMMIT. Also ...
Oh yes - the Change Notification fires after the COMMIT ...
SQL> commit;

COMMIT complete.

SQL> select * from table_log;

TABELLE                        ROW_ID
------------------------------ ------------------
PARTNER.DEPT                   AAApb4AAEAAANxkAAB
Wenn man Änderungen an Zeilen vornimmt, die keinen Einfluß auf die Ergebnismenge obiger Abfrage haben, passiert nichts. Also eine ganz interessante Art und Weise, auf Änderungen in der Datenbank zu reagieren.
If the DML operation affects only rows not relevant for the query's result set there is no action. So this is a pretty interesting piece of technology.
Naja, ein paar Dictionary Views gibt es auch noch - möchte man sich die vorhandenen Registrierungen für Abfragen ansehen, so gibt die View USER_CQ_NOTIFICATION_QUERIES Auskunft ...
There are also some dictionary views. If you want to know whether there are callback registrations for queries have a look in USER_CQ_NOTIFICATION_QUERIES.
SQL> SELECT queryid, regid, TO_CHAR(querytext) query FROM user_cq_notification_queries;

   QUERYID      REGID QUERY
---------- ---------- ----------------------------------------
         2          5 SELECT PARTNER.DEPT.LOC , PARTNER.DEPT.
                      DNAME , PARTNER.EMP.EMPNO , PARTNER.EMP.
                      ENAME , PARTNER.EMP.SAL  FROM PARTNER.DE
                      PT , PARTNER.EMP  WHERE  ( PARTNER.EMP.S
                      AL  > 2000  AND  PARTNER.EMP.DEPTNO  = P
                      ARTNER.DEPT.DEPTNO  )
... während die View USER_CHANGE_NOTIFICATION_REGS über die Registrierung im Allgemeinen Auskunft gibt - insbesondere steht da die Callback-Funktion drin.
And another view, USER_CHANGE_NOTIFICATION_REGS gives information about the registration itself - here you can see the PL/SQL procedure which is registered with the query.
SQL> select REGID, CALLBACK, TABLE_NAME from user_CHANGE_NOTIFICATION_REGS

     REGID CALLBACK                            TABLE_NAME
---------- ----------------------------------- --------------------
         5 plsql://PARTNER.MEIN_CALLBACK?PR=0  PARTNER.EMP
         5 plsql://PARTNER.MEIN_CALLBACK?PR=0  PARTNER.DEPT
Und wie wird man eine Registrierung wieder los? Auch dafür gibt es einen Aufruf ...
And how to delete a change notification registration? There is a procedure available ...
begin
  DBMS_CQ_NOTIFICATION.DEREGISTER(5);
end;
Ihr müsst die Registrierungs-ID (hier: 5) übergeben, um die entsprechende Registrierung zu löschen. Weitere Informationen findet Ihr in den Handbüchern: Zunächst wäre der Advanced Application Developers' Guide zu nennen; Infos über das beteiligte PL/SQL-Paket DBMS_CQ_NOTIFICATION findet Ihr in der PL/SQL Packages and Types Reference.
You have to provide the ID of the change notification registration to the DEREGISTER call. More information in the documentation: The Advanced Application Developers' Guide contains generic information about the change notification technology; the PL/SQL Packages and Types Reference contains reference information for the PL/SQL package DBMS_CQ_NOTIFICATION which was used to perform the registration.

4. März 2009

INSERT, UPDATE oder DELETE in einem: SQL MERGE

English title: INSERT, UPDATE or DELETE in a single statement: MERGE

Wenn man Datensätze in eine Tabelle "mischen" möchte (bspw. Kundenänderungen in eine Bestandstabelle einpflegen), muss man INSERT oder UPDATE-Anweisungen auslösen - je nachdem, ob der Kunde schon da ist oder nicht. Vielfach hilft man sich dann mit PL/SQL-Prozeduren, die zuerst mal einen INSERT versuchen, und wenn ein Fehler auftritt (DUP_VAL_ON_INDEX) ein UPDATE machen.
If one wants to "merge" rows into a table (e.g. to process a set of customer data changes) either SQL INSERT or SQL UPDATE commands have to be issued. If the customer is already there the operation is a SQL UPDATE to reflect the changes - if the customer is new an INSERT must be issued in order to create the table row. In most cases PL/SQL logic is being written; first try an INSERT and if this fails (DUP_VAL_ON_INDEX) do the UPDATE.
Das geht aber auch eleganter - mit dem MERGE-Kommando. Dieses gibt es bereits seit Oracle9i - in Oracle10g wurde es nochmals erweitert. Recht nützlich ist das Kommando im DWH-Umfeld - im Rahmen der Ladeprozesse ist das Einpflegen von Änderungsdaten normal ...
Since Oracle9i there is the SQL MERGE command. MERGE is INSERT and UPDATE (and as we'll see later) DELETE in one SQL statement. So no procedural logic is needed any more. MERGE is very useful in loading operations for datawarehouses - here its quite common that the actual data set is just delta information and has to be merged into the final (and very large) warehouse tables.
Hier ist ein einfaches Beispiel mit der EMP-Tabelle. Die Tabelle EMP_NEW enthält bereits einige Zeilen, die Inhalte aus der Tabelle EMP sollen dort hineingemischt werden.
Here is a simple example of SQL MERGE on the well-known EMP table. The EMP_NEW table already contains some rows of the EMP table and we now want to merge the other rows.
merge into emp_new dest
using (select * from emp) src
on (src.empno = dest.empno)
when matched then update set 
  dest.ename    = src.ename,
  dest.sal      = src.sal,
  dest.hiredate = src.hiredate,
  dest.job      = src.job,
  dest.deptno   = src.deptno,
  dest.comm     = src.comm,
  dest.mgr      = src.mgr
when not matched then 
  insert (empno, ename, sal, comm, mgr, job, hiredate, deptno)
  values (src.empno, src.ename, src.sal, src.comm, src.mgr, src.job, src.hiredate, src.deptno)
/
Man kann sehr schön die Abschnitte erkennen:
  • MERGE INTO ... gibt das Ziel der MERGE-Operation an (typischerweise eine Tabelle)
  • USING ... gibt die Datenquelle an; das kann eine Tabelle, ein View oder (wie hier) eine SELECT-Abfrage sein.
  • ON (matching-kriterium) legt fest, wie bereits vorhandene Zeilen erkannt werden sollen - hier anhand der EMPNO. Auch komplerere Kriterien sind möglich.
  • WHEN MATCHED ... legt fest, was passieren soll, wenn die Zeile schon da ist; in diesem Fall soll ein UPATE erfolgen; die Spalteninhalte der Zieltabelle sollen mit denen der Quelldaten überschrieben werden.
  • WHEN NOT MATCHED ... legt fest, was passieren soll, wenn die Zeile nicht vorhanden ist; in diesem Fall wird sie anhand der Quelldaten erzeugt.
The MERGE statement structure is easy to understand:
  • MERGE INTO ... defines the target of the merge operation (a table)
  • USING ... defines the data souurce - this might be a table, a view or (as here) a SQL query.
  • ON (matching-condition) tells the database how to determine the existing row. In this casewe use the EMPNO column for that. Multiple columns are also possible.
  • WHEN MATCHED ... specify here what to do if the row already exists. Typically the UPDATE operation is performed in this case.
  • WHEN NOT MATCHED ... specify here what to do if the row is not present. Typically the INSERT operation is performed in this case.
MERGE kann jedoch ab Oracle10g noch mehr ... So sollen die DEPTNO 30 verschwinden; alle Zeilen der DEPTNO 30 sollen also nicht mehr in die Zieltabelle gemischt werden.
But MERGE can even do more (Oracle10g or higher). Let's assume that the rows of department 30 should not be merged.
merge into emp_new dest
using (select * from emp) src
on (src.empno = dest.empno)
when matched then update set 
  dest.ename    = src.ename,
  dest.sal      = src.sal,
  dest.hiredate = src.hiredate,
  dest.job      = src.job,
  dest.deptno   = src.deptno,
  dest.comm     = src.comm,
  dest.mgr      = src.mgr
  -- ONLY IF DEPTNO IN SOURCE DATA NOT EQUALS "30"
  where not src.deptno = 30
when not matched then 
  insert (empno, ename, sal, comm, mgr, job, hiredate, deptno)
  values (src.empno, src.ename, src.sal, src.comm, src.mgr, src.job, src.hiredate, src.deptno)
  -- ONLY IF DEPTNO IN SOURCE DATA NOT EQUALS "30"
  where not src.deptno = 30
/
Mal ganz was Neues: ein SQL INSERT mit WHERE-Bedingung - nach diesem MERGE sieht die Tabelle EMP_NEW etwas anders aus - alle Zeilen mit DEPTNO = 30 wurden nicht übertragen. Aber die Zeilen, die bereits vor der MERGE-Operation existierten, sind immer noch da. Der nächste Schritt wäre es nun, die Zeile in der Zieltabelle zu löschen, wenn in der Quelltabelle die DEPTNO auf "30" gesetzt ist. Und MERGE kann auch das (ab Oracle10g): Mit der DELETE-Klausel:
This is new: A SQL INSERT with a WHERE condition - after this MERGE operation the EMP_NEW table looks different. All rows with DEPTNO = 30 were not merged. But the rows which already existed before the merge operation are still there. So the next step is to delete the rows in the target table of the DEPTNO column in the source data equals "30". And MERGE can also do this (Oracle10g or higher): Using the DELETE clause:
merge into emp_new dest
using (select * from emp) src
on (src.empno = dest.empno)
when matched then update set 
  dest.ename    = src.ename,
  dest.sal      = src.sal,
  dest.hiredate = src.hiredate,
  dest.job      = src.job,
  dest.deptno   = src.deptno,
  dest.comm     = src.comm,
  dest.mgr      = src.mgr
  -- DELETE ROWS IN TARGET TABLE IF SOURCE DEPTNO EQUALS 30
  delete where deptno = 30
when not matched then 
  insert (empno, ename, sal, comm, mgr, job, hiredate, deptno)
  values (src.empno, src.ename, src.sal, src.comm, src.mgr, src.job, src.hiredate, src.deptno)
  -- ONLY IF DEPTNO IN SOURCE DATA NOT EQUALS "30"
  where not src.deptno = 30
/
So kann man in einem Ladeprozeß auch Zeilen mit einem Löschflag versehen und mit MERGE dann gezielt löschen lassen. Wie man sieht, ist MERGE ein extrem mächtiges Kommando - viele Ladevorgänge lassen sich so u.U. mit einem einzigen SQL-Kommando erledigen ... und das ist allemal besser als selbstprogrammierte PL/SQL-Schleifenkonstrukte. Wenn man eine Operation auf größeren Datenbeständen mit reinem SQL lösen kann, ist das nahezu in allen Fällen besser, wenn man reines SQL anstelle von PL/SQL Loops verwendet. Dazu muss ich vielleicht auch mal ein Blog-Posting schreiben ...
Mehr Informationen (inkl. Syntax-Charts) findet Ihr in der Dokumentation: http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_9016.htm#i2081218
So one can set a delete flag in the source data - after the MERGE operation those rows are removed from the target table. MERGE can be an extremely powerful statement. Depending on the source data rows in the target table might be inserted, updated or deleted. Operations which otherwise would require procedural (PL/SQL) logic can now be achieved with just one SQL MERGE operation. And that's a general recommendation: If an operation (on large amounts of data) can be achieved with a single SQL operation this is in almost every case better than coding procedural logic. Perhaps I should write a blog posting also about this ...
More information can be found in the database documentation: http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_9016.htm#i2081218

Beliebte Postings