Data Mining mit Bordmitteln - Teil 1

Mit diesem Post möchte ich eine Reihe starten, welche sich mit dem Thema Data Mining beschäftigt; konkret die Implementierung von Algorithmen mit Bordmitteln samt den Besonderheiten, die im Datenbankumfeld zu beachten sind.

Als Beispiel dient ein Algorithmus zur Klassifikation auf der Basis von Entscheidungsbäumen - der ID3-Algorithmus. Doch bevor wir uns mit der Implementierung des Algorithmus beschäftigen, schauen wir uns zunächst das Ergebnis an. Zunächst wird der Algorithmus mit der Prozedur init initialisiert:

exec dm_id3.init(1,10);

Das erste Argument bestimmt das Projekt, während das zweite Argument die minimale Anzahl von Beobachtungen in einem Knoten festlegt; in diesem Fall muss jeder Knoten mindestens 10 Beobachtungen enthalten.

Das Projekt nimmt Bezug auf eine Tabelle, welche die Daten des Data Sets "Chess (King-Rook vs. King)" enthält.

Nach der erfolgreichen Initialisierung erfolgt ein Aufruf der Table-Funtion gain. Man erhält den Gain bzw. den Gain Ratio, also wie sehr sich ein Attribut zur weiteren Zerlegung eignet:

select * from table(dm_id3.gain(0)) order by gain desc;

NODE   ATTRIBUTE          INFO      SPLIT_INFO GAIN     
------ ------------------ --------- ---------- ---------- 
0      BLACK_KING_RANK    3.5041597 3.1896893  0.31446969 
0      WHITE_KING_RANK    3.5041597 3.2139022  0.29025673 
0      BLACK_KING_FILE    3.5041597 3.3190435  0.18511551 
0      WHITE_KING_FILE    3.5041597 3.3388416  0.16531733 
0      WHITE_ROOK_FILE    3.5041597 3.4540855  0.05007348 
0      WHITE_ROOK_RANK    3.5041597 3.4579962  0.04616276 

Man sieht, dass das Attribut "BLACK_KING_RANK" am besten geeignet ist, um eine Zerlegung des Root-Knoten vorzunehmen.

Um den Entscheidungsbaum zu erzeugen, wird die Prozedur make_tree aufgerufen:

exec dm_id3.make_tree(0);

Nun werfen wir einen Blick auf den erzeugten Entscheidungsbaum:

select * from table(dm_id3.show_tree(1));

NODE                              SUPPORT  PREDICTION   CONFIDENCE DEPTH
--------------------------------- -------- ------------ ---------- ------
0: 1 = 1                          28056    fourteen     4553       1
   1: BLACK_KING_RANK = 1         3664     eight        591        2
      9: WHITE_KING_FILE = a      372      eleven       89         3
         49: WHITE_ROOK_RANK = 1  36       draw         10         4
         50: WHITE_ROOK_RANK = 2  48       eight        19         4
         51: WHITE_ROOK_RANK = 3  48       ten          25         4
         52: WHITE_ROOK_RANK = 4  48       twelve       22         4
         53: WHITE_ROOK_RANK = 5  48       eleven       13         4
         54: WHITE_ROOK_RANK = 6  48       fourteen     13         4
         55: WHITE_ROOK_RANK = 7  48       fourteen     13         4
         56: WHITE_ROOK_RANK = 8  48       eleven       12         4
      10: WHITE_KING_FILE = b     620      ten          163        3
...

Man sieht unter anderem, dass das Attribut "BLACK_KING_RANK" tatsächlich für die Zerlegung des Root-Knoten verwendet wurde.

So viel für heute... in den kommenden Postings erfolgt dann die eigentliche Implementierung der eben gezeigten Funktionen.

Alert-Log per XMLQuery auslesen

Heute geht's nochmal um XQuery, um damit das Alert-Log, welches seit 11g auch im XML-Format vorliegt, in SQL verfügbar zu machen.

Dazu wird zunächst ein Directory angelegt, welches das Alert-Log im XML-Format enthält:

create directory alert_log as '/u01/app/oracle/diag/rdbms/ora112/ora112/alert/';

Leider ist das Alert-Log nicht well-formed, da es kein Root-Element besitzt; hier besteht also noch Handlungsbedarf.

Wenngleich man auch mit etwas PL/SQL ein Root-Element hinzufügen kann, habe ich mich hier dafür entschieden, zyklisch eine Kopie des Alert-Logs zu erstellen; diese Kopie wird dann mithilfe von Betriebssystem-Mitteln um ein Root-Element erweitert.

Unter Unix genügt es den Start- und End-Tag in jeweils einer Datei abzuspeichern, um dann eine Verkettung mit cat vorzunehmen.

touch start_tag
echo "" > start_tag
touch end_tag
echo "" > start_tag
cat start_tag log.xml end_tag > log_well_formed.xml

Jetzt kann man auf dieser Grundlage einen View erstellen, der mithilfe eines FLOWR-Ausdrucks, das Alert-Log relational aufbereitet:

create view v_alert_log as
(
select
 to_date(substr(x.log_time, 1, 19), 'YYYY-MM-DD"T"HH24:MI:SS') as log_time
 x.log_org_id,
 x.log_comp_id,
 x.log_msg_id,
 x.log_type,
 x.log_group,
 x.log_level,
 x.log_host_id,
 x.log_host_addr,
 x.log_pid,
 x.log_version,
 x.log_txt
from
 xmltable
 (
  'for $i in //msg return $i'
  passing 
   xmltype(bfilename('ALERT_LOG', 'log_well_formed.xml'), nls_charset_id('AL32UTF8'))
  columns
   "LOG_TIME" varchar2(255) path '/msg/@time',
   "LOG_ORG_ID" varchar2(255) path '/msg/@org_id',
   "LOG_COMP_ID" varchar2(255) path '/msg/@comp_id',
   "LOG_MSG_ID" varchar2(255) path '/msg/@msg_id',
   "LOG_TYPE" varchar2(255) path '/msg/@type',
   "LOG_GROUP" varchar2(255) path '/msg/@group',
   "LOG_LEVEL" integer path '/msg/@level',
   "LOG_HOST_ID" varchar2(255) path '/msg/@host_id',
   "LOG_HOST_ADDR" varchar2(255) path '/msg/@host_addr',
   "LOG_PID" integer path '/msg/@pid',
   "LOG_VERSION" varchar2(255) path '/msg/@version',
   "LOG_TXT" varchar2(4000) path '/msg/txt'
 ) x
);

Jede Änderung an der Quelle wird direkt durch den View reflektiert.

Eine mögliche Abfrage könnte zum Beispiel wie folgt aussehen:

select 
 * 
from 
 v_alert_log 
where 
 log_txt like '%ORA-%' and
 sysdate - log_time <= 3;

Ermittlung von Feiertagen per Table Function

Heute geht's um die Ermittlung von Feiertagen in Deutschland; und das wieder mal per Table Function.

Da viele Feiertage (z.B. Rosenmontag) eine Abhängigkeit zum Osterdatum besitzen, benötigt man zunächst eine Funktion zur Berechnung des Osterdatums; genauer gesagt zur Bestimmung von Ostersonntag:

create or replace function easter_day
(
 p_year_in in integer
)
return date
as
 v_k integer;
 v_m integer;
 v_s integer;
 v_a integer;
 v_d integer;
 v_r integer;
 v_og integer;
 v_sz integer;
 v_oe integer;
 v_os integer;
 v_day integer;
 v_month integer;
begin
 v_k := floor(p_year_in / 100);
 v_m := 15 + floor((3 * v_k + 3) / 4) - floor((8 * v_k + 13) / 25);
 v_s := 2 - floor((3 * v_k + 3) / 4);
 v_a := mod(p_year_in, 19);
 v_d := mod((19 * v_a + v_m), 30);
 v_r := floor(v_d / 29) + (floor(v_d / 28) - floor(v_d / 29)) * floor(v_a / 11);
 v_og := 21 + v_d - v_r;
 v_sz := 7 - mod((p_year_in + floor(p_year_in / 4) + v_s), 7);
 v_oe := 7 - mod(v_og - v_sz, 7);
 v_os := v_og + v_oe;
 if (v_os <= 31) then
  v_day := v_os;
  v_month := 3;
 else
  v_day := v_os - 31;
  v_month := 4;
 end if;
 return to_date(v_day || '.' || v_month || '.' || p_year_in, 'DD.MM.YYYY');
end easter_day;

Dabei handelt es sich im Wesentlichen um die ergänzte Osterformel von Carl-Friedrich Gauß; diese wurde durch Hermann Kinkelin und Christian Zeller dahingehend ergänzt, dass Ausnahmeregeln in der Formel berücksichtigt werden. Siehe dazu auch den entsprechenden Eintrag auf der deutschsprachigen Seite von Wikipedia.

Kommen wir nun zur eigentlichen Table-Function, für die zuächst die Objekt-Typen erstellt werden müssen:

create type holiday_t as object
(
 holiday_date date,
 holiday_name varchar2(30),
 holiday_desc varchar2(100)
);
/
create type holiday_tab as table of holiday_t;
/

Die eigentliche Table-Function liefert nun die "Feiertage" der jeweiligen Jahre:

create or replace function german_holidays
(
 p_year_start_in in integer,
 p_year_end_in in integer
) 
return holiday_tab pipelined
as
 v_easter_day date;
begin
 for y in p_year_start_in .. p_year_end_in loop
  
  v_easter_day := easter_day(y);

  pipe row (
   holiday_t(
    to_date('01.01.' || y, 'DD.MM.YYYY'),
    'Neujahrstag',
    'Gesetzlicher Feiertag'));

  pipe row (
   holiday_t(
    to_date('06.01.' || y, 'DD.MM.YYYY'),
    'Heilige Drei Könige',
    'Nur BW, BY und ST'));

  pipe row (
   holiday_t(
    v_easter_day - interval '52' day,
    'Weiberdonnerstag',
    '-'));

  pipe row (
   holiday_t(
    v_easter_day - interval '48' day,
    'Rosenmontag',
    '-'));

  pipe row (
   holiday_t(
    v_easter_day - interval '46' day,
    'Aschermittwoch',
    '-'));

  pipe row (
   holiday_t(
    v_easter_day - interval '3' day,
    'Gründonnerstag',
    '-'));

  pipe row (
   holiday_t(
    v_easter_day - interval '2' day,
    'Karfreitag',
    'Gesetzlicher Feiertag'));

  pipe row (
   holiday_t(
    v_easter_day + interval '1' day,
    'Ostermontag',
    'Gesetzlicher Feiertag'));

  pipe row (
   holiday_t(
    to_date('01.05.' || y, 'DD.MM.YYYY'),
    'Tag der Arbeit',
    'Gesetzlicher Feiertag'));

  pipe row (
   holiday_t(
    v_easter_day + interval '39' day,
    'Christi Himmelfahrt',
    'Gesetzlicher Feiertag'));

  pipe row (
   holiday_t(
    v_easter_day + interval '50' day,
    'Pfingstmontag',
    'Gesetzlicher Feiertag'));

  pipe row (
   holiday_t(
    v_easter_day + interval '60' day,
    'Fronleichnam',
    'Nur BW, BY, HE, NW, RP'));

  pipe row (
   holiday_t(
    to_date('08.08.' || y, 'DD.MM.YYYY'),
    'Augsburger Friedensfest',
    'Nur im Stadtgebiet Augsburg'));

  pipe row (
   holiday_t(
    to_date('08.08.' || y, 'DD.MM.YYYY'),
    'Mariä Himmelfahrt',
    'Nur SL und Teilen von BY'));

  pipe row (
   holiday_t(
    to_date('03.10.' || y, 'DD.MM.YYYY'),
    'Tag der Deutschen Einheit',
    'Gesetzlicher Feiertag'));

  pipe row (
   holiday_t(
    to_date('31.10.' || y, 'DD.MM.YYYY'),
    'Reformationstag',
    'Nur BB, MV, SL, SN und TH'));

  pipe row (
   holiday_t(
    to_date('01.11.' || y, 'DD.MM.YYYY'),
    'Allerheiligen',
    'Nur BW, BY, NW, RP und SL'));

  pipe row (
   holiday_t(
    to_date('11.11.' || y, 'DD.MM.YYYY'),
    'Karnevalsbeginn',
    '-'));

  pipe row (
   holiday_t(
    to_date('25.12.' || y, 'DD.MM.YYYY'),
    '1. Weihnachtstag',
    'Gesetzlicher Feiertag'));

  pipe row (
   holiday_t(
    to_date('26.12.' || y, 'DD.MM.YYYY'),
    '2. Weihnachtstag',
    'Gesetzlicher Feiertag'));

 end loop;
end german_holidays;

Für das Jahr 2012 erhält man dann:

select 
 * 
from 
 table(german_holidays(2012, 2012));

HOLIDAY_DATE  HOLIDAY_NAME                   HOLIDAY_DESC
------------- ------------------------------ ----------------------------
01.01.12      Neujahrstag                    Gesetzlicher Feiertag
06.01.12      Heilige Drei Könige            Nur BW, BY und ST
16.02.12      Weiberdonnerstag               -
20.02.12      Rosenmontag                    -
22.02.12      Aschermittwoch                 -
05.04.12      Gründonnerstag                 -
06.04.12      Karfreitag                     Gesetzlicher Feiertag
09.04.12      Ostermontag                    Gesetzlicher Feiertag
01.05.12      Tag der Arbeit                 Gesetzlicher Feiertag
17.05.12      Christi Himmelfahrt            Gesetzlicher Feiertag
28.05.12      Pfingstmontag                  Gesetzlicher Feiertag
07.06.12      Fronleichnam                   Nur BW, BY, HE, NW, RP
08.08.12      Augsburger Friedensfest        Nur im Stadtgebiet Augsburg
08.08.12      Mariä Himmelfahrt              Nur SL und Teilen von BY
03.10.12      Tag der Deutschen Einheit      Gesetzlicher Feiertag
31.10.12      Reformationstag                Nur BB, MV, SL, SN und TH
01.11.12      Allerheiligen                  Nur BW, BY, NW, RP und SL
11.11.12      Karnevalsbeginn                -
25.12.12      1. Weihnachtstag               Gesetzlicher Feiertag
26.12.12      2. Weihnachtstag               Gesetzlicher Feiertag

Mit Bezug auf das vorherige Posting ergibt sich nun die Möglichkeit, die Zeitdimension um die Feiertage zu erweitern.

Zeitdimension per Table Function erstellen

Heute geht's um die Erstellung einer Zeitdimension mithilfe einer Table-Function. Dazu verwendet man im Wesentlichen die Möglichkeiten der Funktion to_char, wie das folgende Beispiel zeigt.

Zunächst wird der Objekt-Typ erstellt:

create type time_dim_t as object
(
 v_day date,
 v_day_name varchar2(30),
 v_day_of_week integer,
 v_day_of_month integer,
 v_day_of_year integer,
 v_week integer,
 v_week_start date,
 v_week_end date,
 v_iso_week integer,
 v_iso_week_start date,
 v_iso_week_end date,
 v_month integer,
 v_month_name varchar2(30),
 v_month_start date,
 v_month_end date,
 v_month_days integer,
 v_quarter integer,
 v_quarter_name varchar(2),
 v_quarter_start date,
 v_quarter_end date,
 v_quarter_days integer,
 v_year integer,
 v_year_start date,
 v_year_end date,
 v_year_days integer,
 v_is_leap_year varchar2(1)
);
/

Hinzu kommt der Table-Typ:

