Skip to content

One insert or multiple inserts?

May 13, 2010


A colleague asked me a trick* question today which I failed :)

* whether it was a “trick” question is probably in the eye of the beholder, though…

“What are the differences, if any, between the following two approaches to inserting multiple rows (assume v1 and v2 have different values):

INSERT INTO mytable (a)
SELECT :v1 FROM DUAL;
INSERT INTO mytable (a)
SELECT :v2 FROM DUAL;

OR:

INSERT INTO mytable (a)
SELECT :v1 FROM DUAL
UNION
SELECT :v2 FROM DUAL;

I quickly answered:

  1. The first approach requires one extra parse;
  2. The second approach requires a Sort Distinct (albeit of only two rows)
  3. A UNION ALL would be better, which would not require a Sort, nor would require the extra parse.

My colleague responded, there’s one very important thing I missed: Triggers! The first approach would execute the BEFORE STATEMENT and AFTER STATEMENT triggers once for each row. The second approach would only execute these triggers once.

What’s sad is that the application we’re working on has row-level logic in the BEFORE/AFTER statement triggers. If we try to optimise the code to insert more than one row in one INSERT statement, the application only runs the row-level logic for the first row inserted. Bad code! Very very bad!

About these ads

From → PL/SQL, Tools

4 Comments
  1. Is there any reason for not using INSERT INTO …. VALUES ….. Instead of using DUAL?

    • Hi Jiri,

      Yes, this was just an example. In our case the queries were not on DUAL but on other tables – and each query would only retrieve one row.

      • Thank you for the clarification.

        Typing on iPhone far from dev box to test this….

        I think Option2 should be theoretically faster If

        1.there are indexes
        2. Table is compressed

      • Well, Jiri, we could discuss whether one approach is faster than the other, and under which conditions – but the point of this post is not really about performance, although I can see why it might seem so. The point is really about the differences due to the existence of triggers on the table.

Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 206 other followers

%d bloggers like this: