Can I send "batched" INSERTs to Oracle?

Question

Scenario:

• I load some data into a local MySQL database each day, about 2 million rows;
• I have to (have to - it's an audit/regulatory thing) move to a "properly" administered server, which currently looks to be Oracle 10g;
• The server is in a different country: a network round-trip current takes 60-70 ms;
• Input is a CSV file in a denormalised form: I normalise the data before loading, each line typically results in 3-8 INSERTs across up to 4 tables;
• The load script is currently implemented in Ruby, using ActiveRecord and fastercsv. I've tried the ar-extensions gem, but it assumes that the MySQL style multiple values clause idea will work. It doesn't.

EDIT: Extremely useful answers already - thank-you! More about that pesky input file. The number of fields is variable and positions have changed a few times - my current script determines content by analysing the header row (well, fastercsv and a cunning converter do it). So a straight upload and post-process SQL wouldn't work without several versions of the load file, which is horrible. Also it's a German CSV file: semi-colon delimited (no big deal) and decimals indicated by commas (rather bigger deal unless we load as VARCHAR and text-process afterwards - ugh).

The problem:

Loading 2 million rows at about 7/sec is going to take rather more than 24 hours! That's likely to be a drawback with a daily process, not to mention that the users would rather like to be able to access the data about 5 hours after it becomes available in CSV form!

I looked at applying multiple inserts per network trip: the rather ungainly INSERT ALL... syntax would be fine, except that at present I'm applying a unique id to each row using a sequence. It transpires that

INSERT ALL
    INTO tablea (id,b,c) VALUES (tablea_seq.nextval,1,2)
    INTO tablea (id,b,c) VALUES (tablea_seq.nextval,3,4)
    INTO tablea (id,b,c) VALUES (tablea_seq.nextval,5,6)
SELECT 1 FROM dual;

(did I say it was ungainly?) tries to use the same id for all three rows. Oracle docus appear to confirm this.

Latest attempt is to send multiple INSERTs in one execution, e.g.:

    INSERT INTO tablea (id,b,c) VALUES (tablea_seq.nextval,1,2);
    INSERT INTO tablea (id,b,c) VALUES (tablea_seq.nextval,3,4);
    INSERT INTO tablea (id,b,c) VALUES (tablea_seq.nextval,5,6);

I haven't found a way to persuade Oracle to accept that.

The Question(s)

• Have I missed something obvious? (I'd be so pleased if that turned out to be the case!)
• If I can't send multiple inserts, what else could I try?

Why Accept That One?

For whatever reason, I prefer to keep my code as free from platform-specific constructs as possible: one reason this problem arose is that I'm migrating from MySQL to Oracle; it's possible another move could occur one day for geographical reasons, and I can't be certain about the platform. So getting my database library to the point where it can use a text SQL command to achieve reasonable scaling was attractive, and the PL/SQL block accomplishes that. Now if another platform does appear, the change will be limited to changing the adapter in code: a one-liner, in all probability.

Answer 1

How about shipping the csv file to the oracle db server, use SQLLoader to load the csv file into a staging table and then running a stored procedure to transform and INSERT it in the final tables?

Answer 2

You could use:

insert into tablea (id,b,c)
 ( select tablea_seq.nextval,1,2 from dual union all
   select tablea_seq.nextval,3,4 from dual union all
   select tablea_seq.nextval,3,4 from dual union all
   select tablea_seq.nextval,3,4 from dual union all
   ...
 )

This works until about up to 1024 lines when I remember correctly.

You could also send it as a PL/SQL batch instruction:

BEGIN
 INSERT INTO tablea (id,b,c) VALUES (tablea_seq.nextval,1,2);
 INSERT INTO tablea (id,b,c) VALUES (tablea_seq.nextval,3,4);
 INSERT INTO tablea (id,b,c) VALUES (tablea_seq.nextval,5,6);
 ...
 COMMIT;
END

Answer 3

I would be loading the raw CSV file to a dedicated table in the database using SQL*Loader without normalising it, then running code against the data in the table to normalise it out to the various required tables. This would minimise the round trips, and is a pretty conventional approach in the Oracle community.

SQL*Loader can be a little challenging in terms of initial learning curve, but you can soon post again if you get stuck.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/part_ldr.htm#i436326

Answer 4

I have a quick suggestion. I'm from the MySQL world but was trained on Oracle, I think this will work.

In MySQL you can insert multiple records with a single insert statement. It looks like this:

INSERT INTO table_name (column_one, column_two, column_three, column_four)
VALUES
    ('a', 'one', 'alpha', 'uno'),        // Row 1
    ('b', 'two', 'beta', 'dos'),         // Row 2
    ('c', 'three', 'gamma', 'tres'),     // etc.
....
    ('z', 'twenty-six', 'omega', 'veintiséis');

Now obviously you can only insert into one table at once, and you wouldn't want to do 2 million records, but you could easily do 10 or 20 or 100 at a time (if you are allowed packets that big). You may have to generate this by hand, I don't know if they frameworks you are using (or any, for that matter) support making this kind of code for you.

In the MySQL world this DRAMATICALLY speeds up inserts. I assume it does all the index updates and such at the same time, but it also prevents it from having to re-parse the SQL on each insert.

If you combine this with prepared statements (so the SQL is cached and it doesn't have to be parsed out each time) and transactions (to make sure things are always in a sane state when you have to do inserts across multiple tables... I think you will be doing pretty good.

---

NunoG is right that you can load the CSVs directly into Oracle. You may be best off reading in the input file, generating a normalized set of CSV files (one for each table), and then loading in each of those one at a time.

Answer 5

Instead of executing the SQL over the network you could write the Inserts to a text file, move it over the network and run it locally there.

Answer 6

SQL*Loader is an Oracle-supplied utility that allows you to load data from a flat file into one or more database tables. This will be 10-100 times faster than inserts using queries.

http://www.orafaq.com/wiki/SQL*Loader_FAQ

If SQL*Loader doesn't cut it, try a small preprocessor program that formats the file in SQL*Loader readable format.