Using multi-row inserts

norbert_krupa

Not sure how to arrange the question, but I'm basically curious about the internals work of a multi-row insert. By multi-row I mean:

INSERT INTO schema.object (col1, col2)  SELECT 'a', 'b'  UNION ALL  SELECT 'c', 'd'  ...

For one reason or another an app has to insert row-by-row. Just using insert creates high overhead. When you introduce a multi-row insert like above, what is different in the loading process? Should the insert be run with /*+DIRECT*/ ? Is there a limit to how much data can be passed in with a multi-row insert?

[Deleted User]

You don't need to put /*+DIRECT*/ when using mulit-insert. Vertica will put the data in WOS (memory ) and tuple mover will move it to disk eventually. 

There is not limit how much data you can pass in the multi-row insert, and it will perform faster. 

Hope this helps, 

Eugenia

Navin_C

Just to add on,

 /*+DIRECT*/ Keyword is recommended only in huge loads, so that the data bypasses the WOS and gets directly loaded ROS.