Vertica Update Strategy

avoroninavoronin Registered User

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;

Leave a Comment

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