Wie man Wartezeiten sinnvoll abkürzt oder wobei hilft mir SDO_JOIN?

Was passiert eigentlich auf einem Standard Notebook mit der Oracle Datenbank an Bord, wenn man alle Geometrien einer Tabelle mit allen (oder so gut wie allen) Geometrien einer anderen Tabellen verschneiden will?

Das habe ich mal ausprobiert am Beispiel des GfK GeoMarketing Deutschlanddatensets. Dazu habe ich mir folgendes Result Set überlegt:

Ich brauche eine Auflistung der

• administrativen Ebene 3 (Name und Gemeindekennziffer der Stadt- und Landkreise)
• administrativen Ebene 4 (Name und Gemeidekennziffer Gemeinden)
• und der den Gemeinden zugeordneten 5-stelligen Postleitzahlen.

Frisch ans Werk und sozusagen straight forward habe ich es mal mit dieser SQL-Abfrage probiert:

select 
  d.id , 
  d.name "Name Stadt-/Landkreis", 
  d.new___z "Anzahl EW Stadt-/Landkreis",
  g.id "ID Gemeinde", 
  g.name "Name Gemeinde", 
  g.new___z "Anzahl EW Gemeinde",
  p.id "PLZ"
from 
  de_municipalities_2010 g,
  de_districts_2010 d,
  de_5digpc_2010 p
where
  substr(g.id,1,5) = d.id and 
  sdo_relate(g.geometry, p.geometry, 'mask=anyinteract') = 'TRUE' and 
  p.id = '16515'
group by
  d.id , 
  d.name, 
  d.new___z,
  g.id, 
  g.name, 
  g.new___z,
  p.id
order by
  d.id , 
  d.name, 
  g.id, 
  g.name,
  p.id
/

Aus Erfahrung weiss man um die Nützlichkeit von Filtern, welche die zu vergleichenden Datenmengen zunächst erst einmal einschränken. Von daher die Betrachtung erst mal nur einer einzigen Postleitzahl.

Ein bisschen mutiger geworden, wird der Filter dann gelockert, um ihn ganz zu entfernen. Denn Ziel war ja, den kompletten Datenbestand beider Tabellen räumlich in Beziehung zu setzen, um auch die Postleitzahlen zu ermitteln, welchen den Gemeinden zugeordnet sind. Das sind knapp 11600 Gemeinden und gut 8200 PLZs.

So und das war es dann auch erst mal für den Nachmittag, den Abend ... und auch am nächsten Morgen hatte sich noch kein Result Set einstellen wollen.

Also muss es wohl noch besser gehen.

Folgende überlegungen führten dann zu weiteren Vesuchen:

• Ich stelle sicher, dass für beide Tabellen der räumliche Index benutzt wird.
• Ich nutze erst mal nur den Primary Filter, um ein annäherndes Ergebnis zu erhalten. Danach wird der exakte Vergleich nur noch mit dem dann schon eingeschränkten Datenset vorgenommen.

Mit diesen beiden Gedanken im Hinterkopf und ggf. auch noch mal einem Blick ins Oracle Spatial Handbuch, wird man bei SDO_JOIN fündig.

Wie funktioniert dabei SDO_JOIN?

select
  rowid1 as municipalities_id,
  rowid2 as plz5_id
from
  table(
    sdo_join(
   'DE_MUNICIPALITIES_2010',
   'GEOMETRY',
   'DE_5DIGPC_2010',
   'GEOMETRY',
   'mask=anyinteract'))
/

Und siehe da: Diese Abfrage dauert keine 2 Minuten.
Allerdings fehlt ja auch noch der Join mit der 3. Tabelle und auch group by und order by sollen schon sein und kosten die Datenbank so einiges an Ressourcen und damit Zeit.

Also muss der Rest auch noch her und ergibt dann diese SQL Abfrage:

with spatial_join_result as (
select
  rowid1 as municipalities_id,
  rowid2 as plz5_id
from
  table(
    sdo_join(
   'DE_MUNICIPALITIES_2010',
   'GEOMETRY',
   'DE_5DIGPC_2010',
   'GEOMETRY',
   'mask=anyinteract')))
select 
  d.name "Name Stadt-/Landkreis", 
  d.new___z "Anzahl EW Stadt-/Landkreis",
  g.name "Name Gemeinde", 
  g.new___z "Anzahl EW Gemeinde",
  p.id "PLZ"  
from
  spatial_join_result a, 
  de_municipalities_2010 g,
  de_5digpc_2010 p,
  de_districts_2010 d  
where
  a.municipalities_id = g.rowid and
  a.plz5_id = p.rowid and
  sdo_relate(g.geometry, p.geometry, 'mask=anyinteract') = 'TRUE' and
  substr(g.id, 1, 5) = d.id  
-- Hierarchische Verknüpfung Admin Ebene 3 mit Admin Ebene 4
group by
  d.name,
  d.new___z,
  g.name,
  g.new___z,
  p.id
order by
  d.name,
  d.new___z,
  g.name,
  g.new___z,
  p.id
/

Jetzt habe ich, was ich mir zum Ziel gesetzt hatte und zwar in einer Zeit von 14 Minuten. Das reicht zwar immer noch für einen Plausch mit den Kollegen zwischendurch. Aber diese Zeit ist erst mal akzeptabel.

Für diejenigen Leserinnen und Leser dieses Blogeintrags, die im Besitz einer (gern auch mehrerer) Oracle DB Enterprise Edition Lizenz sind, sei gesagt, dass sie damit noch mehr Optimierungspotential haben. Das verheissungsvolle Wort an dieser Stelle ist Parallisierung.
Wie das funktioniert, dafür verweise ich jetzt einfach mal auf diesen Link. Denn sonst verpasse ich vielleicht noch meinen Flieger in den Urlaub.