27. April 2007

XML ... oder nicht XML ...?

In den letzten Tagen und Wochen war ich recht häufig an Diskussionen rund um das Thema XML in der Datenbank beteiligt. Ab und zu kommt die Idee auf, XML-Dokumente auch als solche in der Datenbank zu speichern ... Dann tun sich Fragen auf, wie ...
  • Speichert man die XML-Dokumente als solche (XMLTYPE) ...?
  • Oder verteilt man die Inhalte eher auf gewöhnliche relationale Tabellen ...?
  • Welche Vorteile bietet die Speicherung als XMLTYPE ...?
Nun, um es vorwegzunehmen: XML ist meiner Meinung nach in erster Linie ein Datenaustauschformat, keine Modellierungssprache. Insofern stellt sich die Frage nach einer Datenmodellierung in XML eigentlich eher nicht. Eine Speicherung im XML-Format in der Datenbank (XMLTYPE) ist aus meiner Sicht vor allem aus einem Grund interessant: Die Daten liegen als XML vor, sollen so gespeichert und auch als XML wieder abgerufen werden. Dann bietet XMLTYPE einen großen Vorteil: Das XML-Dokument wird "nativ" gespeichert, d.h. beim Abrufen aus der Datenbank gehen keine relevanten Informationen (Kommentare, Reihenfolge der Tags, Processing Instructions) . Dies bezieht sich auf die Semantik des XML-Dokumentes. Es können durchaus Zeilenumbrüche fehlen oder aus einem kann ein werden - dies hat auf die Semantik des XML-Dokumentes keine Auswirkungen. Das sog. XML-Datenmodell wird jedoch vollständig wiederhergestellt. Wenn das XML-Format nach Speicherung der Daten in der Datenbank dagegen nicht mehr interessant ist oder sogar auf einem bestehenden Datenmodell gearbeitet werden soll, gibt es auch keinen Grund, die Daten als XML zu speichern. Das gilt übrigens auch, wenn die Daten zwar als XML, aber in einer ganz anderen XML-Struktur wieder ausgelesen werden sollen. Denn das Erzeugen von XML-Dokumenten aus relationalen Strukturen ist sehr einfach und auch noch extrem performant. http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb13gen.htm#sthref1492 Wenn man also über die native Speicherung von XML in einer Datenbank nachdenkt, sollte die erste Frage also sein, ob genau diese XML-Dokumente, die man da speichern möchte, genau so wieder ausgelesen werden sollen. Wenn dies überhaupt kein Thema ist, kann eine XML-Speicherung zwar immer noch (für Teilbereiche) interessant sein, man muss beim Datenmodell dann aber noch etwas mehr tun ... Mehr zum Thema im nächsten Post ... zu diesem Thema bin ich an Kommentaren, Meinungen oder Erfahrungen natürlich sehr interessiert ...

24. April 2007

Den DBA informieren: Langlaufende Jobs ...

Nochmal zum Thema "Den DBA informieren": Wie man Informationen in die View V$SESSION schreibt, haben wir ja gesehen. Nun setzen wir das fort: Es gibt einen längerlaufenden Job - und der DBA soll über den Fortschritt des Jobs informiert werden.
Naja, man könnte Informationen in eine Tabelle schreiben oder mit UTL_FILE anfangen, eine externe Datei zu befüllen. Nachteile: Im ersten Fall müsste man zwischendurch ein COMMIT absetzen, um zweiten Fall muss man mit einer zusätzlichen externen Datei umgehen ... man muss wissen, wo sie ist, wie man den Inhalt interpretiert und so weiter und so fort.
In der Oracle-Datenbank gibt es allerdings schon eine (dem DBA auch bekannte) View für diese Dinge: V$SESSION_LONGOPS. Wenn ein Prozess von Oracle (bspw. DBMS_REDEFINITION) losläuft, schreibt er Informationen über seinen Fortschritt dort hinein. Und das gleiche kann der Anwendungsentwickler auch tun, wie das folgende Beispiel zeigt (zur "Simulation" von Arbeit wird hier DBMS_LOCK.SLEEP aufgerufen; am besten also als SYS oder SYSTEM laufen lassen) ...
declare
  v_rindex   binary_integer := dbms_application_info.set_session_longops_nohint;
  v_slno     binary_integer;
  v_sofar    number := 0;

  v_seconds  binary_integer := 120;