create type time_dim_tab as table of time_dim_t;
/

Damit sind alle Vorbereitungen getroffen, um die Table-Function zu erstellen:

create or replace function time_dim
(
 p_year_start_in in integer,
 p_year_end_in in integer
) 
return time_dim_tab pipelined
as
 v_day date;
 v_day_name varchar2(30); 
 v_day_of_week integer;
 v_day_of_month integer;
 v_day_of_year integer; 
 v_week integer;
 v_week_start date;
 v_week_end date;
 v_iso_week integer;
 v_iso_week_start date;
 v_iso_week_end date;
 v_month integer;
 v_month_name varchar2(30);
 v_month_start date;
 v_month_end date;
 v_month_days integer;
 v_quarter integer;
 v_quarter_name varchar(2);
 v_quarter_start date;
 v_quarter_end date;
 v_quarter_days integer;
 v_year integer;
 v_year_start date;
 v_year_end date;
 v_year_days integer;
 v_is_leap_year varchar2(1);
begin

 begin

  for y in p_year_start_in .. p_year_end_in loop
   
   v_year := y;
   v_year_start := to_date('01.01.' || v_year, 'DD.MM.YYYY');
   v_year_end := to_date('31.12.' || v_year, 'DD.MM.YYYY');
   v_year_days := v_year_end - v_year_start + 1;
  
   if (v_year_days = 366) then
    v_is_leap_year := 'Y';
   else
    v_is_leap_year := 'N';
   end if;
   
   for q in 1 .. 4 loop
   
    v_quarter := q;
    v_quarter_name := 'Q' || v_quarter;
    case v_quarter_name
     when 'Q1' then
      v_quarter_start := to_date('01.01.' || v_year, 'DD.MM.YYYY');
      v_quarter_end := to_date('31.03.' || v_year, 'DD.MM.YYYY');
     when 'Q2' then
      v_quarter_start := to_date('01.04.' || v_year, 'DD.MM.YYYY');
      v_quarter_end := to_date('30.06.' || v_year, 'DD.MM.YYYY');
     when 'Q3' then
      v_quarter_start := to_date('01.07.' || v_year, 'DD.MM.YYYY');
      v_quarter_end := to_date('30.09.' || v_year, 'DD.MM.YYYY');
     when 'Q4' then
      v_quarter_start := to_date('01.10.' || v_year, 'DD.MM.YYYY');
      v_quarter_end := to_date('31.12.' || v_year, 'DD.MM.YYYY');
    end case;
    v_quarter_days := v_quarter_end - v_quarter_start + 1;
    
    for m in to_number(to_char(v_quarter_start, 'MM')) .. 
             to_number(to_char(v_quarter_end, 'MM')) loop
   
     v_month := m;
     v_month_start := to_date('01.' || v_month || '.' || v_year, 'DD.MM.YYYY');
     v_month_end := last_day(v_month_start);
     v_month_days := v_month_end - v_month_start + 1;
     v_month_name := to_char(v_month_start, 'MONTH');
 
     for d in 1 .. v_month_days loop
     
      v_day := to_date(d || '.' || v_month || '.' || v_year, 'DD.MM.YYYY');
      v_day_of_week := to_number(to_char(v_day, 'D'));
      v_day_of_month := to_number(to_char(v_day, 'DD'));
      v_day_of_year := to_number(to_char(v_day, 'DDD'));
      v_day_name := to_char(v_day, 'DAY');
      v_week := to_number(to_char(v_day, 'WW'));
      v_week_start := trunc(v_day, 'WW');
      v_week_end := v_week_start + interval '6' day;
      v_iso_week := to_number(to_char(v_day, 'IW'));
      v_iso_week_start := trunc(v_day, 'IW');
      v_iso_week_end := v_iso_week_start + interval '6' day;
 
      pipe row
     (
      time_dim_t
      (
       v_day,
       v_day_name,
       v_day_of_week,
       v_day_of_month,
       v_day_of_year,
       v_week,
       v_week_start,
       v_week_end,
       v_iso_week,
       v_iso_week_start,
       v_iso_week_end,
       v_month,
       v_month_name,
       v_month_start,
       v_month_end,
       v_month_days,
       v_quarter,
       v_quarter_name,
       v_quarter_start,
       v_quarter_end,
       v_quarter_days,
       v_year,
       v_year_start,
       v_year_end,
       v_year_days,
       v_is_leap_year 
      )
     );  
     end loop;
    
    end loop;
    
   end loop;
   
  end loop;

 end;

end;

Ein Aufruf der Table-Function für die Jahre 2011 und 2012 sieht dann wie folgt aus:

select * from table(time_dim(2011, 2012));

Auf der Grundlage dieser Table-Function kann dann eine Zeitdimension erstellt werden; sei es als Star- oder Snowflake-Schema, was durch entsprechende Projektionen erreicht werden kann.

Eine Ergänzung wäre die Angabe von Feiertagen, welche oftmals im iCal-Format bereitstehen. Vielleicht widme ich ein weiteres Posting diesem Thema, um zu zeigen, wie man dieses Format in SQL bereitstellen kann.

Aggregatsfunktion zur Berechnung der Entropie

Heute geht's um die Berechnung der Entropie mithilfe einer benutzerdefinierten Aggregatsfunktion. Die Entropie ist ein Maß aus der Informationstheorie und findet unter anderem Anwendung in Data Mining Algorithmen wie ID3 und dessen Nachfolger C4.5.

Die Formel zur Berechnung lautet wie folgt:

Dabei bezeichnet c die Anzahl der Ausprägungen, ni die Anzahl der Elemente der i-ten Ausprägung und n die Anzahl aller Elemente.

Anhand folgender Daten soll die Entropie für das Attribut credit_rating berechnet werden.

NAME     CREDIT_RATING
-------- -------------
SMITH    A
BLAKE    B
ALLEN    A
KING     B
JONES    B
CLARK    B
JAMES    B

Man erhält die Entropie zu:

Es sollte aufgefallen sein, dass die Formel nicht der Arbeitsweise von SQL und der von Aggregatsfunktionen entspricht, da die Gesamtanzahl in der Regel erst am Ende zur Verfügung steht.

Durch einige Umformungen erhält man jedoch die gewünschte Form:

Nun lassen sich die Bestandteile der Formel den Phasen von Aggregatsfunktionen zuordnen:

create or replace type entropy_t as object
(
  v_sum integer,
  v_entropy number,
  
  static function odciaggregateinitialize(
   sctx in out entropy_t) return number,
  
  member function odciaggregateiterate(
   self in out entropy_t, 
   value in integer) return number,
  
  member function odciaggregateterminate(
   self in entropy_t, 
   returnvalue out number,
   flags in number) return number,
  
  member function odciaggregatemerge(
   self in out entropy_t, 
   ctx2 in entropy_t) return number
);
/

create or replace type body entropy_t is

  static function odciaggregateinitialize(
   sctx in out entropy_t) return number is
  begin
   sctx := entropy_t(0,0.00);
   return odciconst.success;
  end;
  
  member function odciaggregateiterate(
   self in out entropy_t, 
   value in integer) return number is
  begin
   self.v_entropy := self.v_entropy - value * log( 2, value );
   self.v_sum := self.v_sum + value;
   return odciconst.success;
  end;
  
  member function odciaggregateterminate(
   self in entropy_t, 
   returnvalue out number, 
   flags in number) return number is
  begin
   returnvalue := ( self.v_entropy / self.v_sum ) +  log( 2, self.v_sum );
   return odciconst.success;
  end;
  
  member function odciaggregatemerge(
   self in out entropy_t, 
   ctx2 in entropy_t) return number is
  begin
   return odciconst.success;
  end;

end;
/

Nun fehlt nur noch die entsprechende Funktion.

create or replace function entropy (input integer) return number
parallel_enable aggregate using entropy_t;

