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?



  • Options

     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.


  • Options

    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.

  • Options
    marcothesanemarcothesane - Select Field - Administrator

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

    INSERT INTO help_target 
    , 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.:
    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
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file