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.


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

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