Kleine Statistikvorlesung ... Fortsetzung auf einem neuen Blog

This posting is about a german only posting in another blog and therefore german only.

Vor kurzem hatte ich ja das Blog Posting zum Thema Kleine Statistikvorlesung mit der Oracle-Datenbank, in dem ich die einfache lineare Regression gezeigt habe. In der Praxis ist aber öfter die anspruchsvollere multiple Regression mit mehreren Variablen gefordert, wozu es erstmal keine einfache SQL-Funktion gibt.

Aber die Datenbank kann es doch! Wie immer, eigentlich. Thomas Uhren beschreibt in seinem Blog, wie man das mit dem PL/SQL-Paket OLS_REGRESSION hinbekommen kann. Auf jeden Fall mal reinschauen. Viel Spaß beim Lesen.

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.

delete from emp where sal > 2000 / 6 rows deleted.

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.

delete from (select * from emp) where sal > 2000 / 6 rows deleted.

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:

delete from ( select empno from emp e join dept d on (e.deptno = d.deptno) where d.loc = 'DALLAS' ) / 5 rows deleted.

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.

delete from ( select deptno from dept where deptno not in (select deptno from emp) ) / 1 row deleted.

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 ...

Schon gewusst ...? Multi Table Insert

Did you know ..? Multi Table Insert

Normalerweise macht man ein SQL INSERT in genau eine Tabelle - die Syntax ist ja auch INSERT INTO {TABLE}. Wusstet Ihr, dass man mit einem SQL INSERT jedoch auch in meherer Tabellen gleichzeitig einfügen kann. Und ich meine nun nicht das SQL INSERT in eine VIEW und das Verteilen per INSTEAD-OF Trigger (hierzu gab es schonmal ein Blog Posting.

A SQL INSERT normally does the INSERT operation in exactly one table. Did you know that you also can insert data into multiple tables at the same time. And I do not mean creating a view with an INSTEAD OF Trigger (there already was a Blog Posting about that.

Nein, ich meine den sog. Multi-Table-Insert. Dazu ein Beispiel. Zuerst erstellen wir die Zieltabellen, die zugegebenermaßen recht einfach gestrickt sind, aber für ein einfaches Beispiel sollte das reichen ...

Today I'll talk about the multi-table-insert feature of the Oracle database. This is best described with an example. First we will create some tables ...

create table emp10 as select empno, ename, job, hiredate, mgr, sal, comm from emp where 1=0 / create table emp20 as select empno, ename, job, hiredate, mgr, sal, comm from emp where 1=0 /

Und dann probieren wir unseren ersten Multi Table Insert.

And then we'll do our first Multi Table Insert.

insert all into emp10 values (empno, ename, job, hiredate, mgr, sal, comm) into emp20 values (empno, ename, job, hiredate, mgr, sal, comm) select * from emp / 28 rows inserted.

Man sieht, dass danach beide Tabellen gefüllt sind - mit einem einzigen INSERT-Kommando. Doch das ist noch nicht alles. Wie man den Tabellennamen schon ansehen kann, möchten wir bestimmte Zeilen in bestimmte Tabellen haben ... und auch das geht mit dem Multi-Table-Insert.

You'll see that both tables are being populated - with just one SQL INSERT command. But this example was rather simple - As the table names indicate we want to have different rows from the source table into different target tables. That is no problem at all ...

insert all when deptno = 10 then into emp10 values (empno, ename, job, hiredate, mgr, sal, comm) when deptno = 20 then into emp20 values (empno, ename, job, hiredate, mgr, sal, comm) select * from emp / 8 rows inserted.

Und auch das funktioniert - abhängig von der DEPTNO gehen die Zeilen in unterschiedliche Tabellen. Das kann noch mit einer ELSE-Anweisung kombiniert werden ...

That also works. And we can add an ELSE clause to handle "all others".

create table emp_other as select empno, ename, job from emp where 1=0 / insert all when deptno = 10 then into emp10 values (empno, ename, job, hiredate, mgr, sal, comm) when deptno = 20 then into emp20 values (empno, ename, job, hiredate, mgr, sal, comm) else -- Here are the "other" ones - only EMPNO, ENAME and JOB into emp_other values (empno, ename, job) select * from emp / 14 rows inserted.

Bislang wurden stets alle WHEN-Klauseln beachtet - die Klauseln selbst schließen sich ja schon gegenseitig aus. Anders sieht es hier aus ...

In these examples Oracle evaluated all WHEN clauses. Even when a row was processed - the other WHEN clauses were also evaluated. So far this did not make a difference, because the WHEN clauses were mutually exclusive. In the next example we'll have table rows matching multiple WHEN clauses.

create table emp_highsal as select empno, ename, job, sal from emp where 1=0 / insert all when sal > 3000 then into emp_highsal values (empno, ename, job, sal) when deptno = 10 then into emp20 values (empno, ename, job, hiredate, mgr, sal, comm) when deptno = 20 then into emp20 values (empno, ename, job, hiredate, mgr, sal, comm) else -- Here are the "other" ones - only EMPNO, ENAME and JOB into emp_other values (empno, ename, job) select * from emp / 15 rows created.

KING gehört zur DEPTNO 10 und hat ein SAL größer als 3000. Er landet also sowohl in der Tabelle EMP_HIGHSAL, als auch in EMP10. Unter Umständen sollen diejenigen Zeilen, die in EMP_HIGHSAL eingefügt wurden, nicht mehr weiter verarbeitet werden - es soll also nach der ersten passenden WHEN-Klausel aufgehört werden. Auch das geht - statt INSERT ALL schreiben wir dann INSERT FIRST.

KING belongs to DEPTNO 10 as well as the SAL is greater then 3000. So the row is inserted in both EMP_HIGHSAL and EMP10. But there might be cases where the row may only be inserted into one table - evaluation shoud stop after the first matching WHEN clause. To achieve this we use INSERT FIRST instead of INSERT ALL.

insert first when sal > 3000 then into emp_highsal values (empno, ename, job, sal) when deptno = 10 then into emp20 values (empno, ename, job, hiredate, mgr, sal, comm) when deptno = 20 then into emp20 values (empno, ename, job, hiredate, mgr, sal, comm) else -- Here are the "other" ones - only EMPNO, ENAME and JOB into emp_other values (empno, ename, job) select * from emp / 14 rows created.

Die Tatsache, dass viele INSERTs in einem Kommando abgearbeitet werden, kann die Performance bei großen Datenmengen extrem beeinflussen - denn die Quelldaten werden beim Multi-Table-Insert nur einmal durchgearbeitet - würde man einzelne INSERT-Anweisungen machen, würden die Daten für jedes INSERT einmal durchgescannt. Mehr Info in der Dokumentation: http://download.oracle.com/docs/cd/E11882_01/server.112/e16579/transform.htm#i1006530.

Multi Table Inserts can impact performance significantly: If you have a very huge "source" dataset this is being scanned only once - Individual INSERT statements on the other hand would cause the database scan the source data once for each INSERT statement. More information is available in the documentation: http://download.oracle.com/docs/cd/E11882_01/server.112/e16579/transform.htm#i1006530.