How to execute more than 1 million insert queries in Oracle SQL Developer?

Question

I have more than 1 million insert queries to be executed in Oracle SQL Developer, which takes a lot of time. Is there any way around to optimize this.

Answer 1

SQL Developer is the wrong tool to run a 1,000,000 row-by-row inserts.

So is SQL*Plus for that matter.

If you can't write a program to do the inserts using a loop or a cursor or some pl/sql bulk collects, then do what @marmite-bomber suggests - write your data out to a flat delimited text file, and setup a SQL*Loader scenario.

Now, you CAN use SQL Developer to do this.

Point to your delimited text file.

Map everything up, and then use this IMPORT method.

Once it's done, you'll have a set of bash or cmd scripts, that you will need to execute. But first, you'll need an Oracle Client on your machine - that's where the sqlldr program lives.

It's orders of magnitude faster than running a 1,000,000 individual inserts. And your DBA won't hate you.

Using the SQLDev wizard, you can have it up and going in just a few minutes.

The PROOF: I tested a simple scenario - my findings were (details here): 10,753 records inserted per second for SQL*Loader 342 records inserted per second for SQL Developer

SQL*Loader was 3,144% faster.

Answer 2

Running single row INSERTs is not feasible for such number of rows.

So one possible way is to preprocess the script, extraction only the CSV data.

Here a simple example

Insert into TAB(COL1,COL2,COL3) values ('1','xxx',to_date('16-10-2018 15:13:49','DD-MM-YYYY HH24:MI:SS'));
Insert into TAB(COL1,COL2,COL3) values ('2','zzzzz',to_date('06-10-2018 15:13:49','DD-MM-YYYY HH24:MI:SS'));

remove all not relevant parts to get

'1','xxx','16-10-2018 15:13:49'
'2','zzzzz','06-10-2018 15:13:49'

and load this file using SQL*Loader or external table. The performance will be fine.

Answer 3

I have more than 1 million insert queries to be executed in Oracle SQL Developer

There's an overhead to executing any SQL statement. You are paying that tax 1000000 times. In additions some IDEs instantiate a separate tab for the outcome of each SQL statement executed in a worksheet (when run in one click,). I can't remember whether SQL Developer does that, but if it does then that's another tax you're paying 1000000 times.

Two possible optimizations.

Squidge the single-row inserts into set operations by select values from dual:

insert into your_table
select blah_seq.nextval, q.txt, q.val from (
   select 'WHATEVER' as txt, 42 as val from dual union all
   select 'AND SO ON'  as txt, 23 as val  from dual union all
    ...
);

This will reduce the overhead. It is lot of editing work to do this.

Alternatively, use a bulk loading option such as SQL*Loader or external tables to load the data. If you have a programmer's editor with regex support (like Notepad++) then it's relatively simple to strip out the insert syntax and convert the values clause into CSV rows.

A third approach would be to go back top the source which produced the million insert statements and ask them to provide the data in a more manageable format, such as a data pump export.

Answer 4

another suggestion is to do validation that you do not have any dups on possible PK columns. Drop the constraints before insert and put them back in once done. Keep that alter script handy. You can do this as part of same script or different script. Again, if you dont check for dups and go to put the PKs back on expect error; then you be chasing down rows with dups and deleting that data.

Answer 5

You could put chunks of your data into a collection and bulk insert

Also, delete all indexes other than the primary key. Create the indexes after the data load

could be a little dangerous but depending on quality of data you could also remove foreign keys and then re creating them once the data was imported. You would have to be sure your import didn't break the FK relationships

declare
    type my_tab is table of mytable%rowtype index by binary_integer;
    a_imp   my_tab;
begin

    -- do stuff to populate a_imp from presumably a text file? or select statement

    -- you could chunk your population of the array and bulk insert say 10,000 rows at a time

    -- loop through the holding array and import any remaining data
    forall ix in 1 .. a_imp.count
        insert into [tabel_name] values a_imp(ix);
    commit;

end;