Ein Aufruf der Funktion kann dann wie folgt erfolgen:

select
 entropy(count(*)) entropy
from
 customer
group by
 credit_rating;

ENTROPY
---------
0.8631206 

Dynamischer Rückgabetyp bei Table Functions

Heut geht's um den Einsatz von Table Functions, die einen dynamischen Rückgabetyp besitzen.

Der normale Fall sieht dabei wie folgt aus:

Man erstellt einen Objekt-Typ und gibt die Attribute mit Namen und Typ an.

create type val_t as object
(
 v1 number,
 v2 number,
 v3 number
);
/

Jetzt fehlt noch der Table-Typ von dem gerade erstellen Objekt-Typ.

create type val_tab as table of val_t;
/

Die Table-Function soll nun das Einmaleins bis 3 ausgeben.

create or replace function multiplication_table return val_tab pipelined is
 v_column_count integer := 3;
begin
 for i in 1..v_column_count loop
  pipe row ( val_t (i*1,i*2,i*3) );
 end loop;
 return;
end multiplication_table;

Soweit so gut, jedoch wollen wir auch das Einmaleins bis 5, 10, 100 oder einer anderen natürlichen Zahl n, ohne dafür jeweils eine eigene Table Function samt den notwendigen Objekt-Typen erstellen zu wollen.

Dazu sind einige Funktionen zu implementieren, welche es erlauben, den Rückgabetyp dynamisch zu erstellen. Für die Aufgabe ergibt sich folgenden Struktur:

create or replace type multiplication_table as object
(
 v_row_types anytype,
 v_column_count integer,
 v_rows_processed integer,
 
 static function show_table(
  p_column_count_in in integer
 ) return anydataset pipelined using multiplication_table,
 
 static function ODCITableDescribe(
  rtype out anytype, 
  p_column_count_in in integer
 ) return number,
 
 static function ODCITablePrepare(
  sctx out multiplication_table, 
  tf_info SYS.ODCITabFuncInfo,
  p_column_count_in in integer
 ) return number,
 
 static function ODCITableStart(
  sctx in out multiplication_table, 
  p_column_count_in in integer
 ) return number,

 member function ODCITableFetch(
  self in out multiplication_table, 
  nrows in number, 
  rws out anydataset
 ) return number,

 member function ODCITableClose(
  self in multiplication_table 
 ) return number
);

Dabei wird der Rückgabetyp durch die Funktion ODCITableDescribe beschrieben, also die Anzahl und Typen der Attribute festgelegt. Konkret für die Lösung der Aufgabe bedeutet dies, das für das Einmaleins bis n auch n Attribute vorhanden sein müssen. Die Funktion ODCITableFetch liefert dann die Zeilen zurück, indem die Bestandteile des Objekt-Typs mit Werten gefüllt werden.

create or replace type body multiplication_table as

  static function ODCITableDescribe(
   rtype out anytype, 
   p_column_count_in in integer
  ) return number as
   v_record_structure anytype;
  begin
   anytype.begincreate(dbms_types.typecode_object, v_record_structure);
   for i in 1 .. p_column_count_in loop
    v_record_structure.addattr(   
       ANAME     => '#' || to_char(i),
       TYPECODE  => dbms_types.typecode_number,
       PREC      => null,
       SCALE     => null,
       LEN       => null,
       CSID      => null,    
       CSFRM     => null,
       ATTR_TYPE => null
     );
   end loop;
   v_record_structure.endcreate();
   anytype.begincreate(dbms_types.typecode_table, rtype); 
   rtype.setinfo(
    null, 
    null, 
    null, 
    null, 
    null, 
    v_record_structure, 
    dbms_types.typecode_object, 
    0); 
   rtype.endcreate(); 
   return odciconst.success;
  end ODCITableDescribe;

  static function ODCITablePrepare(
   sctx out multiplication_table, 
   tf_info SYS.ODCITabFuncInfo,
   p_column_count_in in integer
  ) return number as
   prec pls_integer; 
   scale pls_integer; 
   len pls_integer; 
   csid pls_integer; 
   csfrm pls_integer; 
   record_desc anytype; 
   aname varchar2(30); 
   dummy pls_integer;
  begin 
   dummy := tf_info.RetType.GetAttrElemInfo(
    null, 
    prec, 
    scale, 
    len, 
    csid, 
    csfrm, 
    record_desc, 
    aname); 
   sctx := multiplication_table(record_desc, p_column_count_in, 0); 
   return odciconst.success; 
  end ODCITablePrepare;

  static function ODCITableStart(
   sctx in out multiplication_table, 
   p_column_count_in in integer
  ) return number as
  begin
   return odciconst.success; 
  end ODCITableStart;

  member function ODCITableFetch(
   self in out multiplication_table, 
   nrows in number, 
   rws out anydataset
  ) return number as
  begin
   rws := null; 
   if (self.v_rows_processed < self.v_column_count) then
    self.v_rows_processed := self.v_rows_processed + 1;
    anydataset.begincreate(dbms_types.typecode_object, self.v_row_types, rws); 
    rws.addinstance;
    rws.piecewise();
    for i in 1 .. self.v_column_count loop
     rws.setnumber(self.v_rows_processed * i);
    end loop;
    rws.endcreate;
   end if;
   return odciconst.success;
  end ODCITableFetch;

  member function ODCITableClose(
   self in multiplication_table 
  ) return number as
  begin
   return odciconst.success; 
  end ODCITableClose;

end;

Ein Aufruf mit dem Argument 4 liefert dann:

select * from table(multiplication_table.show_table(4));

#1      #2      #3      #4                     
------- ------- ------- -------
1       2       3       4                      
2       4       6       8                      
3       6       9       12                     
4       8       12      16                     

Während die Wahl des Arguments 6 einen Objekt-Typ mit zwei zusätzlichen Spalten zur Folge hat:

select * from table(multiplication_table.show_table(6));

#1      #2      #3      #4      #5      #6                 
------- ------- ------- ------- ------- -------
1       2       3       4       5       6              
2       4       6       8       10      12           
3       6       9       12      15      18               
4       8       12      16      20      24              
5       10      15      20      25      30
6       12      18      24      30      36

Weitere Informationen dazu findet man im Data Cartridge Developer's Guide.

Etwas OLAP zum Jahresende

In diesem Post geht's um eine Aufgabenstellung und deren Lösung mit OLAP. Dabei wird angenommen, dass Kunden eine Umfrage, bestehend aus fünf Seiten, durchführen können. Nun ist man daran interessiert wie viele Kunden die Umfrage komplett durchgeführt haben und wie viele Kunden man bei jedem Schritt "verloren" hat.

Zunächst die Struktur der Tabelle:

create table survey
(
 cust_id integer constraint pk_survey primary key,
 last_page integer constraint nn_survey_last_page not null
);

Für den Datenbestand gehen wir davon aus, dass eine komplette Durchführung der Umfrage ein eher seltenes Ereignis ist; für die Simulation eignet sich somit die Poisson-Verteilung.

Der folgende anonyme PL/SQL-Block erzeugt den Datenbestand:

declare
 v_lamda integer := 1;
 v_max_page integer := 5;
 v_page integer;
 v_value number;
begin
 for i in 1..10000 loop
  v_page := 1;
  v_value := 0.00;
  loop
   v_value := v_value + ( -( 1 / v_lamda ) * ( ln (1 - dbms_random.value) ) );
   if (v_value < 1) then
    v_page := v_page + 1;
   else
    exit;
   end if;   
  end loop;
  if (v_page > v_max_page) then
   v_page := v_max_page;
  end if;
  insert into survey (cust_id, last_page)
  values (i, v_page);
 end loop;
end;

Dabei ist Lamda der Erwartungswert der Poisson-Verteilung; in diesem Fall 1. Man geht also davon aus, dass die meisten Kunden bereits nach der ersten Seite aussteigen. Die Lösung verwendet OLAP-Funktionalität, um erst die kumulierte Anzahl zu bestimmen und dann mithilfe von LAG den Verlust zu berechnen.

