Wann PL/SQL nicht verwendet werden sollte
Dieser Artikel ist ein weiterer einer kleinen Reihe von Fundstellen, die mich bei der Lektüre von Fachliteratur zu Widerspruch gereizt haben. Mir geht es natürlich nicht darum, mit dem Finger auf andere Autoren zu zeigen (ein Teil dieser Reihe behandelt auch eine Fundstelle in meinem PL/SQL-Buch), diese Fundstellen stellen vielmehr den Anker dar, an dem ich ein Thema erläutern möchte, von dem ich glaube, dass es für viele Leser interessant sein könnte. Hier nun ein Rezept in PL/SQL und die Frage: Wann sollte PL/SQL nicht verwendet werden?
Die Fundstelle
In einem Kochbuch für PL/SQL-Code fand ich eine sehr schöne PL/SQL-Prozedur für das Problem, doppelte Einträge aus einer Tabelle zu löschen. Die Prozedur liest sich so:
declare cursor emp_cur is select * from employees order by employee_id; emp_count number := 0; total_count number := 0; begin for emp in emp_cur loop select count(*) into emp_count from employees where employee_id = emp.employee_id; if emp_count > 1 then total_count := total_count + 1; -- do_something end if; end loop; end;
Wunderbar!
Das Problem
Die Tabelle EMPLOYEES gehört zum Schema HR und enthält 107 Zeilen. Das ist nichts. Daher machen wir uns eine realistischere Simulation und verwenden die Zeilen der Tabelle ALL_OBJECTS mit etwa 100.000 Zeilen für unsere Beispiele. Zunächst erstellen wir eine Tabelle und kopieren einige Daten. Die Datenbankobjekte des Benutzers SH werde ich einfach doppelt importieren, damit ich Verstöße gegen einen zu schaffenden Primärschlüssel habe:
SQL> create table test_table( 2 owner varchar2(30), 3 object_id number, 4 object_name varchar2(30), 5 object_type varchar2(30)); Tabelle wurde erstellt. SQL> insert into test_table(owner, object_id, object_name, object_type) 2 select owner, object_id, object_name, object_type 3 from all_objects 4 union all 5 select owner, object_id, object_name, object_type 6 from all_objects 7 where owner = 'SH'; 92553 Zeilen erstellt.
Dann portieren wir das Rezept auf unsere Daten:
SQL> create or replace procedure remove_duplicates 2 as 3 cursor object_cur is 4 select * 5 from test_table 6 order by object_id; 7 l_obj_count number := 0; 8 l_total_count number := 0; 9 begin 10 for obj in object_cur loop 11 select count(*) 12 into l_obj_count 13 from test_table 14 where object_id = obj.object_id; 15 if l_obj_count > 1 then 16 dbms_output.put_line( 17 obj.object_type || ' ' || 18 obj.object_name || ' existiert doppelt.'); 19 end if; 20 end loop; 21 end remove_duplicates; 22 / Prozedur wurde erstellt.
Da ich gerade ohnehin Pause machen wollte, rufe ich die Prozedur auf.
SQL> set serveroutput on; SQL> call remove_duplicates(); TABLE COSTS existiert doppelt. TABLE COSTS existiert doppelt. TABLE PARTITION COSTS existiert doppelt. TABLE PARTITION COSTS existiert doppelt. TABLE PARTITION COSTS existiert doppelt. TABLE PARTITION COSTS existiert doppelt. TABLE PARTITION COSTS existiert doppelt. ... Aufruf wurde abgeschlossen. Abgelaufen: 00:03:14:51
Dieser Code hat offensichtlich eine ganze Menge Probleme, und eines davon ist die Laufzeit, ein anderes die Tatsache, dass jede Dublette doppelt ausgegeben wird. Lassen Sie uns damit beginnen, zu analysieren, auf welche Weise die Dubletten in der Tabelle aufgespürt werden. Wir iterieren über alle Zeilen der Tabelle TEST_TABLE und öffnen für jede ermittelte Zeile einen weiteren Cursor auf TEST_TABLE, um die Anzahl der Zeilen zu zählen, die für diese ID vorhanden sind. Um allem die Krone aufzusetzen, ist die Tabelle TEST_TABLE des äußeren Cursors zudem noch sortiert, obwohl nicht ganz klar ist, wozu das dienen soll. Der äußere Cursor wird also etwa 95.000 Zeilen lesen und im Arbeitsspeicher des Sessionkontextes sortieren, um anschließend für jede Zeile eine Auswertung auf die gleiche Tabelle durchzuführen. Sollte sich dann herausstellen, dass eine Primärschlüsselinformation doppelt gefunden wurde, habe ich meine Dublette gefunden und gebe sie aus.
Ein weiteres Problem dieser Prozedur besteht darin, dass nicht sichergestellt ist, dass die weiteren select-Abfragen in einem lese-konsistenten Zustand zur äußeren Abfrage beantwortet werden. Eine select-Abfrage sperrt keine Daten und solange der Session Isolation Level nicht gerade auf SERIALIZABLE gestellt wurde, wird jede select-Abfrage die Daten der Tabelle so sehen, wie sie zum Zeitpunkt der Abfrage dieser entsprechenden Abfrage sichtbar waren. Die gesamte Abfrage wird in über drei Minuten ausgeführt, dass heißt, dass die letzten select-Abfragen die gleiche Tabelle über drei Minuten später nach Daten gefragt haben, die durch die erste Abfrage vorab abgefragt wurde. Zudem werden immerhin etwa 100.000 select-Abfragen abgeschickt.
Wenn das alles nicht geht – überlege, was für ein Problem Du da hast.
Dieser Ansatz atmet prozeduralen Geist. Das ist wirklich nichts Ehrenrühriges, aber in Datenbanken eben völlig falsch. Das Mantra lautet:
Wenn Du ein Problem hast, löse es in SQL.
Wenn das gar nicht geht, löse es in PL/SQL.
Wenn das gar nicht geht, löse es in Java.
Wenn das gar nicht geht, löse es in C.
Wenn das gar nicht geht – überlege, was für ein Problem Du da hast.
Das Wichtige an diesem Mantra ist die Reihenfolge der einzusetzenden Programmiersprachen. Und das Problem ist, dass die Lösung hier einen entscheidenden Fehler macht.
Lösungsansätze
Versuchen wir, den Code zu retten, indem wir das Problem der Lese-Konsistenz und der vielen select-Anweisungen angehen. Inhaltlich sollen doch wohl die Daten, die nicht als Dublette vorhanden sind, ignoriert werden. Wir wollen also lediglich die Daten sehen, die doppelt auftauchen. Das klingt doch sehr nach folgender select-Anweisung:
SQL> select object_id, object_type, object_name, count(*) anzahl 2 from test_table 3 group by object_id, object_type, object_name 4 having count(*) > 1; OBJECT_ID OBJECT_TYPE OBJECT_NAME ANZAHL ---------- ------------------------- ------------------------- ---------- 92618 TABLE PARTITION COSTS 2 92613 TABLE PARTITION COSTS 2 92660 TABLE PARTITION SALES 2 92694 TABLE TIMES 2 92850 INDEX CHANNELS_PK 2 92891 INDEX PARTITION COSTS_TIME_BIX 2 ... 310 Zeilen ausgewählt. Abgelaufen: 00:00:00.48
Eine erste Verbesserung, das ist klar (achten Sie einmal auf die Zeit). Diese Abfrage würde sich gut für die Verwendung im Cursor der Prozedur eignen, zumal sich dadurch die innere Abfrage komplett erledigt hätte:
SQL> create or replace procedure remove_duplicates 2 as 3 cursor object_cur is 4 select object_id, object_type, object_name, count(*) anzahl 5 from test_table 6 group by object_id, object_type, object_name 7 having count(*) > 1; 8 l_total_count number := 0; 9 begin 10 for obj in object_cur loop 11 dbms_output.put_line( 12 obj.object_type || ' ' || 13 obj.object_name || ' existiert doppelt.'); 14 end loop; 15 end remove_duplicates; 16 / Prozedur wurde erstellt. SQL> call remove_duplicates(); TABLE PARTITION COSTS existiert doppelt. TABLE PARTITION COSTS existiert doppelt. TABLE PARTITION SALES existiert doppelt. TABLE TIMES existiert doppelt. ... Aufruf wurde abgeschlossen. Abgelaufen: 00:00:00.45
Doch ist die Frage, was wir eigentlich tun möchten. Wollten wir nur das Ergebnis auf den Bildschirm schreiben? Dann hätten wir den Aufruf von dbms_output.put_line() auch als Textkonkatenation in der select-Abfrage ersetzen können. Wahrscheinlicher ist jedoch, dass wir die Dubletten nur einfach los werden möchten. Welche Optionen hier zur Verfügung stehen, hängt vom Szenario ab. Spielen wir einmal einige durch.
Wir haben Daten aus einer CSV importiert
Dann wäre das wahrscheinliche Szenario, dass wir die guten Daten in eine Zieltabelle kopieren und die schlechten verwerfen möchten. Das ist aber auch ganz gut ohne PL/SQL machbar:
SQL> create table target_table 2 as select * 3 from test_table 4 where null is not null; Tabelle wurde erstellt. SQL> alter table target_table add constraint pk_target_table 2 primary key(object_id); Tabelle wurde geändert. SQL> insert into test_table(owner, object_id, object_name, object_type) 2 select owner, object_id, object_name, object_type 3 from test_table 4 where rowid in ( 5 select row_id 6 from (select rowid row_id, 7 rank() over ( 8 partition by object_id order by rowid) rang 9 from test_table) 10 where rang = 1); 92243 Zeilen erstellt. Abgelaufen: 00:00:00.17
Die insert-Anweisung kopiert nur die Daten ohne Dubletten. Die Lösung nutzt die analytische Funktion rank(), die eine Reihenfolge über ein Gruppierungskriterium nach einem Sortierkriterium bildet. Da die doppelten Zeilen – nun ja – gleich sind, benötigen wir ein Kriterium, um uns zwischen den beiden zu entscheiden. Hier bietet sich zum Beispiel die ROWID an. In der äußeren Abfrage werden anschließend alle Dubletten ausgefiltert, denn diese haben einen Rang > 1. Die Liste der ROWID der verbleibenden Zeilen wird dann zum Identifizieren der Zeilen verwendet, die eingefügt werden sollen. Das Problem der PL/SQL-Anweisung wäre ja, dass die "guten" Zeilen durch die prozedurale Logik zeilenweise in die Zieltabelle überführt werden müssten, das ist durch diese Anweisung nicht nötig.
Wir möchten Daten bereinigen, um einen Primärschlüssel einzurichten
Eine Löschanweisung direkt in der Tabelle ist etwas aufwändiger, weil eine Entscheidung getroffen werden muss, welche von zwei Zeilen mit gleichen Werten gelöscht werden soll.
Auch diese Lösung lässt sich ohne PL/SQL realisieren:
SQL> delete from test_table 2 where rowid in ( 3 select row_id 4 from (select rowid row_id, 5 rank() over ( 6 partition by object_id order by rowid) rang 7 from test_table) 8 where rang > 1); 310 Zeilen gelöscht. Abgelaufen: 00:00:00.06
Achten Sie auch hier gern auf die Ausführungszeit. Die Lösung nutzt wieder die analytische Funktion rank(). In der äußeren Abfrage werden nur Dubletten gefiltert, denn diese haben einen Rang > 1. Die Liste der ROWID dieser Zeilen wird anschließend zum Bereinigen der Tabelle verwendet.
Die guten ins Töpfchen, die schlechten ins Kröpfchen
Ist das Ziel die Separierung der beiden Datenmengen, stehen ebenfalls mehrere Wege zur Verfügung. Einerseits könnten Sie eine insert-Anweisung in die Zieltabelle mit einer log errors-Klausel ausstatten, die bei einem Primärschlüsselverstoß die verstoßende Zeile in eine Fehlertabelle schreiben wird:
SQL> call dbms_errlog.create_error_log('TARGET_TABLE', 'TARGET_ERR'); Aufruf wurde abgeschlossen. SQL> insert into target_table 2 select * 3 from test_table 4 log errors into target_err 5 reject limit unlimited; 92243 Zeilen erstellt. Abgelaufen: 00:00:00.23 SQL> select count(*) 2 from target_err; COUNT(*) ---------- 310
Alternativ könnte der Weg über eine multi-table-insert-Anweisung laufen. Bei dieser Variante würden die Daten durch eine entsprechende select-Anweisung mit einem Rang ausgestattet und beim Einfügen der Daten eine Fallunterscheidung nach dem Rang ausgeführt:
SQL> insert first 2 when rang = 1 then 3 into target_table(owner, object_id, object_name, object_type) 4 values(owner, object_id, object_name, object_type) 5 else into target_err(owner, object_id, object_name, object_type) 6 values(owner, object_id, object_name, object_type) 7 select t.*, rank() over (partition by object_id order by rowid) rang 8 from test_table t; 92553 Zeilen erstellt. Abgelaufen: 00:00:00.14
Die Aufbereitung der Daten haben Sie schon gesehen, hier entfällt nur die Filterung über die Dubletten, da diese ja für die Fallunterscheidung benötigt werden.
Zusammenfassung
Das Hauptproblem der Programmierung von Datenbanken ist es, der Datenbank möglichst wenig im Weg zu stehen. Genau das aber passiert gerade routinierten Anwendungsprogrammierern häufig. Zu sehr ist man den prozeduralen Ansatz gewohnt, als dass man sich auf die Suche nach einem mengen-orientierten Ansatz macht. SQL hat viele Anstrengungen unternommen, prozedurale Programmierung unnötig zu machen und Standardprobleme direkt aus SQL heraus zu lösen.
Sollten Ihnen einige der eingesetzten Techniken unbekannt gewesen sein (log errors-Klausel, multi-table-insert, analytische Funktionen etwa), verstehen Sie das doch bitte als Einladung, sich wieder einmal mit SQL auseinanderzusetzen. Die Lösungen sind im Regelfall kürzer, schneller, besser zu warten und benötigen keine weitreichenden Unittests. Genug Argumente also, PL/SQL auch einmal im Schrank zu lassen.
Wann PL/SQL nicht verwendet werden sollte
Packages und ihre Initialisierung
Oracle Database: Deterministische Funktionen in PL/SQL
PL/SQL: Über die Bedeutung von Best Practices
Publikationen
- Oracle SQL: Das umfassende Handbuch
- Oracle PL/SQL: Das umfassende Handbuch
- Oracle APEX: Das umfassende Handbuch für Entwickler