Wednesday, May 8, 2013

Multitable Inserts feature


Multitable inserts allow a single INSERT INTO .. SELECT statement to conditionally, or non-conditionally, insert into multiple tables. This statement reduces table scans and PL/SQL code necessary for performing multiple conditional inserts compared to previous versions. It's main use is for the ETL process in data warehouses where it can be parallelized and/or convert non-relational data into a relational format.

-- Unconditional insert into ALL tables
INSERT ALL
  INTO sal_history VALUES(empid,hiredate,sal)
  INTO mgr_history VALUES(empid,mgr,sysdate)
SELECT employee_id AS empid,
       hire_date AS hiredate,
       salary AS sal,
       manager_id AS mgr
FROM   employees
WHERE  employee_id > 200;


-- Conditionally insert into ALL tables
INSERT ALL
  WHEN sal > 10000 THEN
    INTO sal_history VALUES(empid,hiredate,sal)
  WHEN mgr > 200 THEN
    INTO mgr_history VALUES(empid,mgr,sysdate)
  WHEN 1=1 THEN
    INTO full_history VALUES(empid,mgr,sysdate)
SELECT employee_id AS empid,
       hire_date AS hiredate,
       salary AS sal,
       manager_id AS mgr
FROM   employees
WHERE  employee_id > 200;

-- Insert into the FIRST table with a matching condition
INSERT FIRST
  WHEN sal > 25000  THEN
    INTO special_sal VALUES(deptid,sal)
  WHEN hiredate LIKE ('%') THEN
    INTO hiredate_history_00 VALUES(deptid,hiredate)
  WHEN hiredate LIKE ('%99%') THEN
    INTO hiredate_history_99 VALUES(deptid,hiredate)
  ELSE
    INTO hiredate_history_not_99 VALUES(deptid, hiredate)
SELECT department_id AS deptid,
       SUM(salary) AS sal,
       MAX(hire_date) AS hiredate
FROM   employees GROUP BY department_id;



The restrictions on multitable inserts are:
  • Multitable inserts can only be performed on tables, not on views or materialized views.
  • You cannot perform a multitable insert via a DB link.
  • You cannot perform multitable inserts into nested tables.
  • The sum of all the INTO columns cannot exceed 999.
  • Sequences cannot be used in the subquery of the multitable insert statement.



1 comment: