29. Oktober 2007

XML Dokumente speichern: Ein Blick unter die Motorhaube ...

Nun, woher kommen die Unterschiede bei Performance- und Speicherverbrauch?
Je nachdem, welche Speicherungsform man wählt, hat die Datenbank unterschiedliche Informationen über die XML-Strukturen. Bei der objektrelationalen Speicherungsform werden die XML-Dokumente als Objekte abgelegt - der Datenbank sind die XML-Strukturen also bekannt und sie kann diese zur Abfrageoptimierung nutzen. Erfolgt nun eine XML-Abfrage auf eine solche Tabelle, so schreibt die Datenbank diese Abfrage vor Ausführung um (Query Rewrite). Tatsächlich ausgeführt wird eine andere Abfrage - und dies lässt sich nachvollziehen.
Übrigens: Mit dem Query Rewrite für Materialized Views oder Function Based Indizes hat dieses XML Query Rewrite nichts zu tun - es wird daher auch keine besondere Init-Parameter-Einstellung und auch kein Systemprivileg (QUERY REWRITE) benötigt.
Zunächst schauen wir uns die Abfrage auf die Tabelle mit objektrelationaler Speicherung an:
select 
  extractvalue(object_value, '/test-xml/name/text()') as kunde_name,
  extractvalue(object_value, '/test-xml/datum/text()') as kauf_datum,
  extractvalue(value(st), '/stueck/id/text()') as stueck_id
from xml_or,
  table(xmlsequence(extract(object_value, '/test-xml/gekaufte-stuecke/stueck'))) (+)  st
Daraus macht die Datenbank vor Ausführung in etwa sowas hier ...
SELECT 
  "SYS_ALIAS_1"."SYS_NC00009$" "KUNDE_NAME",
  "SYS_ALIAS_1"."SYS_NC00008$" "KAUF_DATUM",
  SYS_OP_ATG( [...] ) "STUECK_ID" 
FROM 
  "SCOTT"."XML_OR" "SYS_ALIAS_1",
  TABLE( [...] )
Die Datenbank verwendet also in Wirklichkeit SYS_NC...-Spalten; de-facto wird eine relationale Abfrage ausgeführt. Die EXTRACTVALUE-Funktionen und die XPath-Ausdrücke sind komplett verschwunden - daher wird auch kein XML-Parsing mehr ausgeführt. Das liegt daran, dass die XML-Dokumente tatsächlich als Objekte und diese wiederum in normalen, relationalen Tabellenspalten abgelegt werden. Die Oracle-Datenbank beachtet dabei allerdings alle XML-Besonderheiten, so dass sich die Oracle XML DB doch massiv von einem "naiven XML-Nach-Relational-Mapping" unterscheidet. Schauen wir uns im Gegensatz dazu nun die Abfrage auf die textbasiert gespeicherten Dokumente an. Hierbei kann kein Query Rewrite stattfinden - die Datenbank hat gar keine Objektstrukturen und damit auch keine SYS_NC...-Spalten, mit deren Hilfe die Abfrage optimiert werden könnte. Tatsächlich ausgeführt wird also ...
SELECT 
  EXTRACTVALUE(SYS_MAKEXML("XML_TEXT"."XMLDATA"),'/test-xml/name/text()') "KUNDE_NAME",
  EXTRACTVALUE(SYS_MAKEXML("XML_TEXT"."XMLDATA"),'/test-xml/datum/text()') "KAUF_DATUM",
  EXTRACTVALUE(VALUE(K),'/stueck/id/text()') "STUECK_ID"
 FROM 
  "SCOTT"."XML_TEXT" "XML_TEXT",
  TABLE(
   "SYS"."XMLSEQUENCE"(
     EXTRACT(
      SYS_MAKEXML("XML_TEXT"."XMLDATA"),
      '/test-xml/gekaufte-stuecke/stueck'
     )
   )
  ) "K"