begin
  dbms_application_info.set_session_longops(
    rindex    => v_rindex
   ,slno      => v_slno
   ,op_name   => 'TEST_SESSION_LONGOPS'
   ,totalwork => v_seconds
   ,sofar     => v_sofar
  );
  while v_sofar < v_seconds loop
    dbms_lock.sleep(v_seconds / 10);
    v_sofar := v_sofar + (v_seconds / 10);
    dbms_application_info.set_session_longops(
      rindex    => v_rindex
     ,slno      => v_slno
     ,op_name   => 'TEST_SESSION_LONGOPS'
     ,totalwork => v_seconds
     ,sofar     => v_sofar
    );
  end loop;
end;
/
sho err
Fragt der DBA nun seine View V$SESSION_LONGOPS ab,so sieht er ...
SQL> select opname, sofar, totalwork from v$session_longops where opname like 'TEST%';

OPNAME                         SOFAR            TOTALWORK 
------------------------------ ---------- ---------------
TEST_SESSION_LONGOPS                   60             120
60 von 120 sind bereits vollbracht, der Job ist also zur Hälfte erledigt. Was die TOTALWORK ist, bestimmt der Entwickler selbst - man sollte nur darauf achten, dass ein realistisches Bild abgegeben wird.

20. April 2007

Der Datenbank sagen, was man tut ...

Heute schneide ich ein etwas anderes Thema an: Die Zusammenarbeit zwischen Entwicklern und DBA's. Und als Entwickler, gleich ob Java, .NET oder PL/SQL, können wir eine ganze Menge tun, um diese zu verbessern. Betrachten wir den typischen Fall einer 3-Schichten-Architektur mit J2EE-Server und Connection-Pool ... Der DBA schaut in seine Datenbank und sieht in der View V$SESSION ...
SQL> select sid, serial#, username, client_info 
  2  from v$session 
  3  where username is not null;

       SID    SERIAL# USERNAME             CLIENT_INFO
---------- ---------- -------------------- ---------------
       147         35 DEMO_WS
       149         73 DEMO_WS
       159        285 SYSTEM
         :          : : 
Und was tun diese Sessions gerade? Tja ... keine Ahnung ...
Es wäre natürlich gut, wenn der DBA wüsste, was da passiert. Er muss vielleicht entscheiden, ob er die Session, die da gerade 90% CPU-Leistung konsumiert, "abgeschossen" werden soll oder nicht. Genauere Informationen würden die Entscheidung erleichtern ... und zum langen Telefonieren bleibt vielleicht keine Zeit mehr ...
Und es ist ganz einfach! In der Oracle-Datenbank gibt es eine fertige Schnittstelle, mit der man den DBA als Entwickler über das, was man da tut, benachrichtigen kann: DBMS_APPLICATION_INFO. Angewendet wird es wie folgt:
begin 
  DBMS_APPLICATION_INFO.SET_CLIENT_INFO(
    client_info => 'APPLIKATION MIS 01'
  );
  DBMS_APPLICATION_INFO.SET_MODULE(
    module_name => 'ADMIN_MODUL',
    action_name => 'BATCH_JOB_FRIDAY_001'
  );
end;
Und der DBA sieht nun folgendes ...
       SID    SERIAL# USERNAME        CLIENT_INFO          MODULE             ACTION
---------- ---------- --------------- -------------------- ------------------ -------------------------
       138         82 SCOTT           APPLIKATION MIS 01   ADMIN_MODUL        BATCH_JOB_FRIDAY_001
         :          : :               :                    :                  :
