We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Using multi-row inserts — Vertica Forum

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?

Comments

  • 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_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
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file