How to update a table, and only update it?

Comments

• Options

  Adrian_Oprea_1

  November 2015

   I guess not having Vertica be able to do inplace updates is what makes it fast. 

   You need to be aware that Vertica is a write once(on datafile/tablespace -- how ever you wanna call it) and this triggers this bad update performance but in the same time it does make a difference(much faster) when loading and reading from Vertica.

   Is important to know that Vertica was build for a purpouse and is addressing that porpuse. Load data fast and query the data fast.

   I sugesst you find a way to implement merge statement, partition switching, implement partitions.

   

  0
• Options

  NorbertK

  November 2015

  As Adrian has mentioned, Vertica is optimized for load and read operations. Depending on the frequency and amount of data being moved, an alternative is to perform a correlated query and load into a staging table. You can then replace the staged table with your source table.

   

  If you are looking at possibly using a MERGE operation, consider using a correlated join instead.

  0
• Options

  marcothesane - Select Field - Administrator

  May 2020

  In my experience, the fastest way of updating over 20% of all rows in a big table is as follows:
  Imagine you increase the salary in the target table by 2 percent for everyone (won't we all like that ...?)

  CREATE TABLE help_target LIKE target INCLUDING PROJECTIONS;
  INSERT INTO help_target 
  SELECT
    id
  , name
  , dob
  , salary*1.02
  , hire_dt
  FROM target;
  ALTER TABLE target RENAME TO target_old; -- or drop it if you don't need the previous status any more
  ALTER TABLE help_target RENAME TO target;
  GRANT SELECT ON target TO PUBLIC; -- or whatever grants existed on the old target table
  

  If you only have to update the last 3 months of a table containing years and years of history, consider partitioning the table by year-month, to only insert -- select the last 3 months's partitions into the help table, and to end up calling the SWAP_PARTITIONS_BETWEEN_TABLES() function.:
  https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/PartitionManagement/SWAP_PARTITIONS_BETWEEN_TABLES.htm
  This approach also saves you from having to worry about grants, as you keep the original table.
  Happy playing ...

  0