Is there a way to do an INSERT INTO SELECT statement while specifying a commit count?

Question

DB: Oracle 11g

Is there a way to do something like the following:

INSERT INTO T1
    (V1, V2)
COMMIT EVERY X
AS
SELECT (V1, V2) FROM T2;

I know how to loop through cursor, but I'm looking for something more streamlined.

PL/SQL is fine, but no looping.
Using a SQL Hint is good too.

If this is just something that oracle can't handle, sadness ensues (Mostly I'm just curious since I have another approach working already).

NOTE: The application has hundreds of billions of records. Millions are created per day. INSERT INTO SELECT doesn't work on such large sets of data. Especially when there are equally large sets running in parallel.

Answer 1

Oracle doesn't allow a statement to have interim commits, no. Doing so would violate the basic properties of an ACID database. What happens if the statement fails on row N? Oracle wouldn't be able to roll back the previously committed rows. It wouldn't know which rows had been processed and which hadn't been processed. So your statement would be partially successful and your database would be left in an unknown state. One of the major benefits of using a relational database is to avoid exactly that outcome.

Why do you want to do an interim commit in the first place? That's going to make your code slower and cause you to use more resources. It's going to force you write a bunch of code to make your process restartable (i.e. you'd have to track which rows had been processed and which hadn't so that you could either roll back partially complete changes or restart the process if it fails in the middle). And it's going to make your code much harder to test. There is almost never a good reason to do an interim commit.

Answer 2

Oracle doesn't provide such a SQL construct. Using PL/SQL with a cursor and the FORALL statement can do it for you ... BUT:

It's good that Oracle doesn't provide such sadness. Oracle is an RDBMS that is build upon the ACID principles. The I stands for isolation which, in Oracle, defaults to READ COMMITTED. Meaning that other concurrent transactions can't see your transaction half way through. It prevents other sessions from seeing inconsistent data. One of the cornerstones of using an RDBMS.

Which brings us to the most important question here: Why would you ever want such a construct?

I hope it is not fear of "big" transactions.