Exceptionfault

Im Zeichen des Datenschutzes war ich heute unterwegs um Daten in einer Datenbank unkenntlich zu machen, bevor sie an einen externen Partner ausgeliefert werden. Hierbei tritt das gleiche Problem auf, wie auch bei der sinnvollen Generierung von Testdaten. Die Daten sollen nicht mit realen Daten verknüpfbar sein, allerdings dürfen sie auch nicht kryptisch und unlesbar sein. Ein Beispiel sind z.B. Ortsbezeichnungen. Hierfür habe ich ein tolles Script von Colin Cromm unter http://www.ortsnamengenerator.de/ gefunden, mit dem es schnell möglich ist sich tausende von Ortsnamen zu generieren und für eigene Zwecke zu verwenden. Dieses Posting befasst sich jedoch nicht mit der Generierung von Testdaten, sondern mit einem interessanten Problem, welches mich dabei abgelenkt hat…

Ich habe mir also eine eigene Tabelle für die erfundenen Ortsnamen angelegt um die original Bezeichnungen der Datentabelle mit diesen in zufälliger Reihenfolge zu ersetzen. Dabei nehme ich keine Rücksicht auf die Postleitzahl, diese wird einfach mit einer zufälligen 5-stelligen Zahl überschrieben. Ich habe mir also eine Tabelle mit einer fortlaufenen Nummer und dem Ortsnamen angelegt, zusätzlich eine Sequence, um die fortlaufende Nummer schnell zu erzeugen:

create table ortsnamen (
   nummer number        not null,
   name   varchar2(100) not null);

create sequence ortsids;

INSERT INTO ORTSNAMEN VALUES (ortsids.nextval, 'Unterweiher'));
[snip...]

select min(nummer), max(nummer) from ortsnamen;

MIN(NUMMER)     MAX(NUMMER)
--------------- ---------------
1               1000

Wie man sieht befinden sich in meiner Tabelle nun 1000 Einträge. Mein Update Statement um die Originaldaten zu verfälschen nutzt ein Subselect auf die Tabelle ORTSNAMEN um für jeden originalen Ort einen zufälligen anderen Ort zu ermitteln. Das Subselect ist recht einfach und bedient sich des PL/SQL Packages DBMS_RANDOM welches in diesem Fall Zahlen zwischen 1 und 1000 erzeugen soll. Da DBMS_RANDOM Gleitkommazahlen liefert, sorgt das ROUND() für Ganzzahlen.

select  name, nummer
from    ortsnamen
where   nummer = round(dbms__random.value(1,1000));

Nachdem ich das Statement mehrmals zum Test ausgeführt habe erhielt ich unerwartete Ergebnisse. Teilweise kamen keine Zeilen zurück, teilweise mehr als eine. Da die Nummer eines Ortes aber eindeutig ist, und auch lückenlos von 1 bis 1000 gefüllt ist, müsste jedes SELECT exakt eine Zeile zurückliefern. Als Beweis für die Eindeutigkeit meiner Nummern habe ich die Spalte NUMMER dann als Primärschlüssel der Tabelle definiert. Überraschenderweise trat das Phänomen nun nicht mehr auf. Egal wie oft ich das SELECT ausführte, es kam immer exakt eine Zeile zurück. Primärschlüssel wieder weg: … altes Problem!

Um dem Phänomen ein wenig tiefer auf den Grund zu gehen betrachten wir die Execution Pläne mit beiden Konstellationen, zuerst mit Primärschlüssel:

---------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |     1 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID| ORTSNAMEN    |     1 |    65 |     1   (0)|
|*  2 |   INDEX UNIQUE SCAN         | PK_ORTSNAMEN |     1 |       |     1   (0)|
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("NUMMER"=TRUNC("DBMS__RANDOM"."VALUE"(:SYS_B_0,:SYS_B_1)))

Wie in Zeile 2 zu sehen ist, findet der Zugriff auf den Datensatz natürlich über den Primärschlüssel PK_ORTSNAMEN statt. Die Operation heißt in diesem Fall “access”. Mit einfachen Worten: Hole einen Zufallswert, suche ihn im Index und liefere die entsprechende Zeile zurück. Der Ausführungsplan ohne Primärschlüssel unterscheidet sich hier ein wenig:

--------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |     2 (100)|
|*  1 |  TABLE ACCESS FULL| ORTSNAMEN |    10 |   650 |     2   (0)|
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("NUMMER"=TRUNC("DBMS__RANDOM"."VALUE"(:SYS_B_0,:SYS_B_1)))

Ohne Index bleibt nur ein FULL TABLE SCAN. Jede Zeile muss gelesen werden und mit dem Zufallswert verglichen werden. Die Operation heißt nun nicht mehr “access” sondern “filter”. Die Vermutung liegt nahe, dass die Funktion DBMS_RANDOM bei der Filter Operation für jede Zeile ausgeführt wird und somit auch keine oder gar mehrere Zeilen möglich sind. Nämlich immer dann wenn die zufällig generierte Zahl mit der aktuellen Zeilennummer übereinstimmt.

Als Beweis habe ich eine eigene Funktion geschrieben, die mir einfach ermöglicht die Häufigkeit des Aufrufs zu protokollieren:

CREATE OR REPLACE FUNCTION MYRANDOM(
     v_min IN NUMBER,
     v_max IN NUMBER )
     RETURN   NUMBER
IS
BEGIN
	dbms_output.put_line('function is called!');
	return round(dbms__random.value(v_min, v_max));
END;

Setzt man in SQL*Plus nun die Option “set serveroutput on“, so erscheint für jede Ausführung der Funktion eine Zeile “function is called!” in der Konsole. Tatsächlich wird die Funktion mit Primärschlüssel nur einmal ausgeführt, ohne Primärschlüssel exakt 1000 mal. Somit können Primärschlüssel auch durchaus Einfluss auf die Logik eines Statements haben.

Anmerkung:
In den Codefragmenten wurde DBMS_RANDOM mit 2 Unterstrichen “__” geschrieben, da Wordpress daraus sonst Tag Links generiert welche den Code verunstalten.