with cumulative_count as
(
 select
  last_page,
  sum( count(*) ) over 
  (order by last_page desc rows between unbounded preceding and current row) n
 from
  survey
 group by
  last_page
)
select
 last_page,
 n,
 ( lag(n) over (order by last_page) - n ) lost 
from
 cumulative_count;

Damit erhält man folgenden Output:

LAST_PAGE              N                      LOST                   
---------------------- ---------------------- ---------------------- 
1                      10000                                         
2                      6255                   3745                   
3                      2638                   3617                   
4                      809                    1829                   
5                      188                    621                    

Man kann sehr gut erkennen, dass die Simulation auf der Basis der Poisson-Verteilung eine realistische Datenbasis erzeugt hat und nur 188 Kunden den Prozess komplett durchgeführt haben.

XMLQuery und XMLTable im Einsatz

Heute geht's um die Verwendung von XMLQuery und XMLTable an einem kleinen Beispiel. Dabei soll lediglich die grundsätzliche Verwendung demonstriert und die Einsatzmöglichkeiten aufgezeigt werden.

Hier zunächst die Datenbasis:

create table xml_test
(
 company_id integer,
 xml xmltype
);

insert into xml_test(company_id, xml)
values
(
 1,
 '<company>
   <employees>
    <emp empno="7839" ename="KING" sal="5000" deptno="10">
     <emp empno="7566" ename="JONES" sal="2975" deptno="20">
      <emp empno="7788" ename="SCOTT" sal="3000" deptno="20">
       <emp empno="7876" ename="ADAMS" sal="1100" deptno="20"/>
      </emp>
      <emp empno="7902" ename="FORD" sal="3000" deptno="20">
       <emp empno="7369" ename="SMITH" sal="800" deptno="20"/>
      </emp>
     </emp>
     <emp empno="7782" ename="CLARK" sal="2450" deptno="10">
      <emp empno="7934" ename="MILLER" sal="1300" deptno="10"/>
     </emp>
     <emp empno="7698" ename="BLAKE" sal="2850" deptno="30">
      <emp empno="7499" ename="ALLEN" sal="1600" deptno="30"/>
      <emp empno="7521" ename="WARD" sal="1050" deptno="30"/>
      <emp empno="7654" ename="MARTIN" sal="1250" deptno="30"/>
      <emp empno="7844" ename="TURNER" sal="1500" deptno="30"/>
      <emp empno="7900" ename="JAMES" sal="950" deptno="30"/>
     </emp>
    </emp>
   </employees>
   <departments>
    <dept deptno="10">
     <dname>ACCOUNTING</dname>
     <loc>NEW YORK</loc>
     <budget>13000</budget>
    </dept>
    <dept deptno="20">
     <dname>RESEARCH</dname>
     <loc>DALLAS</loc>
     <budget>15000</budget>
    </dept>
    <dept deptno="30">
     <dname>SALES</dname>
     <loc>CHICAGO</loc>
     <budget>11000</budget>
    </dept>
    <dept deptno="40">
     <dname>OPERATIONS</dname>
     <loc>BOSTON</loc>
     <budget>10000</budget>
    </dept>
   </departments>
  </company>'
);

Dabei handelt es sich um eine Tabelle mit einer Spalte vom Typ XMLType. Diese Spalte enthält ein XML-Dokument mit der Mitarbeiter-Hierarchie samt Informationen über die Abteilungen. Nun betrachten wir drei Aufgabenstellungen:

(1) Abteilungen relational speichern (ähnlich Tabelle DEPT)
(2) Mitarbeiter relational speichern (ähnlich Tabelle EMP)
(3) Mitarbeiter im XML-Dokument nicht in einer Hierarchie darstellen

Wir beginnen mit der ersten und zugleich einfachsten Aufgabe. Mithilfe von XMLTable und einem XQuery-FLWOR-Ausdruck erhält man:

select 
 x.deptno,
 x.dname,
 x.loc,
 x.budget
from
 xml_test,
 xmltable
 (
  '
   for $dept in //dept order by $dept/deptno return $dept
  '
  passing xml
  columns
   "DEPTNO" integer path '/dept/@deptno',
   "DNAME" varchar2(15) path '/dept/dname',
   "LOC" varchar2(15) path '/dept/loc',
   "BUDGET" integer path '/dept/budget'   
 ) x;

DEPTNO     DNAME           LOC                 BUDGET
---------- --------------- --------------- ----------
        10 ACCOUNTING      NEW YORK             13000
        20 RESEARCH        DALLAS               15000
        30 SALES           CHICAGO              11000
        40 OPERATIONS      BOSTON               10000

Dabei handelt es sich bei //dept um eine Pfadangabe mit XPath, welche alle entsprechenden Department-Tags im XML-Dokument findet. Diese Tags werden separat durchlaufen und sodann zurückgegeben.

Die nächste Aufgabe gestaltet sich etwas schwieriger, da die Elemente der Mitarbeiter geschachtelt sind, um die Hierarchie der Mitarbeiter abzubilden. Nun geht es also darum dies rückgängig zu machen, um eine Struktur, wie aus der EMP-Tabelle bekannt, zu erhalten.

Der FLWOR-Ausdruck durchläuft im for-Teil zunächst alle Mitarbeiter-Tags; für jeden Mitarbeiter wird dann im let-Teil der Parent (sofern vorhanden) ermittelt; jetzt werden die Informationen in ein XML-Element mit den entsprechenden Attributen gepackt und zurückgegeben.

select 
 x.empno,
 x.ename,
 x.mgr,
 x.sal,
 x.deptno
from
 xml_test,
 xmltable
 (
  '
   for $emp in //emp
   let $mgr := $emp/../@empno
   return 
    element emp 
    { 
     attribute empno {$emp/@empno}, 
     attribute ename {$emp/@ename},
     attribute mgr {data($mgr)},
     attribute sal {$emp/@sal},
     attribute deptno {$emp/@deptno}
    }
  '
  passing xml
  columns
   "EMPNO" integer path '/emp/@empno',
   "ENAME" varchar2(15) path '/emp/@ename',
   "MGR" integer path '/emp/@mgr',
   "SAL" integer path '/emp/@sal',
   "DEPTNO" integer path '/emp/@deptno'
 ) x;

EMPNO      ENAME                  MGR        SAL     DEPTNO
---------- --------------- ---------- ---------- ----------
      7839 KING                             5000         10
      7566 JONES                 7839       2975         20
      7788 SCOTT                 7566       3000         20
      7876 ADAMS                 7788       1100         20
      7902 FORD                  7566       3000         20
      7369 SMITH                 7902        800         20
      7782 CLARK                 7839       2450         10
      7934 MILLER                7782       1300         10
      7698 BLAKE                 7839       2850         30
      7499 ALLEN                 7698       1600         30
      7521 WARD                  7698       1050         30
      7654 MARTIN                7698       1250         30
      7844 TURNER                7698       1500         30
      7900 JAMES                 7698        950         30

Man erhält damit die gewünschte Struktur.

Die dritte Aufgabenstellungen zielt nun auf eine Transformation des XML-Dokuments; pro Mitarbeiter soll es nun ein XML-Element geben mit dem zusätzlichen Attribut für den Vorgesetzen.

Die Lösung erfolgt wieder mithilfe von XQuery und der Funktion XMLQuery, die in der Select-Klausel zum Einsatz kommt. Der wesentliche Unterschied liegt darin, dass jetzt ein XML-Dokument zurückkommt.

select
 xmlquery
 (
  '
   
   
   {
   for $emp in //emp
   let $mgr := $emp/parent::emp/@empno
   return 
    
     {data($emp/@ename)}
     {data($emp/@sal)}
     {data($emp/@deptno)}
    
    }
    
    
    {
     for $dept in //dept
     return $dept
    }
    
    
  '
  passing xml
  returning content
 ) x
