CODEWITHSUNDEEP
sqloracle-databaseinsertlimit
user1561358
user1561358

Reputation: 304

Maximum number of rows in one INSERT ALL

The question is rather obvious. Suppose one has an INSERT ALL statement in following form:

INSERT ALL
INTO myTable (id, cola, colb, colc) VALUES (id, 'a', 'b', 'c')
INTO myTable (id, cola, colb, colc) VALUES (id, 'a', 'b', 'c')
INTO myTable (id, cola, colb, colc) VALUES (id, 'a', 'b', 'c')
...
SELECT * FROM dual;

Now suppose those three dots stood for, say 5k INTO clauses. All unique ids, and same number of columns.

What is the maximum number of rows one can insert into table in this fashion?

Upvotes: 2

Views: 3142

Answers (2)

Jon Heller
Jon Heller

Reputation: 36807

Although INSERT ALL does not have a theoretical maximum number of rows, in practice you will want to keep the number of rows to the low hundreds.

As I demonstrated in this answer, Oracle can easily handle hundreds of rows, but there's some magic number where the parse times start to grow exponentially. In older versions, things got really bad at 500 rows. With 19c, performance becomes an issue in the thousands of rows. Below are the results of a quick test:

# of Rows    Time in Seconds
---------    ---------------
     1000                0.4
     2000                1.7
     3000                4
     4000               12
     5000               24

And for reasons I don't understand, the UNION ALL approach tends to work faster. So you might want to limit your number of rows to several hundred and use a statement like this:

INSERT INTO myTable (id, cola, colb, colc)
SELECT 'a', 'b', 'c' FROM dual UNION ALL
SELECT 'a', 'b', 'c' FROM dual UNION ALL
...
SELECT 'a', 'b', 'c' FROM dual;

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 142713

Unlimited, as far as I can tell. Number of columns is limited to 999, but number of rows ... sky is the limit.

Upvotes: 1

Related Questions