Effective way to bulk update with Merge

Best Answer

• Options

  marcothesane - Select Field - Administrator

  December 2018 Answer ✓

  Hi Alex -

  If your table looks like this:

  CREATE TABLE merge_target (
    prod_ky     INT NOT NULL PRIMARY KEY
  , prod_price NUMERIC(18,2)
  );
  

  , the staging table merge_source has the same structure, and your MERGE statement looks like this:

  MERGE /* +DIRECT  */                                                                                                                               
  INTO merge_target t
  USING merge_source s
     ON s.prod_ky=t.prod_ky
  WHEN MATCHED THEN UPDATE SET
    prod_ky=s.prod_ky -- this hurts - the identifier in the set clause- but this is what     makes the difference ...
  , prod_price=s.prod_price
  WHEN NOT MATCHED THEN INSERT (
    prod_ky
  , prod_price
  ) VALUES (
    s.prod_ky
  , s.prod_price
  )
  ;
  

  ... then you will have an optimized Merge, (a DELETE access plan and an INSERT access plan), but only on the rows existing in merge_source, not all in the target.

  Pre-requisite is that the target table has a primary key or a unique index - and that that one is used in the ON clause of the MERGE statement. And note that all columns of the table are used in both the Update and the Insert branch of the statement.

  Consider partitioning the target table by yearmonth, creating a third table with the exact same structure, copying the partitions of the last two months into that table, applying the optimized MERGE to that table, and finally swapping partitions between that table and the final target table.

  Check out the Vertica docu on SWAP_PARTITIONS_BETWEEN_TABLES() to see how that could work ...

  Good luck -
  Marco

  1