... was der Originalabfrage entspricht. Zur Erinnering: Die sah so aus:
select
  extractvalue(object_value, '/test-xml/name/text()') as kunde_name,
  extractvalue(object_value, '/test-xml/datum/text()') as kauf_datum,
  extractvalue(value(st), '/stueck/id/text()') as stueck_id
from xml_text,
  table(xmlsequence(extract(object_value, '/test-xml/gekaufte-stuecke/stueck'))) (+) st
Die EXTRACTVALUE-Funktionen sind noch da - bei Ausführung der Abfrage findet also ein XML Parsing statt, was die Performance und den Speicherverbrauch (letzter Post) erkärt. Schauen wir uns nun noch die in Oracle11g neue Speicherungsform Binary XML an. Zunächst die Original-Abfrage ...
select
  extractvalue(object_value, '/test-xml/name/text()') as kunde_name,
  extractvalue(object_value, '/test-xml/datum/text()') as kauf_datum,
  extractvalue(value(st), '/stueck/id/text()') as stueck_id
from xml_binary,
  table(xmlsequence(extract(object_value, '/test-xml/gekaufte-stuecke/stueck'))) (+) st
Daraus macht die Datenbank im Rahmen des Query Rewrite in etwa dies hier:
SELECT 
  SYS_XMLTYPE2SQL("P"."C_02$") "KUNDE_NAME",
  SYS_XMLTYPE2SQL("P"."C_01$") "KAUF_DATUM",
  SYS_XMLTYPE2SQL("P1"."C_01$") "STUECK_ID" 
FROM 
  "SCOTT"."XML_BINARY" "XML_BINARY",
  XPATHTABLE( [...] ) "P",
  XPATHTABLE( [...] ) "P1"
Auch hier verschwinden die EXTRACTVALUE-Funktionen vollständig. Die durch Query Rewrite veränderte Query führt ebenfalls kein XML-Parsing mehr durch, vielmehr nutzt sie die im binären XML enthaltenen Strukturinformationen zur Ausführung und erreicht so eine bessere Performance. Spitzenreiter ist jedoch, wie im letzten Post schon festgestellt, die objektrelationale Variante. Wie man dies auch mit Ausführungsplänen feststellen kann und was das für die Indizierung von XML bedeutet ... Stay tuned!

25. Oktober 2007

XML Dokumente speichern: Die Wahl der Speicherungsformen ...

Heute wird das Thema XML-Speicherung fortgesetzt. Der letzten Post endete damit, dass die Speicherungsform für das XML doch massiven Einfluß auf die Performance hat. Doch welche Speicherungsformen gibt es überhaupt?
  • Textbasiert (CLOB)
  • Objektrelational
  • Neu in Oracle11g: Binary XML