from 
 xml_test;

X
----------------------------------
<company>
 <employees>
  <emp empno="7839" mgr="">
   <ename>KING</ename>
   <sal>5000</sal>
   <deptno>10</deptno>
  </emp>
  <emp empno="7566" mgr="7839">
   <ename>JONES</ename>
   <sal>2975</sal>
   <deptno>20</deptno>
  </emp>
  <emp empno="7788" mgr="7566">
   <ename>SCOTT</ename>
   <sal>3000</sal>
   <deptno>20</deptno>
  </emp>
  <emp empno="7876" mgr="7788">
   <ename>ADAMS</ename>
   <sal>1100</sal>
   <deptno>20</deptno>
  </emp>
  <emp empno="7902" mgr="7566">
   <ename>FORD</ename>
   <sal>3000</sal>
   <deptno>20</deptno>
  </emp>
  <emp empno="7369" mgr="7902">
   <ename>SMITH</ename>
   <sal>800</sal>
   <deptno>20</deptno>
  </emp>
  <emp empno="7782" mgr="7839">
   <ename>CLARK</ename>
   <sal>2450</sal>
   <deptno>10</deptno>
  </emp>
  <emp empno="7934" mgr="7782">
   <ename>MILLER</ename>
   <sal>1300</sal>
   <deptno>10</deptno>
  </emp>
  <emp empno="7698" mgr="7839">
   <ename>BLAKE</ename>
   <sal>2850</sal>
   <deptno>30</deptno>
  </emp>
  <emp empno="7499" mgr="7698">
   <ename>ALLEN</ename>
   <sal>1600</sal>
   <deptno>30</deptno>
  </emp>
  <emp empno="7521" mgr="7698">
   <ename>WARD</ename>
   <sal>1050</sal>
   <deptno>30</deptno>
  </emp>
  <emp empno="7654" mgr="7698">
   <ename>MARTIN</ename>
   <sal>1250</sal>
   <deptno>30</deptno>
  </emp>
  <emp empno="7844" mgr="7698">
   <ename>TURNER</ename>
   <sal>1500</sal>
   <deptno>30</deptno>
  </emp>
  <emp empno="7900" mgr="7698">
   <ename>JAMES</ename>
   <sal>950</sal>
   <deptno>30</deptno>
  </emp>
 </employees>
 <departments>
  <dept deptno="10">
   <dname>ACCOUNTING</dname>
   <loc>NEW YORK</loc>
   <budget>13000</budget>
  </dept>
  <dept deptno="20">
   <dname>RESEARCH</dname>
   <loc>DALLAS</loc>
   <budget>15000</budget>
  </dept>
  <dept deptno="30">
   <dname>SALES</dname>
   <loc>CHICAGO</loc>
   <budget>11000</budget>
  </dept>
  <dept deptno="40">
   <dname>OPERATIONS</dname>
   <loc>BOSTON</loc>
   <budget>10000</budget>
  </dept>
 </departments>
</company>

Natürlich gibt es zu XQuery noch viel mehr zu sagen und zu schreiben; in einem anderen Post. Dennoch sollen diese Beispiel die Einsatzmöglichkeiten und die grundsätzliche Verwendung innerhalb der Datenbank aufzeigen.

Binäre Genauigkeit bei der Verwendung von BINARY_DOUBLE

Heute geht's um die Verwendung der Datentypen BINARY_FLOAT bzw. BINARY_DOUBLE und eine mögliche Fehlerquelle. Der folgende anonyme PL/SQL-Block demonstriert das Problem:

declare
 v_value binary_double := 0.0d;
begin
 for i in 1 .. 10 loop
  v_value := v_value + 0.1d;
 end loop;
 if (v_value = 1.0d) then
  dbms_output.put_line('v_value  = 1.0');
 else
  dbms_output.put_line('v_value != 1.0');
 end if;
end;
/

v_value != 1.0

Das Ergebnis verwundert, denn man erwartet, dass 10 mal 0,1 genau 1 ergibt. Die Ursache liegt in der binären Darstellung von 0,1; die Datentypen besitzen eben "nur" eine binäre Genauigkeit.

Statt einem Vergleich auf Gleichheit, der bei Gleitkommazahlen generell nicht zu empfehlen ist, überprüft man, ob die absolute Abweichung zum Vergleichswert sehr gering ist. Die gewählte Genauigkeit ist je nach Anwendung verschieden; zum Beispiel 10^-8.

Durch eine Anpassung des vorherigen PL/SQL-Blocks erhält man nun das erwartete Ergebnis:

declare
 v_value binary_double := 0.0d;
begin
 for i in 1 .. 10 loop
  v_value := v_value + 0.1d;
 end loop;
 if (abs(v_value - 1.0d) < 1e-8d) then
  dbms_output.put_line('v_value  = 1.0');
 else
  dbms_output.put_line('v_value != 1.0');
 end if;
end;
/

v_value  = 1.0

Zur Vereinfachung könnte man diese Funktionalität in eine benannte Funktion auslagern:

create or replace function equal(
 p_value_in in binary_double,
 p_comparison_value_in in binary_double,
 p_precision_in in binary_double default 1e-8d
)
return boolean is
begin
 return abs(p_value_in - p_comparison_value_in) < p_precision_in;
end equal;
/

Zeichenketten mit PL/SQL zerlegen

Mit Bezug auf den vorherigen Post möchte ich heute eine Lösung mithilfe von PL/SQL aufzeigen und die beiden Möglichkeiten vergleichen; insbesondere hinsichtlich der Performance.

create or replace function split_string_plsql(
 p_list_in in varchar2,
 p_delimiter_in in varchar2
 )
return string_nt pipelined
is
 v_before_pattern integer;
 v_after_pattern integer;
 v_list varchar2(32767) := p_list_in;
begin
 loop
  v_before_pattern := regexp_instr(v_list, p_delimiter_in, 1, 1, 0, 'c');
  if (v_before_pattern = 0) then
   pipe row(v_list);
   exit;
  else
   pipe row(substr(v_list, 1, v_before_pattern - 1));
  end if;
  v_after_pattern := regexp_instr(v_list, p_delimiter_in, 1, 1, 1, 'c');
  v_list := substr(v_list, v_after_pattern);
  exit when v_list is null;
 end loop;
 return;
exception
 when others then
  return;
end split_string_plsql;
/

Auch damit kann man Zeichenketten zerlegen, wobei das Trennzeichen auch durch einen regulären Ausdruck angegeben werden kann.

select 
 column_value
from 
 table(split_string_plsql('a....b...c....d', '[.]+'));

COLUMN_VALUE
-------------
a
b
c
d       

Kommen wir nun zum Performance-Duell der beiden Varianten. Dafür steht eine Tabelle bereit, welche 100.000 Zeichenketten enthält, die jeweils durch ein Semikolon in fünf Felder unterteilt sind.

select * from strings_to_split;

STRING
------------------------------------------------------------------------------------
BnHZjfREKnUMdSbwOBflCRbqZISiKbLtraWEIuLpEKQCilFrRp;CZXLlbwZHuGcyCOqQlNnmTMwnlDhhryVH
hmwVoAdyOXKsTBhow;YrKdmqKbsSSvuPbtZTrPUPAkxfmNechcYrVkLSJfPnjvsaHwdk;iVxogWXxrQSsDRK
ofFhkZoHaKNqHgbPVjbQOywpNAdLMxabPAa;LgklKsjnwNxmTbriHCNABgisxWOjqLnBoRMaOSONbwiIosMS

