Using multi-row inserts

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?


  • Options
    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, 

  • Options
    Navin_CNavin_C Vertica Customer
    Just to add on,

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

Leave a Comment

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