The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Vertica Update query

Hi I am trying an update query with 23M records and 300 columns. The update is taking more than 3 hours and failing with timeout error. The update query is to update a null column with hardcoded value. When I explained plan, the materialization is shown for all the columns which is taking time I believer. I tried direct hint too which is not working. please suggest an alternate.


  • [Deleted User][Deleted User] Administrator

    Do you only have super projections for table being updated or query specific projections also ? if you have query specific projections, make sure that predicate columns of update are part of every query specific projections.

    If you share explain plan , i can give you more hints.

  • swalkausswalkaus Vertica Employee Employee

    In some instances you might be better off adding a new column, dropping the existing column and renaming the new column. You can't do this if the column you wish to update is used in a projection's segmentation expression or projection sort order. And of course the "drop old, rename new" operation isn't atomic. In case you're interested the steps look like this:

    create table t (a int, b int) order by a segmented by hash(a) all nodes;
    copy t from stdin null as 'null';

    alter table t add column c int default case when b is null then -1 else b end;
    alter table t alter column c set default -1;
    select make_ahm_now(); -- necessary b/c an add column event, which is used by recovery, depends on 'c'

    alter table t drop column b;
    alter table t rename column c to b;

    select * from t; a | b
    1 | 1
    0 | 0
    0 | -1
    (3 rows)

Leave a Comment

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