18. Juli 2011

DELETE löscht ... und das ist es, oder ...?

DELETE deletes ... that's it ...?
Ein SQL DELETE löscht Zeilen von einer Tabelle - das ist bekannt. Und ein DELETE hat eine WHERE-Klausel, die festlegt, welche Zeilen gelöscht werden sollen - das ist auch bekannt.
A SQL DELETE statement deletes rows from a table - that is nothing new. And a DELETE also has a WHERE clause which determines the rows to delete from the table - this is also no news.
Doch was ist, wenn ich die Zeilen, die gelöscht werden sollen, nur durch einen Join mit einer anderen Tabelle ermitteln kann? Als Beispiel möchte ich alle Mitarbeiter löschen, die in DALLAS arbeiten. Das steht in der EMP-Tabelle aber nicht drin - ich muss in die DEPT-Tabelle gucken. Aus der soll aber nichts gelöscht werden ...
But what if I need to look into another table, to determine the rows to be deleted? As an example, I would like to delete all rows representing employees based in DALLAS. This information is not contained in the EMP table. I need to look into DEPT - but I do not want to delete anything from DEPT ...
Es wird selten gemacht, aber man kann bei SQL DML-Kommandos anstelle einer Tabelle oder View (zur Wirkung von DML auf Views gibt es schon ein Blog-Posting) auch eine SQL-Abfrage verwenden. Ein einfaches Beispiel: Anstelle der Tabelle EMP setze ich ein SELECT ein.
It's not used very frequently - but you can use a SQL query in a SQL DML statement instead of a table or view ( regarding views and DML I already had a blog posting in 2010 Blog-Posting). A simple example: I'll replace the table EMP with a SQL query.
Das funktioniert genauso. Im Grunde genommen gilt von nun an das gleiche wie für DML-Kommandos auf Views. Solange ein DML-Kommando auf eine View ohne INSTEAD OF Trigger funktioniert, kann man die View-Abfrage auch direkt ins DML aufnehmen. Also können wir die o.g. Aufgabenstellung wie folgt lösen:
This works - so we can try to use more complex subqueries in our DELETE statement. Oracle, basically, applies the same rules as for using Views in DML statements - so if a DML statement works on a view, (without an INSTEAD OF trigger) it will also work with the view query directly embedded into the DML command. So we can solve the initial problem as follows:
Wenn ich das SQL wie oben formuliere, könnte man ja die Frage stellen, ob nicht aus beiden Tabellen Zeilen gelöscht werden - man verwendet ja auch beide Tabellen in der SQL-Abfrage. Hier ist Oracle aber recht eindeutig - zunächst löscht Oracle grundsätzlich nur aus einer Basistabelle Zeilen (siehe Dokumentation: General Rule). Dann stellt sich aber die Frage: Wie entscheidet Oracle, aus welche Tabelle die Zeilen gelöscht werden sollen - warum nimmt Oracle die EMP-Tabelle und nicht die DEPT-Tabelle?
But now one question arises: If I have a SQL like the example above: from which table will Oracle delete rows? The SQL query references the EMP table as well as DEPT. So will Oracle delete rows from EMP ... or from DEPT ... or from both ...? As a general rule, Oracle will delete rows from only one base table (see the documentation: General Rule). But how does Oracle decide, from which table ...?
Hier kommt jetzt das Konzept der Key-Preserved Table ins Spiel. In einem Join sind die Tabellen Key-Preserved, die Ihre Schlüssel auch im Ergebnis des Joins behalten. Der obige Join ist so definiert, dass alle Zeilen der Tabelle EMP zurückgeliefert werden und die DEPTNO dupliziert wird. Also ist EMPNO auch im Join-Ergebnis immer noch eindeutig. Für die Tabelle DEPT sieht es anders aus - deren Schlüssel DEPTNO ist im Join-Ergebnis nicht mehr eindeutig. Wichtig ist, dass sich diese Eigenschaft nicht nach der tatsächlichen Ergebnismenge des Joins richtet, sondern nach dem konkreten Join und der Tabellendefinition im Datenbankschema. Daher gibt es in diesem Beispiel genau eine Key-Preserved Table und aus der werden die Zeilen gelöscht. Mehr zum Thema Key-Preserved Tables findet sich (wie immer) in der Dokumentation.
Now we need to look at the concept of a Key-Preserved Table. In a join, the tables which keep their unique key in the join result, are Key-Preserved. In our example we have a join, which keeps all the rows from EMP and which looks up values from DEPT. So in the result the "key" EMP.EMPNO is being preserved and "key" DEPT.DEPTNO is lost (values were duplicated). So the table EMP is key-preserved in that join. Note that this does not depend on the actual result set data, but on the join itself and the table definition in the database schema. So Oracle will delete rows from EMP and not from DEPT. More about Key-Preserved Tables can be found in the documentation.
Nun sind auch andere Beispiele denkbar: ich möchte alle Zeilen aus DEPT löschen, zu denen es keine Einträge in EMP gibt. Hierzu brauche ich noch nicht einmal einen Join.
Another example - now without a join: I want to delete all rows from DEPT which habe no corresponding rows in EMP.
Es bestätigt sich immer wieder: SQL ist eine unglaublich mächtige Sprache. Viele Dinge, die man prozedural recht aufwändig programmieren muss, lassen sich mit SQL sehr elegant erledigen ...
It proves all the time. SQL is a very powerful language - and many problems, which would require cumbersome procedural logic, can often be solved with simple, elegant SQL statements ...

Kommentare:

Anonym hat gesagt…

Hallo Carsten,
danke erstmal für die tollen Tips.
Man könnte die Abfrage auch so formulieren:
delete from emp e where sal > 2000
and e.deptno in
(select d.deptno
from dept d
where d.loc = 'DALLAS');

ist das falsch oder schlecht?

Carsten Czarski hat gesagt…

Hallo,

für dieses einfache Beispiel geht das auf jeden Fall - und ich denke auch, dass der Ausführungsplan der gleiche ist - der Optimizer kann ja was :-). Aber wenn man zum Verknüpfen mehr als einen Schlüssel braucht, kann der Join effizienter sein ...

Grüße

-Carsten

Beliebte Postings