... das ist natürlich eine ganz andere Arbeitsbasis für den DBA. Ich kann den Einsatz dieses Paketes nur wärmstens empfehlen ... Die PL/SQL-Calls können natürlich auch von Java (CallableStatement), PHP, .NET oder anderen Umgebungen aus genutzt werden.
Es ist natürlich wie immer: Solange alles gut läuft, braucht man es nicht - aber wenn der DBA eines Tages schnell reagieren muss und keine Zeit mehr für lange Telefonate hat, dann wird's richtig wertvoll ...

17. April 2007

Zeilen zurückgeben ... ohne Tabelle ...?

Man möchte mit einem SQL eine Anzahl Zeilen zurückgeben und hat keine Tabelle ...? Das geht auch ohne Table Functions ...
SELECT rownum zeile FROM dual connect BY level <= x
also ...
SELECT rownum zeile FROM dual connect BY level <= 100
Probiert's mal aus ...

13. April 2007

Die Tage "zwischen" zwei Datumswerten ... ganz einfach ...

Wenn man mit Datumswerten zu tun hat, muss man häufig alle Tage zwischen einem Beginn-Datum und einem Ende-Datum ermitteln. Wie macht man dies am einfachsten ...? Nichts leichter als das:
BEZEICHNUNG                    START_DATU END_DATUM
------------------------------ ---------- ----------
Veranstaltung 1                01.05.2007 10.05.2007
Veranstaltung 2                16.05.2007 19.05.2007
Veranstaltung 3                07.07.2007 12.07.2007
Zuerst lege man sich eine Hilfstabelle mit allen Datumswerten wie folgt an:
create table datum (
  datum date,
  constraint pk_datum primary key (datum)
) organization index
/

declare
  v_curr_date date := to_date('2005-01-01', 'YYYY-MM-DD');
  v_end_date  date := to_date('2015-01-01', 'YYYY-MM-DD');
begin
  while v_curr_date < v_end_date loop
    insert into datum values (v_curr_date);
    v_curr_date := v_curr_date + 1;
  end loop;
end;
/
sho err

commit
/
Um den Zugriff auf die Hilfstabelle zu optimieren, wählen wir eine index-organized-table. Nun lassen sich die Tage zwischen Beginn- und Ende-Datum einfach durch einen Join ermitteln:
select v.bezeichnung, d.datum
from veranstaltung v join datum d on (d.datum between v.start_datum and v.end_datum)
/

BEZEICHNUNG                    DATUM
------------------------------ ----------
Veranstaltung 1                01.05.2007
Veranstaltung 1                02.05.2007
Veranstaltung 1                03.05.2007
Veranstaltung 1                04.05.2007
Veranstaltung 1                05.05.2007
Veranstaltung 1                06.05.2007
Veranstaltung 1                07.05.2007
Veranstaltung 1                08.05.2007
Veranstaltung 1                09.05.2007
Veranstaltung 1                10.05.2007

Veranstaltung 2                16.05.2007
Veranstaltung 2                17.05.2007
Veranstaltung 2                18.05.2007
Veranstaltung 2                19.05.2007

Veranstaltung 3                07.07.2007
Veranstaltung 3                08.07.2007
Veranstaltung 3                09.07.2007
Veranstaltung 3                10.07.2007
Veranstaltung 3                11.07.2007
Veranstaltung 3                12.07.2007
Natürlich können auch die Wochenenden ausgefiltert werden:
select v.bezeichnung, d.datum
from test v join datum d on (
  d.datum between v.start_datum and v.end_datum 
  and rtrim(to_char(d.datum, 'DAY', 'nls_date_language=''GERMAN''')) not in ('SAMSTAG','SONNTAG')
)
/

BEZEICHNUNG                    DATUM
------------------------------ ----------
Veranstaltung 1                01.05.2007
Veranstaltung 1                02.05.2007
Veranstaltung 1                03.05.2007
Veranstaltung 1                04.05.2007
Veranstaltung 1                07.05.2007
Veranstaltung 1                08.05.2007
:

10. April 2007

Kreuztabellen: Jetzt wird es generisch!