acwjSJDzUBDWolfKMhtGwizwJpZvKMipMnuGKsRhbSpDzHHvqh;yIBOHkSVedWmpgoyczdxsoAiKvqGrzWYD
LbqZeIZjgeHOQRRDd;EAjqQJErVzkouioxcPLxYMgIEHERANYuGaqnhIDwpJTCIYeGWZ;NqejlJHoIQWcmca
acbqstRZeAhlKPbgSHexKEOPzEEkpYPkvez;QXYvDNXPtAkoGQvUnqqpagfbhShJZegBinXkgfurKoCVQAYX

ELbJayqFFdsDRcoHYmvqLxfsKqgDzNhmVBoHzYtcNYjLTzwaIL;qjVLHPBINaLXrbegLQAiklKeBexbTTCWn
VYoBXJBhLwlErEucT;hgMSqrOTmaiEfBvkMtynhtcwTodPZeyeIfltOCoLPkboCdwIfC;fmnEQBVqiQWNyuG
UxtDkwceZckXYFyqJBNiOgdWTJkPmGWFYiG;OslJbHXKdVALzjdFbeYcoGeTXCzjyKrlQqISRYXMkgpVDmpr

ztfMZluktZSOwtMIeQIBRLKNyrtUKogyddXQWLbCsjnTFwpTmL;nHDbrxlYhjabiWDpfSEnLWAvVXIzgkDhb
NPYwOBEAwmJZvZSmK;jApmGJeLaXioFJxpAelEugPrxkRZNIirzVWKqFYqweDHNymmoK;zUyCCgkGLURMCat
EzvOVgwIoLIeCeluQtMKkXkcieHSbRpjCDc;TTjxzLccEhSfXFgbxaKVndDhxymsrOlyWOjtcWoPSqPznLjP

LWgGKQSVpqaOabvBYAbUrPQaVbomxyaxOXVvPojYkDkPqNrozP;gafSCgxUsHPtTENvKpPfaJDMkhdIOVqUM
dRdeMsNDehCdxzvEF;sRxDbQIJaQCopFlgWxQGJOUwhDHDHonWZLYSlVmrugAiToLDAt;DJJZCaVEGjFvgAc
bGbaXYQeRodCgAixLMQOpxvPfBqQSrWHHJu;MkUeKsmMFCISPSzTldvrGIRrqQnmYxXPQUNHDzcTsGNeyBJC

OOPamDAHRipKbVWWaSScmdjIWFJbunDBBnXKTRceqsJEpOcnxQ;dwffwkUYBgqEJSWNUvnzAQKbgcsLwTWmY
ZSzmVMZqKWlxNFezc;OdEHsJZYrsEqLaGJEvnimLFJkxdQGgEGpAbOwxxJsqLXzwTbgk;wsqFmybhYypdegs
gEHXETxmyGmIZkQiZIwlrEQizNlbcqlXQeX;BmYSjCeyeWPJHgvbVjwbgSsygJiJPlJJMIjScmvQNUapgJMw

...

Nun sollen die Zeichenketten in jeweils fünf Zeichenketten zerlegt werden, wodurch eine Tabelle mit 500.000 Zeilen entsteht. Beginnen wir mit der Java-Variante aus dem vorherigen Post:

create table sub_strings as 
 select 
  t.* 
 from 
  strings_to_split s, 
  table(split_string(s.string, ';')) t;

Tabelle wurde erstellt.

Abgelaufen: 00:04:37.35

select count(*) from sub_strings;

COUNT(*)
----------
    500000

Jetzt zum Vergleich die PL/SQL-Variante:

create table sub_strings_plsql as 
 select 
  t.* 
 from 
  strings_to_split s, 
  table(split_string_plsql(s.string, ';')) t;

Tabelle wurde erstellt.

Abgelaufen: 00:00:19.81

select count(*) from sub_strings;

COUNT(*)
----------
    500000

Man sieht deutlich, dass die PL/SQL-Variante um ein Vielfaches schneller ist; mehr als 14 mal so schnell.

An dieser Stelle gilt der Dank Carsten Czarski, der mich darauf aufmerksam gemacht hat, wie viel Zeit für das Context-Switching zwischen Java und SQL verloren geht.

Zeichenketten zerlegen mithilfe von Java in der Datenbank

Heute geht's um die Zerlegung von Zeichenketten. Dabei soll die Trennung durch ein Trennzeichen vorgenommen werden, welches auch als regulärer Ausdruck angegeben werden kann. Die Zeichenkette 'a,b,c' enthält drei, durch Kommata getrennte, Elemente. Eine Zerlegung, mit dem Komma als Trennzeichen, soll dann die Elemente 'a', 'b' und 'c' enthalten.

Die Elemente, die aus der Zerlegung hervorgehen, werden in einer Nested Table gespeichert:

create or replace type string_nt as table of varchar2(100);
/

Natürlich kann die Aufgabe auch mit PL/SQL gelöst werden, jedoch enthält Java bereits eine Methode zur Lösung des Problems; die Methode split der Klasse String.

create or replace and compile java source named string_utilities as

import java.sql.Connection;
import java.sql.DriverManager;

import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

public class StringUtilities
{
 public static ARRAY split(String input, String delimiter) throws Exception
 {
  Connection connection = DriverManager.getConnection("jdbc:default:connection:");

  ArrayDescriptor aDesc = ArrayDescriptor.createDescriptor("STRING_NT", connection);

  String[] elements = input.split(delimiter);
 
  return new ARRAY(aDesc, connection, elements);
 }
}
/

Der Java-Code ruft die Methode split auf, um das erste Argument in seine Bestandteile zu zerlegen. Das zweite Argument beschreibt das Trennzeichen als regulären Ausdruck.

Nun fehlt nur noch die Funktion in PL/SQL, um die Methode verwenden zu können.

create or replace function split_string (
 p_string_in in varchar2, 
 p_delimiter_in in varchar2
) return string_nt
is language java name 
'StringUtilities.split(java.lang.String, java.lang.String) return oracle.sql.ARRAY';
/

Da der Rückgabetyp der Funktion eine Collection (Nested Table) ist, erfolgt der Aufruf im FROM-Teil des Select-Statements mit dem Table-Keyword.

select 
 column_value 
from 
 table(split_string('a,b,c,d', ','));

COLUMN_VALUE
-------------
a
b
c
d       

Wie bereits angedeutet, wird das Trennzeichen als regulärer Ausdruck interpretiert. Das folgende Beispiel trennt die Elemente bei einem '#' oder einem '/'.

select 
 column_value 
from 
 table(split_string('a#b/c#d', '(#|/)'));

COLUMN_VALUE
-------------
a
b
c
d     

Durch die Beschreibung als regulärer Ausdruck können auch sehr komplexe Trennzeichen beschrieben werden.

Abschließend sei noch erwähnt, dass die Funktion natürlich auch in PL/SQL verwendet werden kann, wie das folgende Beispiel zeigt:

declare
 v_columns string_nt := split_string('empno;ename;job;sal', ';');
begin
 for i in v_columns.first .. v_columns.last loop
  dbms_output.put_line(i || ': ' || v_columns(i));
 end loop;
end;
/

1: empno
2: ename
3: job
4: sal

Data Warehouse - Modellierung auf der konzeptionellen Ebene

Dieser Post beschäftigt sich mit der Fragestellung, wie das Data Warehouse Modell auf der konzeptionellen Ebene aussieht. Diese Modellierung auf der Grundlage eines Entity Relationship Diagram ist natürlich unabhängig von der physischen Modellierung als Star- oder Snowflake-Schema.

Eine Möglichkeit besteht darin, Dimensionen als Typ und die Ebenen innerhalb einer Dimensionen als Subtypen darzustellen. Die Hierarchie stellt sich dann als 1:n Beziehung zwischen den einzelnen Ebenen dar. Das folgende Beispiel zeigt die Modellierung einer Dimension für Produkte samt den Ebenen und deren Hierarchie:

Der Cube wird dann mit der detailliertesten Ebene der Hierarchie verbunden (im Beispiel die Ebene PR_ITEM):

