Vertica Update query

SRA1

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]

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.

swalkaus

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';
0|0
0|null
1|1
.

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)