Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle multi insert statement

In my application I have to add many records. I am using the following construct:

   INSERT /*+ append parallel(t1, 4) parallel(t2, 4) */ ALL
   INTO t1 (col1, col2, col3) VALUES ('val1_1', 'val1_2', 'val1_3')
   INTO t2 (col1, col2, col3) VALUES ('val2_1', 'val2_2', 'val2_3')
   INTO t2 (col1, col2, col3) VALUES ('val3_1', 'val3_2', 'val3_3')
   .
   .
   .
SELECT 1 FROM DUAL;

I am also using APPEND and PARALLEL hints. Notice that I am inserting data in two different tables. It seems that parallel is being ignored (the DBA told me). So how I can know if it is being used or not? Is it possible to use PARALLEL hint in such construct? Is it effective?

like image 461
Eduardo Mauro Avatar asked May 17 '11 19:05

Eduardo Mauro


People also ask

How do I run multiple inserts in Oracle SQL Developer?

To run multiple statements together you need to Run Script, either from the toolbar icon or by pressing F5. Show activity on this post. After all of the insert statements are in the "Query" Builder window, you can "hilight" (Control A) and then hit "Control" and "Enter" at the same time.

Can we insert multiple rows single insert statement?

Answer. Yes, instead of inserting each row in a separate INSERT statement, you can actually insert multiple rows in a single statement. To do this, you can list the values for each row separated by commas, following the VALUES clause of the statement.


2 Answers

This will probably be enough to get it to work:

alter session enable parallel dml;

You can check the actual degree of parallelism with a query like this:

select px_servers_executions, v$sql.*
from v$sql where lower(sql_text) like '%insert%parallel%' order by last_load_time desc;

If you're still not getting parallelism there are many possible reasons. To start, look at these parameters:

select * from v$parameter where name like 'parallel%'

But you probably don't want parallelism for your insert statement. Parallelism has a a large amount of overhead, and generally is only useful if you're dealing with many thousands or millions of records.

I'm guessing your real problem is the time to parse the large SQL statement. Multi-table inserts are especially bad. If you try to insert more than a few hundred rows your query will take many seconds for parsing. And depending on your version of Oracle, it will just hang forever if you try to use 501 tables. It's much faster to run several smaller queries instead of one large query. For example, 5 inserts of 100 rows will run much faster than one insert of 500 rows. (In general this is the exact opposite of how to performance tune for Oracle. This is a special case because of the bugs related to parsing large SQL statements.)

like image 87
Jon Heller Avatar answered Nov 15 '22 06:11

Jon Heller


The APPEND hint is only supported with the subquery syntax of the INSERT statement, not the VALUES clause. If you specify the APPEND hint with the VALUES clause, it is ignored and conventional insert will be used. To use direct-path INSERT with the VALUES clause, refer to "APPEND_VALUES Hint".

like image 30
xorinox Avatar answered Nov 15 '22 04:11

xorinox