Working with Merge in Vertica

In this article we will talk about the Merge operation in HP Vertica.

What is Merge and why is important to know how to use it while working with Vertica ?

  • to start with we all know that Vertica is optimized for query-intensive workloads and not for single insert or updates.

  • to speedup a bit the insert and update operations we can make use of the MERGE operation.

MERGE statement combines insert and update operations into a single operation.

Syntax for Merge

MERGE [/*+ direct */]
... INTO [[db-name.]schema.]target-table [ alias ]
... USING [[db-name.]schema.]source-table [ alias ]
... ON ( condition )
... [ WHEN MATCHED THEN UPDATE SET
      column1 = value1 [, column2 = value2 ... ] ]
... [ WHEN NOT MATCHED THEN INSERT ( column1 [, column2 ...])
      VALUES ( value1 [, value2 ... ] ) ]
  • you must have select permission on the source table and insert,update and delete on the target table.
Important:
  • during the merge operation an exclusive lock will be required.

Examples of merge operations

  • Table we will use for this demonstration
create table target_tbl (id int, name varchar(10));
insert into target_tbl values(1,'Adi');
insert into target_tbl values(2,'Adi');
insert into target_tbl values(3,'Adi');
commit;

create table source_tbl (id2 int , name2 varchar(10));
insert into source_tbl values(4,'Ju');
insert into source_tbl values(5,'Ju');
insert into source_tbl values(6,'Ju');
commit;
  • Merge Statement
MERGE INTO target_tbl
  USING source_tbl b
  ON id = b.id2
WHEN MATCHED THEN
  UPDATE SET id = b.id2, name = b.name2
WHEN NOT MATCHED THEN
  INSERT (id, name) VALUES (b.id2, b.name2);
  •  Target Table Result Set before Merge
(dbadmin@:5433) [dbadmin]  select * from target_tbl ;
id | name
----+------
1 | Adi
2 | Adi
3 | Adi
  •  Executing the Merge Statement
  • this is a simple merge statement that includes both MATCHED and NOT MATCHED options.
(dbadmin@:5433) [dbadmin]  MERGE INTO target_tbl
 USING source_tbl b
 ON id = b.id2
 WHEN MATCHED THEN
 UPDATE SET id = b.id2, name = b.name2
 WHEN NOT MATCHED THEN
 INSERT (id, name) VALUES (b.id2, b.name2);

OUTPUT
--------
3
(1 row)

(dbadmin@:5433) [dbadmin] * select * from target_tbl;
id | name
----+------
1 | Adi
2 | Adi
3 | Adi
4 | Ju
5 | Ju
6 | Ju
Description of the action that merge statement did.
  • WHEN MATCHED - an update statement will be executed
  • WHEN NOT MATCHED - an insert statement will be executed (this is optional)
To see how improve MERGE performance follow this article.