Vertica Update Strategy
I need to update several columns in one table based on the values from another tables.
Which of the two strategies below is more appropriate in Vertica taking into account that the amount of expected updates is relatively small compared to the size of table 2% or less?
strategy 1 -- column by columns
SET SESSION AUTOCOMMIT TO off;
START TRANSACTION ISOLATION LEVEL READ COMMITTED READ WRITE;
UPDATE Facts.Orders_User2 F
SET brand_cod = ISNULL(c.brand_cod, -1)
FROM Dictionary.Chrt_NM_IMT c
WHERE c.chrt_id = f.chrt_id AND F.brand_cod <> ISNULL(c.brand_cod, -1);
UPDATE Facts.Orders_User2 F
SET kind_id = ISNULL(c.kind_id, -1)
FROM Dictionary.Chrt_NM_IMT c
WHERE c.chrt_id = f.chrt_id AND F.kind_id <> ISNULL(c.kind_id, -1);
UPDATE Facts.Orders_User2 F
SET subject_id = ISNULL(c.subject_id, -1)
FROM Dictionary.Chrt_NM_IMT c
WHERE c.chrt_id = f.chrt_id AND F.subject_id <> ISNULL(c.subject_id, -1);
SELECT COUNT(*) FROM
(
SELECT C.brand_cod, F.brand_cod, F.* FROM Facts.Orders_User2 F, Dictionary.Chrt_NM_IMT c
WHERE c.chrt_id = f.chrt_id AND F.brand_cod <> ISNULL(c.brand_cod, -1)
) F;
ROLLBACK;
strategy 2 -- all columns at once
SET SESSION AUTOCOMMIT TO off;
START TRANSACTION ISOLATION LEVEL READ COMMITTED READ WRITE;
UPDATE Facts.Orders_User2 F
SET
brand_cod = ISNULL(c.brand_cod, -1),
kind_id = ISNULL(c.kind_id, -1),
subject_id = ISNULL(c.subject_id, -1)
FROM Dictionary.Chrt_NM_IMT c
WHERE c.chrt_id = f.chrt_id AND
(F.brand_cod <> ISNULL(c.brand_cod, -1) OR F.kind_id <> ISNULL(c.kind_id, -1) OR F.subject_id <> ISNULL(c.subject_id, -1));
SELECT COUNT(*) FROM
(
SELECT C.brand_cod, F.brand_cod, F.* FROM Facts.Orders_User2 F, Dictionary.Chrt_NM_IMT c
WHERE c.chrt_id = f.chrt_id AND F.brand_cod <> ISNULL(c.brand_cod, -1)
) F;
ROLLBACK;