Dieses Modell ist somit die Grundlage für Star- und Snowflake-Schema. Für ein Snowflake-Shema ergibt sich für die Produkt Dimension dann die folgende physische Struktur:

Das zugehörige Star-Schema für die ausgewählte Dimension sieht wie folgt aus:

Damit wäre die Frage beantwortet, wie man ein Data Warehouse in Form eines Entity Relationship Diagrams, unabhängig von der physischen Implementierung, darstellen kann.

Objekttyp als Spaltentyp verwenden

Heute geht's um die Verwendung von Objekttypen als Spaltentyp in einer Tabelle. Damit besteht die Möglichkeit einen Constraint an einen Typ statt an eine Tabelle zu binden. Das macht insbesondere dann Sinn, wenn der gleiche Typ mit den gleichen Restriktionen in mehreren Tabellen verwendet wird; im Normalfall ist der Constraint pro Tabelle zu erstellen, in der dieser Typ verwendet wird. Als Beispiel dient ein Typ für die Angabe von Stückzahlen, der wie folgt als Objekttyp erstellt wird:

create type qty as object
(
 q integer,
 constructor function qty(q in integer) return self as result,
 member function the_q return integer,
 pragma restrict_references(the_q, wnds)
);
/

Dabei gilt die Restriktion, dass sich die Stückzahl zwischen 0 und 5000 (jeweils inklusive) befinden muss. Dieser Sachverhalt wird im Rahmen des Konstruktors überprüft und gegebenenfalls eine Exception ausgelöst.

create type body qty is
 
 constructor function qty(q in integer) return self as result is
 begin
  if (q >= 0 and q <= 5000) then
   self.q := q;
   return;
  else
   raise_application_error(-20010, 'This is not a valid value for type QTY.');
  end if;
 end;

 member function the_q return integer is
 begin
  return self.q;
 end;
 
end;
/

Die zusätzliche Funktion THE_Q kann später dazu verwendet werden, die Stückzahl als skalaren Typ zu erhalten; im Beispiel einen Wert vom Typ integer. Nun kann dieser Objekttyp als Spaltentyp verwendet werden, wie das folgende Beispiel zeigt:

create table warehouse
(
 product_id integer,
 available qty,
 constraint pk_warehouse primary key (product_id)
);

Bei jedem INSERT-Befehl wird dann der Konstruktor aufgerufen, welcher die notwendige Überprüfungen vornimmt und mitunter eine Exception auslöst; auch bei einem UPDATE-Befehl erfolgt eine enstprechende Überprüfung.

insert into warehouse (product_id, available)
values (1, qty(100));

-- 1 rows inserted

insert into warehouse (product_id, available)
values (2, qty(1000));

-- 1 rows inserted

insert into warehouse (product_id, available)
values (3, qty(10000));

-- SQL-Fehler: ORA-20010: This is not a valid value for type QTY.

Bei Abfragen kann man dann auf die Funktion THE_Q zurückgreifen, um die Stückzahl als integer zu erhalten.

select 
 w.product_id, 
 w.available.the_q() qty_int
from 
 warehouse w;

Natürlich kann der erstellte Typ in mehreren Tabellen verwendet werden. Somit besteht die Möglichkeit einen Constraint pro Typ statt pro Tabelle, in der dieser Typ verwendet wird, zu definieren.

Systematische Herleitung von SQL durch Logik

Heute geht's um die Verwendung von Logik zur systematischen Herleitung von SQL. Gesucht wird eine Abfrage, um die Mitarbeiter auszugeben, welche an allen Projekten beteiligt sind. Dazu werden die bekannten Tabellen EMP und DEPT um die Tabellen PROJECT und EMP_PROJECT erweitert. Die Tabelle PROJECT speichert Projekte während die Tabelle EMP_PROJECT eine Zuordnung von Mitarbeitern zu Projekten vornimmt.

create table project
(
 pno integer not null,
 pname varchar2(15) not null,
 constraint pk_project primary key (pno)
);

create table emp_project
(
 empno integer not null,
 pno integer not null,
 percentage number(5,2) not null,
 constraint pk_emp_project primary key (empno, pno)
);

alter table emp_project
add constraint fk_emp_project_emp
foreign key (empno)
references emp (empno);

alter table emp_project
add constraint fk_emp_project_project
foreign key (pno)
references project (pno);

insert into project (pno, pname)
values (1, 'X100C');
insert into project (pno, pname)
values (2, 'P00A1');
insert into project (pno, pname)
values (3, 'P00A2');

insert into emp_project (empno, pno, percentage)
values (7369, 2, 30.00);
insert into emp_project (empno, pno, percentage)
values (7521, 1, 25.00);
insert into emp_project (empno, pno, percentage)
values (7566, 1, 10.00);
insert into emp_project (empno, pno, percentage)
values (7521, 2, 50.00);
insert into emp_project (empno, pno, percentage)
values (7521, 3, 50.00);
insert into emp_project (empno, pno, percentage)
values (7654, 3, 75.00);
insert into emp_project (empno, pno, percentage)
values (7654, 1, 5.00);
insert into emp_project (empno, pno, percentage)
values (7788, 1, 100.00);
insert into emp_project (empno, pno, percentage)
values (7844, 2, 65.00);
insert into emp_project (empno, pno, percentage)
values (7934, 2, 15.00);
insert into emp_project (empno, pno, percentage)
values (7900, 1, 80.00);

commit;

Kommen wir nun zurück zur Fragestellung, welche Mitarbeiter an allen Projekten beteiligt sind. Alternativ könnte man das auch so formulieren:

"Ausgabe aller Mitarbeiter für die für alle Projekte eine entsprechende Projektzuordnung exstiert".

Genau dafür gibt es den Allquantor (FORALL) und den Existenzquantor (EXISTS) aus der Prädikatenlogik.

FORALL x ( p( x ) ) ist genau dann wahr, wenn für alle x das Prädikat p( x ) wahr ist. Also handelt es sich im Wesentlichen um eine Kurzschreibweise für eine UND-Verknüpfung der Form:

p( x1 ) AND p( x2 ) AND ... AND p( xn )

EXISTS x ( p( x ) ) ist genau dann wahr, wenn ein x existiert, für das dass Prädikat p( x ) wahr ist. Also handelt es sich im Wesentlichen um eine ODER-Verknüpfung der Form:

p( x1 ) OR p( x2 ) OR ... OR p( xn )

Eine mögliche Formulierung mit dem Allquantor (FORALL) und dem Existenzquantor (EXISTS) sieht dann so aus:

{ E } WHERE
       FORALL P ( 
        EXISTS EP ( EP.EMPNO = E.EMPNO AND EP.PNO = P.PNO ) )

Da SQL kein FORALL unterstützt macht man sich folgende Regel zu Nutze:

FORALL x ( p( x ) ) ≡ NOT EXISTS ( NOT p( x ) )

Eine Anwendung auf die erste Formulierung ergibt dann:

{ E } WHERE
       NOT EXISTS P ( 
        NOT EXISTS EP ( EP.EMPNO = E.EMPNO AND EP.PNO = P.PNO ) )

Diese Variante kann man fast eins zu eins in SQL überführen:

select *
from emp e
where not exists
(
 select * 
 from project p
 where not exists
 (
  select *
  from emp_project ep
  where ep.empno = e.empno and ep.pno = p.pno
 )
);

Damit erhält man eine Lösung für die Problemstellung, auf die man so mitunter nicht direkt kommt.

Mit etwas Übung und den notwendigen Kenntnissen aus dem Bereich der Logik kann man damit "trial and error" bei vielen Problemen vermeiden und eine Lösung systematisch aus einer logischen Formulierung herleiten.

Als weiterführende Literatur zu diesem Thema sei das Buch "SQL and Relational Theory" von Chris Date und das Buch "Applied Mathematics for Database Professionals" von Lex de Haan und Toon Koppelaars empfohlen.