One insert or multiple inserts?


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!

Weird SQL Results
ORA-01481 Invalid number format model

Comments

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

    • Jeffrey Kemp
      14 May 2010 - 6:47 am

      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.

Leave a Reply

Your email address will not be published / Required fields are marked *