Inserting multiple rows into Oracle

Question

In the discussion about multiple row insert into the Oracle two approaches were demonstrated:

First:

insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
          select 8000,0,'Multi 8000',1 from dual
union all select 8001,0,'Multi 8001',1 from dual

Second:

INSERT ALL
   INTO t (col1, col2, col3) VALUES ('val1_1', 'val1_2', 'val1_3')
   INTO t (col1, col2, col3) VALUES ('val2_1', 'val2_2', 'val2_3')
   INTO t (col1, col2, col3) VALUES ('val3_1', 'val3_2', 'val3_3')
   .
   .
   .
SELECT 1 FROM DUAL;

Could anyone argue the preference of using one over another?

P.S. I didn't do any research myself (even explanation plan), so any information or opinion would be appreciated.

Thanks.

Raze

Answer 1

From performance's point of view, these queries are identical.

UNION ALL won't hurt performance, since Oracle estimates the UNION'ed query only when it needs it, it doesn't cache the results first.

SELECT syntax is more flexible in that sense that you can more easuly manupulate the SELECT query if you want to change something.

For instance, this query:

insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
          select 8000,0,'Multi 8000',1 from dual
union all select 8001,0,'Multi 8001',1 from dual

can be rewritten as

INSERT
INTO    pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
SELECT  7999 + level, 0, 'Multi ' || 7999 + level, 1
FROM    dual
CONNECT BY
        level <= 2

By replacing 2 with appropriate number, you can get any number of rows you want.

In case of INSERT ALL, you would have to duplicate the destination table description, which is less readable if you need, say, 40 rows.

Lebron

Note that using SOME_SEQUENCE.NEXTVAL as value, gives an Oracle error ORA-02287: sequence number not allowed here (Oracle 11g). A workaround is described here: orafaq.com/forum/t/54217/2 - try INSERT INTO ... SELECT SOME_SEQUENCE.NEXTVAL, T.* FROM (SELECT ... UNION ALL SELECT ...) T – Theca

Answer 2

The INSERT ALL method has a problem with inserting bigger number of rows into a table.

I recently wanted to insert 1130 rows into a table with single SQL statement. When I tried to do this with INSERT ALL method I got the following error:

ORA-24335 - cannot support more than 1000 columns

When I used INSERT INTO .. UNION ALL .. approach everything went fine.

Btw. I didn't know about the UNION ALL method before I found this discussion :)

Kitchenmaid

Answer 3

I would suspect solution 1 is a bit of a hack that works and is probably less efficient than the designed alternative of Insert ALL.

Insert all is really designed for you to insert many rows into more than 1 table as a result of a select, eg:

Insert ALL
into 
  t1 (c1, c2) values (q1, q2)
  t2 (x1, x2) values (q1, q3)
select q1, q2, q3 from t3 

If you want to load thousands of rows and they are not in the database already, I don't think this is the best way to do it - If your data is in a file, you want to look at External Tables or SQL Loader to efficiently insert the rows for you.

Choral

Answer 4

i tried some test and the faster solution should be

insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
          select 8000,0,'Multi 8000',1 from dual
union all select 8001,0,'Multi 8001',1 from dual

buffering between 300 <-> 400 rows (i tried with odbc, this value could depends about its configuration)

Softcover

Answer 5

The statement utilizing the UNION ALL has theoretically a small performance disadvantage as it has to union the results of all statements before the insert can happen. The INSERT ALL doesn't have this disadvantage as the final result can already be processed line-by-line.

But practically the optimizer inside Oracle should make the difference negligible and it is up to your preferences which way you choose.

In my own opinion the INSERT ALL is the better human-readable of the two while the UNION ALL variant is the one taking less space when such an insert is automatically generated.

Kebab

Agreed with the conclusion, but: Nitpick: these INSERT statements do not return any values, so it is meaningless to say anything happens "before the insert can happen" or "line-by-line". Anyway, he's using UNION ALL so there is no problem with sorts, if that is what you were thinking of. – Nicaragua

A quick "explain plan" shows that the INSERT ALL with 4 lines int SCOTT.EMP has a cost of 2 (1 multi-table insert with the only CPU-cost in the dual-select) while the UNION ALL variant has a cost of 8 (with every SELECT causing another 2). Its not sorting but the combining of the results and the single (at least FAST DUAL) selects which are causing the cost. – Kebab

Answer 6

If you have insert statements that are more than 1000 then put all the insert statements in a .sql file and open that in Toad or SQL Developer and then execute. All records will get inserted.

Sweettempered

OP wanted to know if one of the approaches he mentioned is to be preferred over the other, it is not about how/where to execute the SQL. – Ixia

Answer 7

You should consider Array-Insert.

• Easy SQL
• need some client-side coding to setup the array-Parameters

This is the way to minimize the Network-Traffic if some hundred inserts needs to be done in a batch.

Braunstein