Nun kommt der zweite Teil. Nach Patricks Kommentar habe ich die Lösung nochmals durchdacht und festgestellt, dass die DECODE-Variante gar nicht so schlecht ist (vor allem erzeugt sie bessere Ausführungspläne) - Vielen Dank, Patrick. Insofern habe ich die generische Lösung umgestellt ... bis auf zwei Dinge:
  • Die WITH-Klausel bleibt drin - sie erlaubt mir, das "Pivoting" mit beliebigen Abfragen durchzuführen
  • Anstelle von DECODE verwende ich CASE WHEN ... END-Ausdrücke - diese sind mächtiger als DECODE
Daraus ergibt sich folgende Funktion:
create or replace function getPivotSql(
  p_sql            in varchar2,
  p_x_col          in varchar2,
  p_x_col_type     in varchar2 default 'DATE',
  p_x_col_start    in varchar2,
  p_x_col_interval in varchar2,
  p_x_col_int_unit in varchar2 default 'DAY',
  p_x_col_count    in number,
  p_y_col          in varchar2,
  p_cell_col       in varchar2,
  p_cell_col_aggr  in varchar2 default NULL
) return varchar2
is
  v_sql varchar2(32767);
begin
  v_sql := '';
  v_sql := v_sql || 'with data as ('||chr(10);
  v_sql := v_sql || '  '||p_sql||chr(10);
  v_sql := v_sql || ')';
  if p_x_col_type = 'VARCHAR2' then
    v_sql := v_sql ||', x_dist_values as ('||chr(10);
    v_sql := v_sql ||'  select distinct '||p_x_col||' val from data order by 1'||chr(10);
    v_sql := v_sql ||'), x_values_rownum as ('||chr(10);
    v_sql := v_sql ||'  select rownum zeile, val from x_dist_values where rownum <= '||p_x_col_count||chr(10);
    v_sql := v_sql ||')'||chr(10);
  else
    v_sql := v_sql || chr(10);
  end if;
  v_sql := v_sql || 'select distinct '||chr(10);
  v_sql := v_sql || '  data.'||p_y_col||','||chr(10);
  for i in 1..p_x_col_count loop
    if p_cell_col_aggr is not null then
      v_sql := v_sql || p_cell_col_aggr||'(';
    end if;
    v_sql := v_sql || '  case when ';
    if p_x_col_type = 'VARCHAR2' then
      v_sql := v_sql || 'x.zeile = '||i;
    elsif p_x_col_type = 'NUMBER' then
      v_sql := v_sql || ' data.'||p_x_col||' >= '||p_x_col_start||
        ' + ('||(i - 1)||' * '||p_x_col_interval || ') ' ||
        ' and data.'||p_x_col||' < '||p_x_col_start||
        ' + ('||i||' * '||p_x_col_interval||') ';
    elsif p_x_col_type = 'DATE' then
      v_sql := v_sql || ' data.'||p_x_col||' >= '||p_x_col_start||
        ' + interval '''||((i - 1) * p_x_col_interval )||''' '||p_x_col_int_unit||
        ' and data.'||p_x_col||' < '||p_x_col_start||
        ' + interval '''||* p_x_col_interval ||''' '||p_x_col_int_unit;
    end if;
    v_sql := v_sql ||' then '||p_cell_col|| ' else null end';
    if p_cell_col_aggr is not null then
      v_sql := v_sql || ')';
    end if;
    v_sql := v_sql || ' as "VALUE['||i||']"';
    if i < p_x_col_count then
      v_sql := v_sql || ',';
    end if;
    v_sql := v_sql ||chr(10);
  end loop;
  v_sql := v_sql || 'from data'||chr(10);
  if p_x_col_type = 'VARCHAR2' then
    v_sql := v_sql || 'join x_values_rownum x on (data.'||p_x_col||' = x.val)'||chr(10);
  end if;
  if p_cell_col_aggr is not null then
    v_sql := v_sql || 'group by data.'||p_y_col||chr(10);
  end if;
  return v_sql;
end;
/
Sie wird wie folgt aufgerufen:
getPivotSql(
  P_SQL            => [SQL, dessen Ergebnismenge pivotiert werden soll]
  P_X_COL          => [X-Spalte]
  P_X_COL_TYPE     => [Datentyp der X-Spalte]
  P_X_COL_START    => [Bei NUMBER und DATE: Startwert der X-Spalte]
  P_X_COL_INTERVAL => [Bei NUMBER und DATE: Intervall, umwelches sich X mit jeder Spalte erhöhen soll]
  P_X_COL_INT_UNIT => [Bei DATE: Einheit des Intervalls (DAY, MONTH, YEAR)]
  P_X_COL_COUNT    => [Anzahl der X-Spalten]
  P_Y_COL          => [Y-Spalte]
  P_CELL_COL       => [Spalte, mit der die Zellen gefüllt werden sollen]
  P_CELL_COL_AGGR  => [Aggregatsfunktion für die Zellen]
)
Ein Beispiel: Auf der X-Achse sollen die Jobs, auf der Y-Achse die Departments aufgetragen werden; in den Zellen soll die Gehaltssumme stehen, so dass man sehen kann, in welchen Jobs und in welchen Abteilungen welche Gehälter gezahlt werden.
select getPivotSql(
  'select deptno, job, sal from emp', /* SQL mit den Daten                         */
  'JOB',                              /* X-Spalte                                  */
  'VARCHAR2',                         /* Datentyp der X-Spalte                     */
  null,                               /* VARCHAR2: Kein Startwert                  */
  null,                               /* VARCHAR2: Kein Intervall                  */
  null,                               /* VARCHAR2: Keine Einheit für das Intervall */
  5,                                  /* maximal 5 X-Spalten                       */
  'DEPTNO',                           /* Y-Achse (Zeilen)                          */
  'SAL',                              /* Zellenwerte (Gehalt)                      */
  'SUM'                               /* Aggregatsfunktion (Summe)                 */
)
from dual
/
Als Ergebnis kommt folgendes SQL heraus ...
with data as (
  select deptno, job, sal from emp
), x_dist_values as (
  select distinct JOB val from data order by 1
), x_values_rownum as (
  select rownum zeile, val from x_dist_values where rownum <= 5
)
select distinct
  data.DEPTNO,
SUM(  case when x.zeile = 1 then SAL else null end) as "VALUE[1]",
SUM(  case when x.zeile = 2 then SAL else null end) as "VALUE[2]",
SUM(  case when x.zeile = 3 then SAL else null end) as "VALUE[3]",
SUM(  case when x.zeile = 4 then SAL else null end) as "VALUE[4]",
SUM(  case when x.zeile = 5 then SAL else null end) as "VALUE[5]"
from data
join x_values_rownum x on (data.JOB = x.val)
group by data.DEPTNO
... und ausgeführt ergibt sich:
    DEPTNO   VALUE[1]   VALUE[2]   VALUE[3]   VALUE[4]   VALUE[5]
---------- ---------- ---------- ---------- ---------- ----------
        30                   950       2850                  5600
        20       6000       1900       2975
        10                  1300       2450       5000

3 Zeilen ausgewählt.
Andere Beispiele: Es soll anhand des Gehalts selektiert werden. Ausgehend von einem Startgehalt von 1.000 sollen drei Gruppen gebildet werden (1000 - 2500, 2500 - 4000, 4000 - 5500). Diese werden auf der X-Achse aufgetragen - Die Y-Achse bilden diesmal die Jobs:
select getPivotSql(
  'select job, sal, empno from emp',  /* SQL mit den Daten                         */
  'SAL',                              /* X-Spalte                                  */
  'NUMBER',                           /* Datentyp der X-Spalte                     */
  1000,                               /* VARCHAR2: Kein Startwert                  */
  1500,                               /* VARCHAR2: Kein Intervall                  */
  null,                               /* VARCHAR2: Keine Einheit für das Intervall */
  3,                                  /* maximal 5 X-Spalten                       */
  'JOB',                              /* Y-Achse (Zeilen)                          */
  'EMPNO',                            /* Zellenwerte (Gehalt)                      */
  'COUNT'                             /* Aggregatsfunktion (Summe)                 */
)
from dual
/
Dann kommt dieses SQL heraus:
with data as (
  select job, sal, empno from emp
)
select distinct
  data.JOB,
  COUNT(  case when  data.SAL between 1000 + (0 * 1500) and 1000 + (1 * 1500)  then EMPNO else null end) as "VALUE[1]",
  COUNT(  case when  data.SAL between 1000 + (1 * 1500) and 1000 + (2 * 1500)  then EMPNO else null end) as "VALUE[2]",
  COUNT(  case when  data.SAL between 1000 + (2 * 1500) and 1000 + (3 * 1500)  then EMPNO else null end) as "VALUE[3]"
from data
group by data.JOB
Ausgeführt ...
JOB         VALUE[1]   VALUE[2]   VALUE[3]
--------- ---------- ---------- ----------
CLERK              2          0          0
SALESMAN           4          0          0
PRESIDENT          0          0          1
MANAGER            1          2          0
ANALYST            0          2          0
Probiert es aus! Feedback bzw. Kommentare sind natürlich stets erwünscht ...

2. April 2007

Kreuztabelle -I-

Häufig kommt die Anforderung, Tabellendaten als Kreuztabelle darzustellen. Ausgangspunkt ist eine "normale" SQL-Abfrage ...

select deptno, job, count(ename) anzahl 
from emp 
group by deptno, job 
order by deptno, job;

    DEPTNO JOB           ANZAHL
---------- --------- ----------
        10 CLERK              1
        10 MANAGER            1
        10 PRESIDENT          1
        20 ANALYST            2
        20 CLERK              2
        20 MANAGER            1
        30 CLERK              1
        30 MANAGER            1
        30 SALESMAN           4

Möchte man die Tabelle nun "kippen" ...

    DEPTNO     JOB_1      JOB_2      JOB_3     JOB_4      JOB_5
---------- --------- ---------- ---------- --------- ----------
        10                    1          1         1
        20         2          2          1
        30                    1          1                    4

... muss man die Abfrage für jede Spalte wie folgt mit sich selbst joinen. Wenn die "Grundabfrage" keine einfache Tabelle ist, kann man mit der WITH ... AS-Klausel auch eine SQL-Abfrage als Inline-View definieren.

with data as (
  select deptno, job, count(ename) anzahl from emp group by deptno, job

 deptno
), x_dist_values as (
  select distinct JOB val from data order by 1
), x_values_rownum as (
  select rownum zeile, val from x_dist_values where rownum <= 5
)
select distinct
  data.DEPTNO,
  p1.ANZAHL "VALUE[1]",
  p2.ANZAHL "VALUE[2]",
  p3.ANZAHL "VALUE[3]",
  p4.ANZAHL "VALUE[4]",
  p5.ANZAHL "VALUE[5]"
from data
join (select val from x_values_rownum where zeile = 1) xvr1 on (1=1)
join (select val from x_values_rownum where zeile = 2) xvr2 on (1=1)
join (select val from x_values_rownum where zeile = 3) xvr3 on (1=1)
join (select val from x_values_rownum where zeile = 4) xvr4 on (1=1)
join (select val from x_values_rownum where zeile = 5) xvr5 on (1=1)
left outer join data p1 on (
  data.deptno = p1.deptno and
  p1.JOB = xvr1.val
)
left outer join data p2 on (
  data.deptno = p2.deptno and
  p2.JOB = xvr2.val
)
left outer join data p3 on (
  data.deptno = p3.deptno and
  p3.JOB = xvr3.val
)
left outer join data p4 on (
  data.deptno = p4.deptno and
  p4.JOB = xvr4.val
)
left outer join data p5 on (
  data.deptno = p5.deptno and
  p5.JOB = xvr5.val
)

Das Prinzip einer relationalen Datenbank und damit eines jeden SQL ist, dass die Spalten stets fix sind. Ein SQL-Kommando, welches stets eine dynamische Anzahl von Spalten anzeigt, ist nicht so einfach. Lösbar ist das Problem jedoch mit dynamischem SQL. Wie das geht, steht dann in der nächsten Ausgabe.

Beliebte Postings