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.