How to update a table, and only update it?

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?

 

Comments

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

     

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

  • marcothesanemarcothesane Employee

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.