oracle多表插入

多表插入的性能要单个插入性能更好，因为多表插入只需要与源表进行一次交互，如下语句

INSERT ALL

  WHEN region = 'EMEA' THEN INTO sales_emea
           (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
            QUANTITY_SOLD, AMOUNT_SOLD)
    VALUES (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, 
            QUANTITY_SOLD, AMOUNT_SOLD)
  WHEN region = 'US' THEN INTO sales_us 
           (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
            QUANTITY_SOLD, AMOUNT_SOLD)
    VALUES (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, 
            QUANTITY_SOLD, AMOUNT_SOLD)
SELECT * FROM sales_updates;

 

单个插入：

INSERT INTO sales_emea
         (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, 
          PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD)
   SELECT PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, 
          PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD
   FROM sales_updates
   WHERE region = 'EMEA';

INSERT INTO sales_us
         (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, 
          PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD)
   SELECT PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, 
          PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD
   FROM sales_updates
   WHERE region = 'US';