How to update a table, and only update it?

David_P_1

I need to do many large updates to a large table.  According to the documentation an update is really a delete and insert.  

 

Is there any way I can stop this behavior, ie have an update be just an update?

 

Adrian_Oprea_1

 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.

 

NorbertK

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.

marcothesane

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 ...