Die jeweils verwendete Speicherungsform gibt man in der Storage-Clause der Tabelle an. Gibt man nichts an, so nimmt die Datenbank die einfachste Option: die textbasierte Speicherung. Nur ist diese, wenn man in die Dokumente hinein abfragen möchte, die langsamste Variante. Im diesem Post seht Ihr, wie Ihr die anderen Speicherungsformen nutzen und was das (Performance!) ausmachen kann. Für die objektrelationale Speicherungsform ist es wichtig, dass ein XML Schema vorliegt. Objektrelationale Speicherung bedeutet ja, dass die Datenbank die XML-Dokumente tatsächlich als Objekte ablegt - dazu muss sie allerdings wissen, wie die XML-Dokumente aussehen. Mit dem XML Schema machen wir der Datenbank die XML-Strukturen bekannt. Nun bauen wir uns also einen einfachen Testcase:
  1. Der folgende Code registriert das XML Schema, welches für die objektrelationale Speicherung benötigt wird, in der Datenbank. So werden die XML-Strukturen der Datenbank bekannt gemacht. Zum Parameter schemaurl ist noch zu sagen, dass dies ein einfacher Name ist; die Adresse muss nicht gültig sein. Man kann jeden beliebigen Text nehmen; mit Mondfinsternis würde es genauso funktionieren.
    begin
      dbms_xmlschema.deleteschema(
        schemaurl => 'http://sql-plsql-de.blogspot.com/xml-tests/schema.xsd',
        delete_option => dbms_xmlschema.delete_cascade_force
      );
    end;
    /
    
    begin
      dbms_xmlschema.registerschema(
        schemaurl => 'http://sql-plsql-de.blogspot.com/xml-tests/schema.xsd',
        schemadoc => '<?xml version="1.0" encoding="UTF-8"?>
                      <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
                       <xs:element name="test-xml">
                        <xs:complexType>
                         <xs:sequence>
                          <xs:element name="datum" type="xs:date"/>
                          <xs:element name="name" type="xs:string"/>
                          <xs:element name="gekaufte-stuecke">
                           <xs:complexType>
                            <xs:sequence>
                             <xs:element name="stueck" minOccurs="0" maxOccurs="unbounded">
                              <xs:complexType>
                               <xs:sequence>
                                <xs:element name="id" type="xs:string"/>
                                <xs:element name="name" type="xs:string"/>
                               </xs:sequence>
                              </xs:complexType>
                             </xs:element>
                            </xs:sequence>
                           </xs:complexType>
                          </xs:element>
                         </xs:sequence>
                        </xs:complexType>
                       </xs:element>
                      </xs:schema>'
      );
    end;
    /
    
  2. Als nächstes erzeugen wir die Tabellen:
  3. -- Tabelle für die textbasierte Speicherung
    create table xml_text of xmltype
    xmltype store as clob
    /
    
    -- Tabelle für die objektrelationale Speicherung
    create table xml_or of xmltype
    xmltype store as object relational
    xmlschema "http://sql-plsql-de.blogspot.com/xml-tests/schema.xsd"
    element "test-xml"
    /
    
    -- Tabelle für die Speicherung als BINARY XML
    create table xml_binary of xmltype
    xmltype store as binary xml
    allow nonschema
    /
    
  4. Nun geht es daran, die Tabellen zu füllen. Dazu generieren wir die XML-Dokumente mit ein wenig PL/SQL-Code. Nun wird es bereits interessant: Je nach Speicherungsform wird der Vorgang unterschiedliche Zeit benötigen. Man erfährt also schon etwas über die Speicherungsformen. Es macht also Sinn, sich die Ausführungszeiten im SQL*Plus mit set timing on anzeigen zu lassen.
    -- Prozedur erzeugen ...
    create or replace procedure populate_xml_table(
      p_count in number,
      p_table in varchar2
    ) is
      v_xmltext varchar2(32767); 
    begin
      for i in 1..p_count loop
        v_xmltext := '<test-xml>';
        v_xmltext := v_xmltext ||'<datum>'||to_char(sysdate + round(dbms_random.value*2000), 'YYYY-MM-DD')||'</datum>';
        v_xmltext := v_xmltext ||'<name>Kunde #'||to_char(round(dbms_random.value*10000))||'</name>';
        v_xmltext := v_xmltext ||'<gekaufte-stuecke>';
        for s in 1..round(dbms_random.value*20) loop
          v_xmltext := v_xmltext ||'<stueck>';
          v_xmltext := v_xmltext ||'<id>'||to_char(10000 + round(dbms_random.value * 1000))||'</id>';
          v_xmltext := v_xmltext ||'<name>Stueck #'||to_char(10000 + round(dbms_random.value*1000))||'</name>';
          v_xmltext := v_xmltext ||'</stueck>';
        end loop;
        v_xmltext := v_xmltext ||'</gekaufte-stuecke>';
        v_xmltext := v_xmltext ||'</test-xml>';
        execute immediate
           'insert into '||p_table||
           ' values (xmltype(:1).createschemabasedxml(''http://sql-plsql-de.blogspot.com/xml-tests/schema.xsd''))'
        using v_xmltext;
      end loop;
    end;
    /
    sho err
    
    set timing on
    
    -- 1. 5.000 XML-Dokumente für textbasierte Speicherung erzeugen
    
    begin
      populate_xml_table(5000, 'XML_TEXT');
    end;
    /
    
    PL/SQL-Prozedur erfolgreich abgeschlossen.
    
    Abgelaufen: 00:00:13.25
    
    -- 2. 5.000 XML-Dokumente für objektrelationale Speicherung erzeugen
    --    Kopieren der Dokumente aus XML_TEXT in XML_OR
    
    insert into xml_or (select * from xml_text)
    / 5000 Zeilen wurden erstellt. Abgelaufen: 00:00:43.56 -- 3. 5.000 XML-Dokumente für Speicherung als BINARY XML erzeugen -- Kopieren der Dokumente aus XML_TEXT in XML_BINARY insert into xml_binary (select * from xml_text)
    / 5000 Zeilen wurden erstellt. Abgelaufen: 00:00:02.07
  5. Nun haben wir drei Tabellen ... mit jeweils unterschiedlichen Speicherungsformen. Das lässt sich auch anhand der Pseudespalte XMLDATA feststellen ...
    set long 160
    set lines 80
       
    select e.xmldata from xml_text e where rownum = 1;
    
    XMLDATA
    --------------------------------------------------------------------------------
    <test-xml><datum>2008-08-18</datum><name>Kunde #731</name><gekaufte-stuecke><stu
    eck><id>10383</id><name>Stueck #10482</name></stueck><stueck><id>10808</id><name
    
    select e.xmldata from xml_or e where rownum = 1;
    
    XMLDATA(SYS_XDBPD$, datum, name, gekaufte-stuecke(SYS_XDBPD$, stueck(SYS_XDBPD$,
    --------------------------------------------------------------------------------
    test-xml1690_T(XDB$RAW_LIST_T('13070000000102'), '10.01.08', 'Kunde #4172', geka
    ufte-stuecke1691_T(XDB$RAW_LIST_T('1301000080800A'), stueck1693_COLL(stueck1692_
    T(XDB$RAW_LIST_T('130300000001'), '10354', 'Stueck #10579'), stueck1692_T(XDB$RA
    
    select e.xmldata from xml_binary e where rownum = 1;
    
    XMLDATA
    --------------------------------------------------------------------------------
    9F01039E000000C83EC4C0092441323031332D30332D3239C00A2B384B756E6465202331393335C8
    1F59C817C8C004129A3130333036C00C2B3853747565636B20233130303532D9D7D5C004129A3130
    
    Selektiert man die Tabelle "normal", so bekommt man in allen drei Fällen das gleiche XML zurück; es ist eben nur unterschiedlich gespeichert ...
  6. Nun kommen dann endlich die Tests: Wir bereiten die Inhalte nochmals, wie im letzten Post, relational auf. Das SQL dazu sieht dann wie folgt aus (um die Zeit auszublenden, die das SQL*Plus braucht, um alle Ergebnisse darzustellen, wird ein SELECT COUNT(*) außenrum gesetzt (wenn Ihr das Ergebnis selbst sehen wollt, führt einfach nur das innere SELECT aus):
    -- relationales Aufbereiten der textbasiert abgelegten XML-Dokumente
    
    select count(*) from (
    select
      extractvalue(object_value, '/test-xml/name/text()') as kunde_name,
      extractvalue(object_value, '/test-xml/datum/text()') as kauf_datum,
      extractvalue(value(st), '/stueck/id/text()') as stueck_id
    from xml_text,
      table(xmlsequence(extract(object_value, '/test-xml/gekaufte-stuecke/stueck'))) (+) st
    )
    /
    
      COUNT(*)
    ----------
         50027
    
    1 Zeile wurde ausgewählt.
    
    Abgelaufen: 00:00:05.43
    
    -- relationales Aufbereiten der objektrelational abgelegten XML-Dokumente
    
    select count(*) from (
    select
      extractvalue(object_value, '/test-xml/name/text()') as kunde_name,
      extractvalue(object_value, '/test-xml/datum/text()') as kauf_datum,
      extractvalue(value(st), '/stueck/id/text()') as stueck_id
    from xml_or,
      table(xmlsequence(extract(object_value, '/test-xml/gekaufte-stuecke/stueck'))) (+) st
    )
    /
    
      COUNT(*)
    ----------
         50027
    
    1 Zeile wurde ausgewählt.
    
    Abgelaufen: 00:00:00.17
    
    -- relationales Aufbereiten der als BINARY XML abgelegten XML-Dokumente
    
    select count(*) from (
    select
      extractvalue(object_value, '/test-xml/name/text()') as kunde_name,
      extractvalue(object_value, '/test-xml/datum/text()') as kauf_datum,
      extractvalue(value(st), '/stueck/id/text()') as stueck_id
    from xml_binary,
      table(xmlsequence(extract(object_value, '/test-xml/gekaufte-stuecke/stueck'))) (+) st
    )
    /
    
      COUNT(*)
    ----------
         50027
    
    1 Zeile wurde ausgewählt.
    
    Abgelaufen: 00:00:00.67
    
    Am besten lasst Ihr die Abfragen zweimal laufen und nehmt das zweite Ergebnis; dann hat man die Abfragezeiten einer "warmen" Datenbank. Die Unterschiede sind mithin recht eindrucksvoll:
    • Textbasiert: 5,43 Sekunden
    • Objektrelational: 0,2 Sekunden
    • Binary XML: 0,7 Sekunden
    Vergleicht man textbasiert mit objektrelational, so ergibt sich Faktor 25. Die Auswahl der richtigen Speicherungsform ist also ganz entscheidend für den (Projekt-)Erfolg. Diese Unterschiede kann man durch keine Tuning-Maßnahme mehr aufholen. Schauen wir uns nun den Speicherverbrauch der Abfragen an: Dazu öffnet man am besten drei Sessions und führt in jeder Session die Abfrage auf eine der Tabellen aus. Vorher setzt man mit in jeder Session einen Aufruf auf DBMS_APPLICATION_INFO ab, um einen "Marker" in der View V$SESSION zu setzen:
    begin
      dbms_application_info.set_action('TESTBASED[BINARY_XML][OBJECT_RELATIONAL]');
    end;
    /
    Als SYS lässt sich der Speicherverbrauch der Abfragen nun leicht feststellen:
    select
      ss.sid,
      s.action,
      sa.name,
      ss.value
    from v$sesstat ss, v$sysstat sa, v$session s
    where sa.statistic# = ss.statistic# and sa.statistic# = 25 and s.sid=ss.sid
    and s.username = 'SCOTT'
    
           SID ACTION               NAME                                VALUE
    ---------- -------------------- ------------------------------ ----------
           134 BINARY_XML           session pga memory                1177752
           143 TEXTBASED            session pga memory                7469208
           144 OBJECT_RELATIONAL    session pga memory                1439896
    
    Man sieht deutlich, dass die textbasierte Speicherungsform nicht nur am langsamsten ist, sondern auch den meisten Speicherplatz beansprucht: 7,1MB - die anderen beiden Varianten sind mit 1,1 bzw. 1.4 MB doch wesentlich bescheidener. Woher kommen diese Unterschiede ...? Stay tuned!

21. Oktober 2007

XML-Dokumente relational aufbereiten: mit SQL!

XML wurde ja bereits schon einige Male in diesem Blog behandelt - heute gehen wir nochmal quasi "zurück zu den Grundlagen". Bereits seit Oracle 9.2 gebt es sehr umfassende XML-Funktionen in der Datenbank. Man kann nicht nur XML aus relationalen Tabellen erzeugen, sondern auch als solches mit dem Datentyp XMLTYPE speichern. Ein richtig schönes Feature ist die Möglichkeit, MXL-Dokumente als relationale Tabelle aufzubereiten. Und das ist einfacher als man denkt - ein Beispiel ...
<blog>
  <name>SQL und PL/SQL</name>
  <autor>Carsten Czarski</autor>
  <themen>
    <thema>XML</thema>
    <thema>PL/SQL</thema>
  </themen>
</blog>
Speichert man das XML in eine Tabelle mit einer Spalte vom Typ XMLTYPE ...
create table xmltest (
  dokument xmltype
)
/

insert into xmltest values (xmltype(
'<blog>
  <name>SQL und PL/SQL</name>
  <autor>Carsten Czarski</autor>
  <themen>
    <thema>XML</thema>
    <thema>PL/SQL</thema>
  </themen>
</blog>'
))
/
... so bereitet dieses SQL-Kommando hier ...
select
  extractvalue(dokument, '/blog/name') as blog_name,
  extractvalue(dokument, '/blog/autor') as blog_autor,
  extractvalue(value(thema), '/thema/text()') as thema
from xmltest,
  table(xmlsequence(extract(dokument, '/blog/themen/thema'))) thema
/
... die Ergebnisse relational auf.
BLOG_NAME            BLOG_AUTOR      THEMA
-------------------- --------------- --------------------
SQL und PL/SQL       Carsten Czarski XML
SQL und PL/SQL       Carsten Czarski PL/SQL
Wichtig sind die Funktionen EXTRACTVALUE und das Konstrukt TABLE(XMLSEQUENCE(EXTRACT))). EXTRACTVALUE extrahiert, wie der Name schon sagt, den Inhalt eines einzelnen XML-Tags oder -Attributs (XML-Namespaces werden übrigens auch unterstützt). Das TABLE(XMLSEQUENCE(EXTRACT()))-Konstrukt braucht man, um hierarchische XML-Dokumente relational aufzubereiten; schließlich gibt es in diesem Beispiel nur ein XML-Dokument (mit zwei Themen) - das Ergebnis der SQL-Abfrage enthält jedoch zwei Zeilen. Die Abfrage "klopft" das XML-Dokument sozusagen flach. Das geht übrigens auch mit mehrfach geschachtelten Hierarchien ...
Und wie sähe es bei einem Blog ohne Themen aus?
insert into xmltest values (xmltype(
'<blog>
  <name>Ein zweiter Blog</name>
  <autor>John Doe</autor>
  <themen/>
 </blog>'
))
/
Das bringt genau das gleiche Ergebnis ... da das XML-Dokument keine Themen enthält, erscheint das XML-Dokument erst gar nicht. Das liegt daran, dass das TABLE(XMLSEQUENCE(EXTRACT()))-Konstrukt den hierarchischen Teil wie eine zweite "virtuelle" Tabelle behandelt und einen Join mit der eigentlichen Tabelle durchführt. Damit gibt es auch schon eine einfache Lösung:
select
  extractvalue(dokument, '/blog/name') as blog_name,
  extractvalue(dokument, '/blog/autor') as blog_autor,
  extractvalue(value(thema), '/thema/text()') as thema
from xmltest,
  table(xmlsequence(extract(dokument, '/blog/themen/thema'))) (+) thema
/

BLOG_NAME            BLOG_AUTOR      THEMA
-------------------- --------------- --------------------
SQL und PL/SQL       Carsten Czarski XML
SQL und PL/SQL       Carsten Czarski PL/SQL
Ein zweiter Blog     John Doe
Mit einem einfachen CREATE VIEW hinterlegt man das Ganze im Dictionary - es sieht damit aus wie eine gewöhnliche Tabelle. Ein anderer User, der die View selektiert, muss gar nicht mehr wissen, wo die Daten eigentlich herkommen. Soviel zur Funktionalität ... Beim nächsten Post werde ich mich ein wenig mit dem Thema Performance beschäftigen. Gerade wenn man dies auf sehr vielen Tabellen durchführt, gibt es hier, je nachdem, wie man das XML in der Datenbank speichert, doch massive Unterschiede. Vorab nur soviel: In diesem Beispiel wurde das XML textbasiert gespeichert - und das ist für ein relationales Aufbereiten auch schon die langsamste und denkbar schlechteste Option. Wie es besser geht ...? Stay tuned!

16. Oktober 2007

SQL-Funktion XMLDIFF: Unterschiede in XML-Dokumenten erkennen

Das Vergleichen zweier XML-Dokumente ist keine einfache Aufgabe - einfach weil gleiche Dinge nach dem dem XML-Standard unterschiedlich ausgedrückt werden können. Hier sind einige Beispiele für textlich unterschiedliche XML-Dokumente, die jedoch inhaltlich gleich sind:
  • XML-Tags ohne Inhalt:
    1. <xml-tag/>
    2. <xml-tag></xml-tag>
  • Whitespace:
    1. <xml-tag>
        </xml-tag>
    2. <xml-tag></xml-tag>
  • Namespace-Präfix:
    1. <a:xml-tag xmlns:a="my-namespace"></a:xml-tag>
    2. <xml-tag xmlns="my-namespace"></xml-tag>
  • Entity Referenzen:
    1. <xml-tag>"</xml-tag>
    2. <xml-tag>&quot;</xml-tag>
An diesen Beispielen ist gut erkennbar, dass ein einfacher Textvergleich von XML-Dokumenten zu kurz greift. Insbesondere die Namespaxe-Präfixe werden häufig aus der Anwendung heraus generiert; so dass unterschiedliche Namespace-Präfixe völlig normal sind. Ein Vergleichsalgorithmus sollte daher erkennen, dass semantisch kein Unterschied zwischen den XML-Dokumenten besteht: gerade wenn Änderungen auf ein anderes System übertragen werden sollen, ist das sehr wichtig - sonst verschwendet man unnötig Netzbandbreite und Rechnerkapazität.
Oracle11g bringt hierfür neue SQL-Funktionen mit: XMLDIFF und XMLPATCH. Ein Beispiel für den Vergleich zweier unterschiedlicher Dokumente:
select xmldiff(
  xmltype('<xml-tag></xml-tag>'),
  xmltype('<xml-tag>Inhalt</xml-tag>')
) from dual
/

<xd:xdiff xsi:schemaLocation="http://xmlns.oracle.com/xdb/xdiff.xsd http://xmlns.oracle.com/xdb/xdiff.xsd" 
          xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd" 
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <?oracle-xmldiff operations-in-docorder="true" output-model="snapshot" diff-algorithm="global"?>
  <xd:append-node xd:node-type="text" xd:parent-xpath="/xml-tag[1]">
    <xd:content>Inhalt</xd:content>
  </xd:append-node>
</xd:xdiff>
Der fettgedruckte Teil repräsentiert die Unterschiede ... und zwar schon direkt in einem "Patch"-fähigen Format: Die Ausgabe der XMLDIFF-Funktion kann direkt von der XMLPATCH-Funktion verwendet werden - um die Änderungen direkt auf ein anderes XML-Dokument anzuwenden. XMLDIFF und XMLPATCH können also kombiniert genutzt werden, um Deltas in XML-Dokumenten zu erkennen und auf andere Systeme zu übertragen. Doch nun kommt die XML-Besonderheit: Vergleicht man mit XMLDIFF zwei textlich verschiedene, inhaltlich jedoch gleiche XML-Dokumente, so ergibt sich folgendes:
select xmldiff(
  xmltype('<xml-tag></xml-tag>'),
  xmltype('<xml-tag/>')
) from dual
/

<xd:xdiff xsi:schemaLocation="http://xmlns.oracle.com/xdb/xdiff.xsd http://xmlns.oracle.com/xdb/xdiff.xsd" 
          xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd" 
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <?oracle-xmldiff operations-in-docorder="true" output-model="snapshot" diff-algorithm="global"?>
</xd:xdiff>

11. Oktober 2007

Auf den Ort kommt es an: Geodaten in der Datenbank!

Wie dem einen oder anderen vielleicht bekannt ist, kann die Oracle-Datenbank auch Geodaten speichern. Dazu gibt es mit SDO_GEOMETRY sogar einen eigenen Datentyp. So repräsentiert ...
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(11.57917, 48.13842, NULL), NULL, NULL)
... die Lokation des Marienplatzes in München. Diese Geodaten-Funktionalität wird übrigens recht verbreitet genutzt; so nutzen die meisten Geodaten-Systeme Oracle als darunterliegende Datenbank. Neben dem einfachen Speichern werden natürlich auch Operationen auf den Geodaten angeboten - Distanzberechnungen, das Finden der nächsten Nachbarn oder andere räumliche Operationen sind mit den Funktionen des PL/SQL-Pakets SDO_GEOM kein Problem - Als Beispiel soll die Datenbank mal berechnen, wie weit es vom Reichstag in Berlin zum Marienplatz ist ...
select 
  sdo_geom.sdo_distance(
    SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(11.57917, 48.13842, NULL), NULL, NULL),
    SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(13.45742, 52.58146, NULL), NULL, NULL),
    1
  ) as entfernung_luftlinie
from dual;


ENTFERNUNG_LUFTLINIE
--------------------
    511930.915008963

1 Zeile wurde ausgewählt.
... also ca. 511km (Luftlinie). Solche Geometrien können nun natürlich einfach zusammen mit Fachdaten in einer Tabelle gespeichert werden - dies sähe dann in etwa so aus:
create table meine_kunden(
  id             number(10),
  name           varchar2(200),
  lokation       sdo_geometry
  :
)
/
In diesem Zusammenhang kam neulich eine Frage auf mich zu: Wie kann man, wenn man von einem bekannten Punkt ausgeht, die Himmelsrichtung eines anderen Punktes bestimmen?. Dazu bietet die Datenbank leider keine eingebaute Funktion an - ist aber zum Glück recht einfach selbst zu implementieren ...
create or replace function getAngle(
  p1 sdo_geometry,
  p2 sdo_geometry
) return number
is
  v_dx number;
  v_dy number;
  v_distance number;

  v_angle number;
begin
  v_dx := p2.sdo_point.x - p1.sdo_point.x;
  v_dy := p2.sdo_point.y - p1.sdo_point.y;

  v_distance := sqrt(v_dx * v_dx + v_dy * v_dy);
  v_angle := acos(v_dy / v_distance) * 360 / 6.28;
  if v_dx < 0 then v_angle := -v_angle; end if; 
  return v_angle;
end;
/
sho err
Probiert man diese neue Funktion nun aus, dann ergibt sich ...
select
  getangle(
    SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(11.57917, 48.13842, NULL), NULL, NULL),
    SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(13.45742, 52.58146, NULL), NULL, NULL)
  ) as winkel
from dual

    WINKEL
----------
22,9273402

1 Zeile wurde ausgewählt.
... und aus dem Winkel lässt sich recht leicht die Himmelsrichtung ableiten - dies hier wäre dann etwa Nord-Nordost

Mehr Interesse am Thema Geodaten in der Datenbank ... ? Dann einfach was in den Kommentar schreiben. Übrigens: Man muss nicht unbedingt die Spatial-Option lizensieren, um die Geodaten-Funktionen nutzen zu können. Die hier vorgestellten Dinge (und mehr) sind komplett in der Standard-Edition enthalten.

Beliebte Postings